Query Confusion

[nocci]

I have setup a database in which is similar to checking in/out a library book. Everything is working fine except 1 very important query. I want to be able to run a query that shows me all items that are currently in inventory that are available to check out. It seems simple on the surface as I have a Date Issued and Date Return field in which dates are entered. But I cannot figure out how to setup the criteria or expressions to pull it how I want. If you use an "Is Null" criteria in the Date Issued field, it gives nothing in return as at one time it was issued, even though it has been returned and is available. If you use an "Is Not Null" in the Date Returned field, it gives back anything that has ever been returned even if it has been checked out again. I am sure there is a simple argument or criteria that can be setup but I have not found it yet. Any help would be appreciated.

 

[pbaldy]

It would help to know your table structure. It sounds like you have a transaction table, and presumably an items table. If so, I think you need a two query solution. The first finds all the books that are out, using your date returned field. The second compares that to your items table to find the others (unmatched query wizard).

 

[c_smithwick]

Try two criteria: Date Returned Is Not Null which will give you everything which is in stock combined with Date Issued < Date Returned which will limit the items to those which have not been re-checked out, as I assume new reissues will have a Date Issued greater than the previous Date Returned.

 

[nocci]

The structure is like this.
I have a table for all the people who can check items in and out.
I have a table that has all of the items that can be checked in/out.
I have a table that has the assignment, which contains the the date for in and a date for out.

 

[pbaldy]

Did you try my solution?

 

[nocci]

Yes, I did. It seems to have worked. Thank you!

 

[pbaldy]

No problem, and welcome to the site by the way!