Access Query Problem

[angelr21]

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.

[#2]

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

[#3]

@angelr21 

[#4]

@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

[#5]

@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

[#6]

@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

[#7]

@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

[#8]

@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

[#9]

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)