Table Data! Help Needed

[saqibawr]

How to create a field that if data is selected in one table from parent table in a child table it is not available for other child tables.
For example in a CD rent shop there are tables of costumers, CD'S, Loan, Return, Reserve.
Now a costumer wants to reserve a CD and the data from CD table is selected in reserve table. Then how to make it possible that this reserved CD is not available for loan to other costumers.

 

[dkinley]

You could add a "Status" field to your table.

And you could check the status ... If status is loan then you cannot loan this CD. Or on your lookup for availability, then status <> loan and it won't show up in the list.

-dK

 

[saqibawr]

I didn't understand that how to do it i tried it but its not working. I want to create lookup fields in both the tables if CD table contain A,B,C,D and i select B in reserve table then in loan table only A,C and D are available. If someone can create this and upload in zip format that will be awesome.

 

[dkinley]

Here is the concept ... look at the queries.

-dk

 

[boblarson]

I didn't understand that how to do it i tried it but its not working. I want to create lookup fields in both the tables if CD table contain A,B,C,D and i select B in reserve table then in loan table only A,C and D are available. If someone can create this and upload in zip format that will be awesome.

Don't create lookup fields at table level. Use forms. See here for more about why:
The Evils of Lookup Fields at Table Level

 

[saqibawr]

Thanks Mate for your help.

 

[dkinley]

No problem. The queries I put in there you could use as your underlying queries to a combo-box or to generate an onscreen list (datasheet view) to do those lookups based on a primary key or such so it is indexed properly. However, read Bob's post so you fully understand of evils of doing the lookup at the table level.


-dK