Serial number Query

[Kevin]

I need to get some ideas on how to sort my inventory. I
I have an inventory table that contains 4 fields which
are,
Equipment to, Equipment from, serial number and Date.
I need to query this table to show me the latest date of
all serial numbers. Our inventory is scanned in and out of
the Technicians inventory and can change hands several
times so there is duplication in the serial numbers field
and by getting a query to show me all serial numbers with
the latest date would give me my inventory status on each
of my technicians truck. I can find the inventory for each
technician but it is showing me every serial number that
was scanned to the technician which may not be on their
truck anymore. By seeing the last date for their inventory
should solve the problem. any help will be
appreciated. Kevin

 

[John Viescas]

Try this:

SELECT Inventory.*
FROM Inventory
WHERE Inventory.[Date] =
(SELECT Max([Date])
FROM Inventory As I2
WHERE I2.[Serial Number] = Inventory.[Serial Number])

That should select the record with the latest date for each inventory item.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)