One to many query - duplicate processing

[djkay2637]

Good afternoon eclectic minds of database wisdom.

Ok here is my query:

I have a customer table and i have a communication table where we record all outbound and inbound communications. There are many communications for each customer. I want to run a query that displays the date the last time the customer was in contact. At current, when the query runs it will create duplicate rows for every customer as there are many dates of contact. Essentially I want it to return the max date value for each customer and only have one instance of that display. Ultimately, i want the query to show the last date the customer was in contact with us.

I hope my explanation was clear.

Thank you all in anticipation.

 

[plog]

You will need a subquery. That will get the max date of the communication along with the customer. Then you use that query inanother query to actually get the comment by joining it by customer and max date of coummunication.

 

[djkay2637]

But how would i get it include the next date in the MAX formula range if the customer ID is the same. in other words would need to to do the max value on an unspecified number of duplicates but only on the duplicates and not the whole dataset.

 

[plog]

Huh? Can you demonstrate your issue with data please? Provide 2 sets:

A. STarting sample data from your table. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what data you expect to end up with when you feed in the data from A.