Hi all,

I am pretty new to access and have been playing around with it the last few weeks. I feel like this is a pretty basic question but it has stumped me for the past two days. I am setting up a faculty database and I am trying to set up their certifications and expiration dates. Each faculty member can be certified by four different groups (ACLS, ATLS, PALS, BLS) and they are certified as either a "Provider" or "Instructor". Right now it is just set up like an excel sheet with the faculty members name, their unique userid (combination of their first and last name) followed by their certifications and expiration dates so it would look something like this.

alex jones / aljo3 / ACLS / Provider / 6/30/2012 / BLS / Instructor / 07/24/2013

I assume I should be setting up at least two tables with Faculty Members on one table and a separate table for certifications. Do I make a separate table for each certification group or can they all go on one? I believe in the end I will want to set up some type of lookup function or combo /listbox.

Playing with Access has been a good learning experience and I am looking forward to someday not being such a newbie. Any advice or extra reading materials would be greatly appreciated.

Hi breeon,

Make Tables...
1. Facultymembers (fMID pk auto, MamaberName, other fields )

2. Certification (CertID pk auto,Certdescrip,CertType fk)

3. CertificationType (certTypeId pk auto,Typeof cert e.g. ACLS )

4. Junctional Table Facultymember_Certifiactions (FMCRTID pk auto,FMID fk, CertifiedDate,note)

Facultymember 1:M Facultymember_Certifiactions
Certification 1:M Facultymemeber_Certifications

CertificationType 1:M Certification

my 2C

khalak

Thank you for the response khalak. I've been playing around with the tables like you described and the only part that I got a little stuck was at the second relationship you listed.

Certification 1:M Facultymemeber_Certifications

How does the certification table link with the junction_factulyCertifications table?

In the junction table do i need a certifictionID (FK) that will connect the two tables?
Right now I have Certifications: certID (pk), certDescription, certificationTypeID (fk)
junctiontable: facultyCertificationID (PK), faculty ID (FK), expirationDate

Thank you again.

You answered your own question correctly (IMG:style_emoticons/default/thumbup.gif)

Yes, you need a CertID foreign key in the junction table.

Thanks John! Even the small victories like that are a boost to my morale. After setting up the tables and relationships, I tried to see what it would look like in form view but with no luck. I was hoping the typeofcertification (ATLS, BLS, ETC) would be available to select from a drop down with the same going for certDescription (Provider/Instructor). However, when I click on the arrow for the list box and select the blank space it gives me a field cannot be update error.

In this case you would use a Form based on the Faculty table with a Subform based on the junction table, using the FMID as the parent and child link field; on the suborm you can add a Combo Box or Listbox using the Certification table as its rowsource, bound to the CertID but displaying the cert name. The Bound Column would be the ID (not the description).

Thanks for the response John. I took a little break over the weekend but just started to play around with it some more but I'm not sure how to display the cert description and not the CertID. Right now I have:

rowsource: t_Certification
Control Source: CertificationID (I think this is where my problem is coming from)
Bound Column: 1

I also tried following this link, http://www.techonthenet.com/access/comboboxes/bind_index.php but when I tried to select from the combo box it would not let me due to being bound to a autonumber.

I'd suggest using

SELECT CertID, CertDescrip FROM Certifications ORDER BY CertDescrip;

as the Rowsource of the combo box; Control Source = CertID (you did add that to the junction table, right?); Bound Column = 1; ColumnWidths = 0;2"

This will display the alphabetized certification text but store the ID.

Yep, I've added the CertID to the junction table. After I've added Control and Row Sources you advised, I am still getting the "Control can not be edited; it's bound to Autonumber field" I feel like it is trying to edit the CertificationID field in the CertifictionID and not ouputting the number the field in the junction table. This is what is suppose to happen correct?

I think I found my problem. The subform was not based on the junction table. I also added a certifictiontypeID to the junction table and created a 1:M relationship between the certificationtype table and the junction table. Does this sound right?

Ah Sorry! I was not able to find a delete or edit function for my last reply, so I'm writing another reply. I understand that lookup tables are not recommended; however, I was just wondering how this would kind of look like if I initially went that route? I was told that I would want to make the Certification and Certification Type a concatenated key and create a new table but I got lost after that. Would the lookup table be something like this? Thank you again for all of your your help.

1 ACLS Provider
2 ACLS Instructor
3 ATLS Provider
4 ATLS Instructor
5 BLS Provider
6 BLS Instructor
7 PALS Provider
8 PALS Instructor

There's absolutely nothing wrong with lookup tables; what a lot of us object to is lookup fields in tables (the notorious Microsoft Lookup Wizard). Lookup tables are essential and universal, and the table structure you describe looks fine. Your subform could have, in addition to the Junction table's (not the certifications table itself) CertID, another combo box bound to the CertificationTypeID.

Thank you for the explanation of the lookup table versus a lookup field in the database. As of right now, I have a working subform which I can select from a combobox ACLS/ATLS/BLS/PALS and then second combobox for Instructor/Provider. Then as I enter that in, I can keep adding various certifications. The max a faculty member can have is one of each of these four certifications. Would it be possible to have a all four of the certifications listed on the subform kind of like a label and only have a combobox to select Instructor/Provider?

ACLS - Instructor/Provider - Exp Date
ATLS - Instructor/Provider - Exp Date
BLS - Instructor/Provider - Exp Date
PALS - Instructor/Provider - Exp Date

Then if they were not certified, I would just leave the Instructor/Provider and Exp Date fields blank, but the certification would still show up on the form.

You might want to use a Listbox Control on the subform displaying the four certifications. I'm not quite sure I understand the question though! If you just want a label displaying what certs are possible, without reference to the individual faculty member, sure; but I don't see what purpose that would serve.

Oh haha, let me explain myself a little bit better. I will list the faculty member's name on the form and on the subform is where I would like to list their certifications. A faculty member can not be certified with the same certification more than once, so they can have a maximum of 4 different certifications (ACLS, ATLS, BLS, PALS). On the subform, I would like to have those 4 permanently listed and the option to select if they are a provider or instructor beside each certification.

hrm. A bit tricky! You could have a sub-subform based on a query joining the junction table between faculty and certifications to the certifications table, using a "Right Outer Join" to show all records in Certifications and matching records in the junction table; or you could use a similar query as the rowsource of a multiselect Listbox, with a little VBA code to update the table when the user selects a new certification.