I am sorry. I also noticed that I made a mistake in my question. I was mixing up gears and events. Okay, I'll try to explain better what I want to do, because this post is also related to my other running post. This is the case: Within the project the user can enter several orders [which is a one to many relation ship of the tables 'Bills' and 'Orders'] .
Because there are many types of orders, I've added also one to many relation ship relations to the table 'Orders'.
One of the order types is "Gear", so I've set up here this one to many relationship: master 'Orders' and child 'Gear'. Once the specific order is accepted, ordered and arrived in stock (those relations I won't describe here), in this case a "gear" item, the specific gear item gets a GearNr. This nr is entered by a user. The table 'Gear' is build up as follows: GearID
OrdersID
StatusID
GearNr
Photo
Colors Now if this gear has arrived in stock, it should be used (once) during (one) event(-s).
(See my other post "limit the lookup values based on another field in the same table"). But the gear will not only be used during events but maybe also on specific dates. When it's used on a specific date, a user should not be able to select the same item on that date again: Table 'GearSelect'
field 1: GearSelectID (int)
field 2: Date (date) user can select any date
field 3: GearNr (varchar) based on the selected days, only items should show up that would not already have been selected for those dates. I'm trying to find out how I could achieve this: that a GearItem can only be selected depending on a date (or as in my other post, depending on a event). In the end it should be best if a user can select a gear item only, when it's not 'out on an event' or 'out on a specific date'. But because I don't know even how to achieve one of these options, I thought I'd better ask one by one and maybe I will start to understand how to achieve the solution. I am sorry that I may confuse you, this project is kind of confusing to myself, because there are so many variables. I would be very happy if you would give me some tips. You'd make my day if I know how to continue. Pim