You are here:

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

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.