Forum Discussion
angelr21
Aug 06, 2020Copper Contributor
Access Query Problem
I have a simple two table sales database. The first table lists the business name (tblBusiness) and associated information. The second table lists each time I have contacted the business for a sales ...
Woldman
Sep 17, 2020Iron Contributor
A query like this should fulfill 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 ROM tblBusiness AS b INNER JOIN tblCustomerContacts AS cc ON b.BusinessID = cc.BusinessID ROUP 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