Query to return latest prices for different products stored in the same table???

[alan_conoco]

Hello,

I have a table (in MS Access 2000) which stores data on the sales of
various different products, the sale price is variable.

Time Price Product
1:00 60 a
1:02 61 a
1:04 50 b
1:05 25 c
1:06 49 b

I would like to retrieve the latest sale price for each product.
Therefore (based on the above) the query should return

Time Price Product
1:02 61 a
1:06 49 b
1:04 50 c

I can do this using the TOP function for one product but am struggling
to do it for many products.

Any ideas would be greatly appreciated.

Many thanks,

Alan

 

[Lynn Trapp]

Try something like this:

SELECT Max(Time), Price, Product
FROM YourTable
GROUP BY Product, Price
ORDER BY Product;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Ken Sheridan]

Alan:

Use a subquery to get the MAX time per product and restrict the outer
query's result set to rows where the Time matches the value returned by the
subquery:

SELECT *
FROM YourTable AS T1
WHERE Time =
(SELECT MAX(Time)
FROM YourTable AS T2
WHERE T2.Product = T1.Product);

BTW I'd avoid terms like Time or Date as column names. They could be
confused with the built in Time or Date functions . Use something like
TransactionTime or whatever suits the context.

Ken Sheridan
Stafford, England