Forum Discussion

angelr21's avatar
angelr21
Copper Contributor
Aug 06, 2020

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 ...
access
Woldman's avatar
Woldman
Iron Contributor
Sep 17, 2020

angelr21 

 

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