Forum Discussion
Access Query Problem
A query like this should tackle your request:
SELECT lc.BusinessID, lc.BusinessName, lc.LastContactDate, tcc.Contact, tcc.ContactMemo
FROM tblCustomerContacts AS tcc
LEFT JOIN (
SELECT Max(b.BusinessID) AS BusinessID, Max(b.BusinessName) AS BusinessName,
Max(cc.Contact) AS Contact, Max(cc.ContactDate) AS LastContactDate
FROM tblBusiness AS b INNER JOIN tblCustomerContacts AS cc ON b.BusinessID = cc.BusinessID
GROUP BY b.BusinessID) AS lc ON tcc.BusinessID = lc.BusinessID
WHERE tcc.BusinessID = lc.BusinessID AND tcc.ContactDate = lc.LastContactDate;
lc (in the line GROUP BY b.BusinessID) AS lc ON tcc.BusinessID = lc.BusinessID) stands for last contact and is the name of the derived table which is used for the join.
Hope this works for you.
Best wishes,
Tieme
- Its been a while since this was posted but I hav a similar issue, I have customers on one table and customers appointments with us on another. These are linked with customer ID and each appointment visit has its own unique ID too. I want a query to pull up the last appointment information for each customer by the last date they been seen. Then I want it to group information together, eg some customers are in different categories for each different appointment and I want it to pull up the last category by the last date for each customer. The code above confuses me when it's typed like that. I only know how to add it into query design rather than the code. Hope I hav explained enough, if not I am willing to tell u all the different fields if this helps. When I've tried to use max or last, it's pulling up maybe their second visit rather than the third visit (most recent)
