Look-up trouble

[tmarsh]

Hello. Recently posted for advice on building an access database on health service training. I had a lot of help but I suppose I haven't really grasped relationships yet. My database looks like this:

tb_staff containing staff_id, first_name, last_name, base, dept_ward, job_title
tb_courses containing course_id and course
tb_training_done containing staff_id, course_id, date_trained, trainer, venue

I have 1:M tb_staff to tb_training_done and the tb_training_done is linked to the tb_courses 1:1 - not sure if this is right but it's the only way access will do it.

Anyway, my idea was to use a form and subform to enter training done but after setting up the lookup it won't allow duplicate courses i.e. same course done each year. Can anyone explain how the lookup works? Where are the details of courses done stored?

Hope that's not too vague?

 

[tmarsh]

Access (Jet) determines the cardinality of the relationship by examining the primary keys of the related tables and the columns chosen to form the relationship. Your training_done table doesn't have the correct primary key. If a person may only attend a course ONCE, the pk should be a compound key comprised of Staff_ID and Course_ID. To make a compound pk, select one of the fields to highlight it and while holding down the cntl key, select up to 9 more. When all necessary fields are highlighted, press the key icon on the toolbar.

If a person may under some conditions take a course multiple times, you'll need to add an autonumber to use as the primary key for this table or increase the pk to three fields. The third field would be the date_trained. I prefer an autonumber.

Changing the pk on the training_done table will change the relationship (you may need to remove the existing relationship before you can change the pk of the table) to 1-many and solve your form problem.

Thanks Pat - beleive it or not I figured that out by looking at a few examples. I've now got a 1:n I'm highly pleased with myself!!

I've attached the database and would be grateful if you would have a look at it. Earlier posts had suggested that I have a staff/courses/training done tables and link them. Just having the staff and training done tables linked seems to give me what I want but I'm not sure if it's correct.

One person can take a course more than once but always on a different date.