Find the latest purchase for each customer

[Aubreylc]

I recall it being fairly easy to find the latest purchase record for each customer but find myself running in circles.



Out of the sample data below, I would look to return the whole record for 2006. I have 700K records to work through contained in one imported spreadsheet.

Customer	TradingPartner	DeploymentID	PurchaseDate
Acme	Warner Bros.	10001	11/16/1995
Acme	Warner Bros.	10001	2/18/2006

Does anyone have any suggestions?


-Aubrey

[ranman256]

Q1, make a MAX query on the purchase table, with only 2 FIELDS: CustID, Max(PurchDate)
then
make Q2, use Q1 and join to the tCustomer table to get the customer info.

select * from tCustomer,Q1 where tCustomer.CustID = Q1.CustID

(the MAX query must only have 2 fields because you want the MAX date of CUSTID.)

[Aubreylc]

Thanks, I believe I got it working.
The thing I was missing was:
creating a relationship between the DeploymentID's in both the qry and tbl, in addition to creating a relationship between the MaxDate in the qry and the Date in the table.