You are here:

Using MS Access/Latest Dated Stock Record

Advertisement


Question
Hi

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.

Thanks

Dibo

Answer
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,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

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

Experience

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

Organizations
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

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.