Compare two tables, if record doesn't exist in one then returns re

[Guest]

I have three tables, one for tools, one for signing them out and one for
returning them. Trying to kill two birds here. First I have been trying to
figure out how to return a result for the first time a tool has been signed
out but not returned. Second once they have been signed out and returned a
few times only show record for last sign out entry that still has not been
returned. How would I go about getting results in these scenarios? Would I
be better off to combine the signout and return tables into one table and
just compare dates in record for a null return date value? I am VBA
illiterate and am working in design window, if you must post in code please
be specific on where to place it.


The tables are as follows
Tool table: tool item number (unique), model number, manufacturer, serial
number, desription, lot
Sign out table: tool item number, signed out by, date signed out, location,
condition taken
Return table: tool item number, returned by, returned date, condition returned

 

[George Nicholson]

Good no VBA for this.

YES, go with just 2 tables: Tools & ToolStatus
ToolStatus would be the signout table with returned by, returned date,
condition returned added to it.
With what you have presented I see no reason to have separate Out & In
tables. Much fewer headaches if you can combine them.

I think you have just one bird to kill, not 2, because I don't see how a
unique ToolItemNumber could be "not returned" more than once.
You might eventually replace a non-returned tool, but it would have a new
ToolItemNumber.

"Unreturned tools" query: Select * FROM ToolStatus WHERE [ReturnedBy] Is
Null

(I'm ignoring the join to ToolTable that you'll want to add to the above)

Once you decide a tool is a loss and will never be returned, you could enter
"Lost" or "WriteOff" in the ReturnedBy field (something that can be easily
queried later). This would also prevent it from appearing in the results of
your "Unreturned tools" query, since the criteria value would no longer be
null.

HTH