Relationship problem

[Quan9r]

I'm trying to track training for employees -
I have a table of 10 employees with a unique ID
and I have a table listing their training criteria - it also has a unique ID. I want to place a date next to the criteria once the employee has been trained. My problem is when I create a form of the employees and a subform of the criteria it doesn't pull up all of the criteria -
I have a Many[Criteria] to One[Employee] -

Should I create a seperate table and link both tables by their ID?

I know this is basic Db design but I'm suffering on this one...any help would be appreciated - How would you set it up?

 

[AlexMidd]

I thought you had separate tables, from your description. You should have, as you appear to, a 1-Many relationship between employee and criteria. When you add your subform to your main form, select "Link Master Fields" in the properties box for the subform. You should get a dialog that will default the fields to the primary key in the employees table, and the field in the criteria table that you use to tell it which employee the record applies to (foreign key). This field should be in the same format as the primary key in the employees table, and contain the appropriate value to correctly identify the employee.

Foloowing all this, it should work. If not, let us know what is going wrong. Have fun! )

Alex Middleton

 

[Quan9r]

Thanks for the response ~
Since my subform is connected to the form by the employeeID - That when I go to form view I do not get any of the criteria from the table because there isn't any data in the criteria table - where I planned on placing the date of training.

I'm confused as to where I should put the training date field...
Also, I would like to see all of the criteria for each emlployee ...it only show the ones that are connected...make sense?...Thanks for you help -

 

[FoxProProgrammer]

I answered this earlier but it didn't show up due to a server error. Let's try again.

It sounds like you almost have it set up properly. All you have to do is create another field in the Training table to match the ID in the Employee table. The name of the fields don't have to match, although this makes it easier to track. What does have to match is the number in the ID fields of both tables. Example:

Employee table
NameID Name
1 Joe
2 Mary
3 Quan

Training table
NameID TrainingID Course Name
1 1 DB Design
1 2 Access 2000
2 1 DB Design

Hope this helps.

dz