Database/form design

[debinnyc]

I have created a db that is structured like this:

tblEmployees
EmployeeID pk
FullName
Email
JobTitle
Phone
Section

tblCertification
CertID pk
Certification

tbl Category
CategoryID pk
Category

tblDetails
ID pk
EmployeeID 1 to M
CertID 1 to M
CategoryID 1 to M
JobTitle
Certification
Expiration Date
Category

My issue: I created a form to enter certifications acheived, by using a
combo to select employee, certification and category and then enter text box
for expiration. Resulting data should be deposited into tblDetails, however I
cannot get tbl details to populate FirstName, JobTitle, etc based on the
entries on the form. When I set control source for the three combos to
EmployeeID, etc it enters the proper id from each connected table. However, I
want to have the additional information pull as well. If I should design it
another way, please let me know as I have not entered real data yet, only
testing. Thanks!

 

[Beetle]

You should *remove* the following fields from tblDetails;

JobTitle
Certification
Category

That information is already stored in your other tables, so
storing it again here is redundant. As long as you have the
key value (EmployeeID, CategoryID, CertID) you can *display*
any other info that you need from those tables. You could do
this by using DLookup in the control source of an unbound
text box, or the Column property of your combo box, etc.

If you need the data for a report, you can bring it all together
in a query, then use the query as the record source for your
report.

Also, you should use separate FirstName and LastName fields
in tblEmployees (instead of FullName). You can easily concatenate
the First and Last name fields for display purposes whenever
necessary, but it should not be stored as FullName.

Post back if you have more questions.