I'm new to access and i'm trying to use a query to return the 2 most recent records (based on date and time) for a group of products and their price changes. Data looks like this:

ProdID Date of change Time of Change Price
UP 3/5/12 18:00 2.25
UP 3/5/12 13:00 2.37
UP 3/4/12 18:00 2.68
UP 3/3/12 18:00 2.45

UD 3/5/12 18:00 2.15
UD 3/4/12 18:00 2.58
UD 3/3/12 18:00 2.25
UD 3/1/12 18:00 2.69

I want to return the 3/5/12 18:00 and 3/5/12 13:00 price for UP and and 3/5/12 18:00 and 3/4/12 18:00 record for UD

This post has been edited by arnelgp: Mar 6 2012, 08:53 AM

You might not realize it - but you've already made a really good start in the question itself.
You've identified that you need some criteria by which to identify the order of the rows. (So often the "most recent" is offered without qualification or order of entry is assumed, without any data assistance - which it can't be.)

What you want is a TOP N per group query.
http://allenbrowne.com/subquery-01.html#TopN

If that doesn't give you enough of a pointer, then shout.

Cheers.

Thank you so much for your help. What if i don't want to explicitly specify each product in the code, but rather return the top 2 for every product in the table

SELECT D.ProdID, D.[Date of change], D.[Time of Change], D.Price
FROM Dhill23 As D
WHERE ([Date of change]+[Time of Change])
In (SELECT Top 2 [Date of change]+[Time of Change] AS Expr1
FROM Dhill23
WHERE Dhill23.ProdID = D.ProdID)
Order by D.ProdID, [Date of change]+[Time of Change] DESC;

Change all the Dhill23's to the actual table name.

NOTE: If either the Date of change or Time of Change fields are null, that record won't be returned.

Revised the above code from Mr. Whittle.

arnelgp,

Good catch. Thanks!

Mr. Whittle I am keeping your code with me. I learned a lot from this one (IMG:style_emoticons/default/notworthy.gif)

Thank you both.