limiting number of related records

[czarjosh]

i am designing a database which is meant for scheduling people to a certain location. I Wwant to be able to limit the number os records able to relate. For example.

Table 1 - People (25 records)
Table 2 - Location (3)

Each of the location records allows for a different number of people

let's say the record values for Table 2 are:

Pizza Hut
Papa John's
Dominos

Pizza hut can have 8 people, Papa John's 4, and Dominos 15

So we have 25 people to work with. When i assign 8 of the 25 to Pizza hut I do not want any more people allowed to be assigned to that. AND if i delete a person from Pizza Hut, I want to be able to add another person. No person can be in more than one place.

Any suggestions on how to accomplish this?

 

[dhookom]

Add a PeopleMax field to the location table. You can then create a combo box for selecting a location that counts the number of people currently assigned and has a criteria of <PeopleMax.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.