I'm trying to write a system to manage archived materials.
I have a system for logging people removing items or boxes of items, and now want a query that returns all the boxes, and the current status of all of the boxes. Not all boxes have log entries though.
The logs are stored in a table of logs, and related to the a table of boxes by the boxes primary key.
My current attempts either do not return all the boxes (only those with log entires) or return multiple copies of those boxes that do have log entries (one copy per log entry) and single copies of the boxes without log entries. What I really want is it to return the most recent entries from the log only (each log entry has check out and check in date fields).
Can anyone suggest how I'd go about achieving this?
Thanks