Query - Latest Price of a Product

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table similar to the following:-

Cust, Product, Date, Price
================
CustA, ProdA, 3/1/2005, 1.20
CustA, ProdA, 3/2/2005, 1.25
CustA, ProdB, 3/3/2005, 2.00
CustA, ProdB, 3/5/2005, 2.10
CustA, ProdB, 3/4/2005, 2.20

How do I create a query that retrieve only the latest pricing of a product
for a specific customer?

The result of the query should returns:-

CustA, ProdA, 3/2/2005, 1.25
CustA, ProdB, 3/5/2005, 2.10

Can anybody help?

Thank You,
mfwoo
 
Woo,

You need a query along the lines of:

SELECT Cust, Product, Max([Date]) AS PriceDate, First(Price) AS LatestPrice
FROM [Your Table Name]
GROUP BY Cust, Product

Tip: avoid the use of Date as a field or other object name, it is an
Access reserved keyword and can get you into trouble, with Access
thinking it is the function returning the current system date (this is
why I have put it in square brackets). Suggest you chnage the name to
something else, like EffectiveDate (?).
Check the following link for a list of reserved keywords:

http://support.microsoft.com/default.aspx?scid=kb;en-us;321266

HTH,
Nikos
 
Back
Top