I've inherited/modified an access database in work that keeps track of the courses we run and the beneficiaries that have attended. There exists a many to many relationship between these two particular tables as one or more beneficiaries can attend one or more courses and a single course has one or more students.
So I created a link table with beneficiary_id and course_id. However populating the table was tedious so I re-did the table to use drop-down lists so I can easily identify the beneficiary by surname, firstname and the course by name, date and venue.
But now there are two problems
1) It is no longer possible to define the relationship between the 3 tables using enforced referential integrity.
2) The drop down menus display the information I need but when a choice is made only one of the column values is displayed
Under beneficiary drop-down it lists an entry such as [Bloggs | Joe] but when selected the table only displays Bloggs.
So it's difficult to see at a glance which "Jones" attended a particular course.
The same is also true for the course entry; even though I can clearly select the correct course by date and venue in order to input an entry, it's impossible to determine who attended what course at a later date. I can't even click on the drop down menu to find what entry I selected as Access seems to default to the first entry it finds for that course name.
I hope this is all making sense
Anyway what is the best way of creating a link table in Access (2007) and what is the easiest way to populate it?
Thank you for taking the time to read this, if you need clarification please ask