Junction Tables
Search MS Access Help and these forums for the phrase "Junction Table".
Given:
tblParticipants
autoParticipantKey = autonum (PK)
txtParticipantName = text
(etc., etc.)
tblWorkshops
autoWorkshopKey = autonum (PK)
dtmWorkshopDate = date
txtWorkshopName = txt
(etc., etc.)
Create a third table to link them together. This table will have two fields in each record. Each field is a long integer and they are BOTH set as Primary Key (to create a complex key):
tblLinkWorkshopToParticipant
lngParticipantID = long int (PK)
lngWorkshopID = long int (PK)
In the relationships window, join tblWorkshops.autoWorkshopKey to tblLinkWorkshopToParticipant.lngWorkshopID as a one to many. Do the same for autoParticipantKey and lngParticipantID.
Entries in this table will represent the intersection of any particular participant to any particular workshop. To add Participant X to Workshop Y, create a new record in the linking table where lngParticipantID = X and lngWorkshopID = Y. To find out if Participant X is booked for Workshop Y, do a query to see if there is a record in the table where lngParticipantID = X AND lngWorkshopID = Y. To remove Participant X from Workshop Y, run the same query and delete the found record. You can also use this method to build queries to show "All workshops attended by Participant X" or "All participants attending Workshop Y". Extend it further and you can show all participants with a "yes" or "no" next to them based on whether or not they are attending a given workshop...
Anyway, play around with it. If you need any help, just shout out.
--Conjunction-Junction Mac
P.S. to all who read the earlier version of this post: I never realized it was also the "official" answer! D'Oh!
