Items In Stock Problem

[AN60]

I have a query which is based on a tbl which has data on chemicals in storage. The query is based on two tbls as follows;
Main tbl
ProducdID
ChemicalName


Second tbl
Batch number
TransactionID
Transaction date
Purchased check box
Used check box,
expirydate
ProducdID

When a chemical is purchased a tick goes in the Purchased check box with the other relavant data. When a chemical is used a tick goes in the Used box along with other relevant data in the other fields. In essence each chemical will have only two records 1. a purchased record and 2.eventually a Used record.

I want the query to report on all chemical records which are still in stock. So it's not as simple as adding a True or False to either check boxes. Showing records which have ticks in the Purchased CB ,or not showing records with ticks in the Used CB, will still show a Chemical which has been Purchased but may have been used at a later date and is no longer in stock.

Any suggestions? I hope I haven't confused anyone with my problem?

 

[wkalo]

Is That Waht You Are Looking For

See Attachment

 

[AN60]

Wakalo
Thank you for your assistance. It is not quite what I was looking for however I can probably apply some of your ideas if I have a think about them, maybe.

I really need to look at the field which contains the Batch Numbers and where I have two identical batch numbers then establish if one Batch has a related Used check box. If so then remove both batch number records.

 

[wkalo]

If You Have Sample Database I Will Gladly Try To Work On It