Finding Latest Dated Record in Access

[NellyC]

I need to produce a query in Access which finds a unit price for an item for the latest dated entry.

When I try to do a summary it does not offer the option of Max on a date field.

Does anyone know how I can overcome this

 

[mdav2]

Max should work. I just tried creating a table with a field called date (don't call it this as it is a reserved word but it works for this example). I simply select the max(date) from table1 as the sql code shows below. that worked for me.

SELECT Max([date]) AS Expr1
FROM Table1;

Mark Davies
Warwickshire County Council

 

[NellyC]

Thanks for the advice. My problem is that I have a purchase Items Query which includes the fields 'Item ID', 'Order Number', 'Order Date', 'Product Code', 'Quantity' and 'Unit Price' but if I put any fields in the new query other that 'Product Code' and 'Order Date' then the results include more than 1 record for some products.

I simply need to filter this list down to show all the above fields for the latest order date for each product code.