Form Structure Question

[BYoung]

Here's what I've got:
tblEmployeeInfo (contains all employee info, name, ID #, company phone, etc)
tblTrainingRecords
chrEmpID (FK to EMP ID in tblEmployeeInfo)
chrTrainingTopic (lookup field to tblTrainingTopic)
chrTrainingDate
chrTrainingFrequency (New hire, annual, etc)
tblTrainingTopic
idsTrainingTopicID
chrTrainingTopic

I set the training record table up this way to help with "real estate", I
believe I read that records are cheap while fields are expensive, and since
there are about 26 training topics I didn't want a bunch of empty spaces if
someone hadn't completed the training.

I want to create a Form that will be opened from a command button on another
form, the frmTrainingRecords would open with the employee's name and ID at
the top, then all their Training records below. I've already got that
figured out. However I would like the form to show the employee's
information and automatically show all 26 training topics with a place to put
the date and frequency next to them, regardless of whether the training had
been completed or not. Currently my form will only show the topics which
have been completed, so I can't see what hasn't been completed.

Does this make sense?

 

[Guest]

I see two choices here.
One would be to de-normalize and write a record for each employee in the
TrainingRecords table whether they've taken the training or not.

Another would be to unbind the child portion of your form, the part (a
subform, I'm guessing) that contains the training records. Instead of basing
this form solely on the training records table, you could base it on a union
query that would combine the records from the training records table and
unmatched records from the TrainingTopics table, giving you a complete list.
Then, you'd need a way to update an individual "record". You wouldn't be able
to directly update records through a union query, so you'd need to write some
code that allows the user to select a record which perhaps fills some unbound
textboxes. You could then insert/update records into the TrainingRecords
table based on the values in the textboxes/comboboxes.

I hope this is clear.

Barry