Field Type

[Guest]

I am trying to set up an emloyee table that contains employee education,
position and certification information. I will need to do reports in the
future on # of people with each type of certification per unit.

I want to set up a field called Certifications which includes a list of all
types of certifications. However, since a person can have more than one
certifications, I don't know which filed type I should set for it so that it
can get multiple choices?

Can anyone help me on this? or there is another way to approach the problem?

Thanks a lot!

Ally

 

[Allen Browne]

Hi Ally

You need 3 tables to do this:
1. Certification table: one record for each type of certification, with a
CertificationID primary key.

2. Employee table: one record for each person, with an EmployeeID primary
key.

3. EmployeeCertification table, with fields:
EmployeeID which employee
CertificationID what certification.
So, if Mary has 3 certifications, she has 3 records in this table.

You interface this with a main form bound to the Employee table, and a
subform bound to the EmployeeCertification table. The subform has a combo
box for selecting the certification, and you add as many rows as you need
for the person in the main form.

Here is a similar example for you to read further:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

 

[Guest]

Allen,

Thanks so much for your prompt response and the link you provide! It's just
so helpful for someone like me. I guess I could do the same way with the
Advance Practice field since a person could have more one advance practice.

I am sure I will ask more questions as I am moving forward on this project.

Thanks again!

Ally