Using MS Access/Latest Dated Stock Record
I have a database with 4 fields: StockIDNumber,DateofStockRecord,Location,SubLocation
The StockIDNumber repeats in the table for a particular item of stock because the location of the stock changes. So I am recording say
ABC123, 5/2/13, Shop, Shelf1 for the 4 fields above as a record, then
ABC123, 12/2/13, Shop, Shelf2
so there is only 1 unit of ABC123 actually in stock and the latest record for ABC123 is the one dated 12/3/13.
How can I run a query which selects the latest dated record and returns all 4 fields?
I created a sub-query which worked, but then fell over when I had different StockIDNumber but that had a date that was the most recent for it, but not the most recent for other units of stock.
Any help very appreciated.
First you need to have a PK for this table, preferably an Autonumber. Then I think you need to use a subquery here. Something like:
SELECT StockID, StockIDNUmber, DateOfStockRecord, Location, SubLocation
FROM tablename AS A
WHERE StockID IN(SELECT Top1 StockID FROM tablename AS B WHERE A.StockIDNumber = B.StockIDNumber ORDER BY DateOfStockRecord DESC);
The subquery should return the latest dated record for the each StockIDNumber.
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA