Question regarding using 2 tables to create 1 form

[stlryan82]

Hello everyone,

I have two tables. One table lists 6 different hospital units and the services that are associated with that unit. The other table is a list of doctors.

I want to create a form where a user can select which unit(s) a doctor belongs to and which services within those unit(s) that doctor is using.

What is the easiest way to go about this? Any help is greatly appreciated. Also, I'm sure this sort of thing has been discussed on this forum before, but I wasn't quite sure what words to use to search for this particular issue, so I apologize beforehand.

Thank you,
Ryan

 

[pbaldy]

What you're describing sounds like a many-to-many relationship. A doctor can use many services, and a service can be used by many doctors. That is usually represented by a junction table, which would typically include a primary key field of its own, plus 2 fields for the key fields from each of the other two fields. If a doctor used 3 services, they would have 3 records in this junction table.