The issue is that one employee can work an many different locations over
time, and one location can have many many people work there over time. There
is therefore a many-to-many relationship betwen employees and locations.
You already have a Location table and an Employee table. You need a 3rd
table to record who is authorized to work where when:
Location table
Location_ID (primary key)
Employee table
Employee_ID (primary key)
Surname
...
Authorized_To_Work table:
Location_ID relates to a record in Location.Location_ID
Employee_ID relates to a record in Employee.Employee_ID
WordDate when this person is to work here.
Now you will have a main form bound to the Employee table.
It will have a subform bound to the Authozed_To_Work table.
The subform will have a combo where you can select the location for the
employee.
With the same tables, you could interface this by location instead of by
employee if you wish:
- Main form bound to Location table;
- subform bound to Authorized_To_Work table.
The suborm will have a combo where you can select the employee for the
location.
It's really important to grasp this concept: the 3rd table (called a
junction table) resolves the many-to-many relationship into a pair of
one-to-many relationships.
For another example, see:
http://members.iinet.net.au/~allenbrowne/casu-06.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"yb" <ya**********@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I'm totally lost. Here is what I have:
Table Location:
Location_Id Text
Table Authorized_To_Work
Employee Name Text
Locations Text
form: lblLocations
Field: Location bound to Location.Location_Id and is set to Combobox
form: WorkAuthorization
I have inserted lblLocations but cannot get it to allow multiple
selections.
Please help!!
YB