find the most current record

[Liz Hansen]

Hi,

I have created a query which is looking at two data tables. It displays the
following information:

Customer InvDate Invoice StockCode Price
CUS001 01/01/04 10001 A-1 $10
CUS001 01/01/04 10001 A-2 $15
CUS001 02/01/04 10002 A-2 $15.50
CUS002 01/01/04 10003 A-1 $9.95

I need to group the information by Customer and Stock Code. And I only need
to display the most current data looking at the Invoice Date. So using the
above data I would get this:

CUS001 A-1 $10
CUS001 A-2 $15.50
CUS002 A-1 $9.95

I'm assuming that I need to use a WHERE statement in the Invoice Date field
but I'm not sure on how to go about it.

Hope I'm making sense.

Thanks in advance!

Liz

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a subquery to find the Max InvDate:

SELECT Customer, StockCode, Price
FROM query_name As Q
WHERE InvDate = (SELECT MAX(InvDate) FROM query_name
WHERE Customer = Q.Customer AND
StockCode = Q.StockCode)
ORDER BY Customer, StockCode

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQSO1G4echKqOuFEgEQLYNACdH33kxi6K2XTWCOz7KKfi4wY7WkcAnRW1
aZBRh1KFuwQv6Rd5GB5cevNQ
=HaC4
-----END PGP SIGNATURE-----