SQL syntax for query items to delete

[LPrange]

I'm trying to create a query that shows items from one query but deletes
those items that are also in another query. For example, an item has been
checked-out, returned, and checked out again. Qry 1 shows the items that are
not returned yet, and that works fine, but Qry 2 shows all items that were
ever returned, even the ones that are rechecked-out. How do I get Qry 2 to
pull out those items that are currently on the "checked-out" query?

 

[LPrange]

OK, never mind, I figured it out. What I had to do was go into the query
design view, click on the "View" bar on top, select "Totals" (this adds a
"totals" row to your query) then from the drop-down menu select "Last". This
pulled the information from the most current transaction.

 

[John Spencer]

It may work for you, but Last does not mean latest. It means the last
record in the group that the query sees when pulling the records to
combine.

That for most purposes could be considered random since depending on the
query and the complexity of the query the "last" record in the group
might be the first record added in the group.

Since you didn't post the SQL of your queries it is close to impossible
to give you any specific advice on solving your problem. For instance
do you have fields named checkedout and returned that are date fields or
do you have a datefield and an action type field (returned, lost,
checked in, etc).



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================