Thread: Finding latest date with query

I need to pull only the records with the latest date into Excel from Access. As a simplified example, let's say the Access database has two tables:

Table T1 is price history with fields PartNum, PriceDate, Price. For each PartNum, there are multiple dates (PriceDate) and associated prices (Price).
example:
P1, 1/1/10, $1.00
P1, 2/1/10, $2.00
P1, 3/1/10, $3.00
P2, 1/1/10, $4.00
P2, 2/1/10, $3.00
P3, 1/1/10, $1.00

Table T2 has cost data and has fields PartNum, Cost.
example:
P1, $0.50
P2, $0.75

Using a query executed from within Excel, I need to pull into Excel from Access all PartNum's where the PartNum is in both tables, the latest PriceDate and Price for each PartNum, and the Cost for each PartNum.
results example:
P1, 3/1/10, $3.00, $0.50
P2, 2/1/10, $3.00, $0.75

I've tried this but it doesn't work.

Select T1.PartNum, max(T1.PriceDate), T1.Price, T2.PartNum, T2.Cost
From T1, T2
Where T1.PartNum = T2.PartNum

I'd appreciate any help here.

I got this to work in Access by using two queries. Attached is the dB I created and the two queries. You can then export the second query to excel, or you can adapt your excel query to mirror the same one in Access. Good luck.

Alan