Access Query Problem

Highlighted
Visitor

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 call (tblCustomerContacts). Fields for this table are ContactDate, Contact, and ContactMemo.

What I would like to do is produce a query that lists the last time I had contacted the business (ContactDate) and associated Contact information and ContactMemo associated with that last date.

I created a group query and was able to show the last contact date for each business by doing "Group By" for the BusinessID and then doing "Max" for the ContactDate. Problem is I do not know how to show the ContactMemo associated with the last ContactDate. When I put "Max" for the ContactMemo it shows me the last record by alphanumeric order and it is not necessary associated with the last ContactDate.

7 Replies
Highlighted

@angelr21 As a general rule, posting the ACTUAL table structures (i.e all of the field names in all of the related tables), along with some sanitized sample data is more effective than general descriptions of some parts of the objects in communicating the basic facts that allow people to ferret out possible problems and solutions. Thanks.

Highlighted
Highlighted

@angelr21 

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

Highlighted

@angelr21 

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

Highlighted

@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
                   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

Highlighted

@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
                   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

Highlighted

@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