Show latest action for each customer

[CW]

I have a Customers table, and a related Actions table in which I store the
Actions taken for each customer such as phone calls, quotes etc, with their
respective dates.
I want to pull out just the most recent action taken for each customer,
sorted by customer - I don't want the whole history, just the latest one, so
the output would be a list of all customers with one line for each showing
the customer name, the last action, and the date of that action.
How can I do this?
Many thanks
CW

 

[John Spencer]

Build a "Totals" query that gets the date of the latest action per customer

SELECT CustomerID, Max(ActionDate) as LastActionDate
FROM Actions
GROUP BY CustomerID

Now use that query as a table in another query. Join Customers table to the
Actions Table on the CustomerID and join Actions table to the above query on
CustomerID and the dates.

SELECT Customers.CustomerName
, ActionDate, ActionType
FROM (Customers INNER JOIN Actions
ON Customers.CustomerID = Actions.CustomerID)
INNER JOIN SavedQuery
ON Actions.CustomerID = SavedQuery.CustomerID
AND Actions.ActionDate =SavedQuery.LastActionDate

If you don't work in the SQL window and can't figure out what to do, post back
and I'll try to describe the steps to take to build the queries using query
design view.
n
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[CW]

Many thanks for the prompt response, John. I'll be able to manage that, will
come back if any problems.
Thanks again
CW