Query to bring last date and comment of a customer follo up

[camiloeslu]

Hi.



Im trying to design a query that shows my customers follow up, showing the customer info, with its last contact date and last comment (see attached doc).

The queries I have right now (active and interest customer) show me the entire history of my customer.

I appreciate your help.

Camilo

[aytee111]

Use a subquery - see http://allenbrowne.com/subquery-01.html

[JoeM]

You can use an Aggregate Query to get the latest date for each Customer from your tblComments table.
Just add CustomerID and CommentDate to your query, click on the Totals icon, and change the words "Group By" on the Totals row under the "CommentDate" field to "Max".
Then, join this query back to the original query, matching on both the CustomerID and CommentDate fields (returning all the fields you want), and it should just return the last record for each person.

[camiloeslu]

Its not working.This is my current structure (see atachment).

[JoeM]

You have done the SubQuery and your joins incorrectly.
The Subquery should have the CustomerID_PK field, not the CommentID_PK field.
Then, when joining this Subquery to the tblComments table, you need to join on BOTH the CustomerID_PK field AND the CommentDate (to the MaxOfCommentDate) field.

[camiloeslu]

For the purpose of this exercice,ive created a database with the information. Note: Still i cannot link the info.Ill appreciate the help.

Note:
This is the backgroung of the DB Im trying to create

BACKGROUND
My paint company sells 3 different paint products cars manufacturers and importers around the world. Note: Red, blue and green paints costs $10, $20 and $35 USD per unit respectively.
I’ve created a spreadsheet in excel that keeps track of my customers history, allowing me to see their status (active, interested, inactive) and last comments regarding the last contact date (see EXCEL document attached). Note: Active means working on the project, interested means negotiating and Inactive means nothing.

OBJECTIVE
Create a DB to keep a list of customer information for the purpose following up the commercial activity and current projects. Initially, the database is intended to be used only by the commercial area, but the idea is that eventually the database will be used by each director (blue, red and greent paints directors.

[JoeM]

So why didn't you try to make the changes I suggested in my previous reply? They should be pretty straightforward.
Here are the steps you need to do:
1. Open your "SubQueryComments" query and add the CustomerID_PK field, then save and close it.
2. Open your "ActiveCustomers" query and delete the join on the CommentID_PK field, and then add a join between the CustomerID_PK field AND the CommentDate (to the MaxOfCommentDate) field. Save and close it.
3. Re-open your "SubQueryComments" query and remove the CommentID_PK from the fields that the query returns. Save and close.

Now your "ActiveCustomers" query should return just the latest comment for each Active Customer.

[ssanfu]

I was bored with a little time on my hands, so AFTER you try what JoeM suggested, take a look at my demo of your dB.
I say "After", because then you will have a learning experience.