Query - Latest Price of a Product

[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

 

[Nikos Yannacopoulos]

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

 

[Van T. Dinh]

See The Access Web article:

http://www.mvps.org/access/queries/qry0020.htm

 

[Guest]

Thanks a lot guys.
I'll get to get right away.

Thanks
mfwoo