How to show most current record only?

[duckster]

I am creating a query based on two tables: 1) tblClient (only one primary key "ClientID") and 2) tblContactDate (with two primary keys "ClientID" and "ContactDate").

Each client may have more than one contact date.

In the query, I only want the records from the tblContactDate showing for the MOST RECENT contact date of the client only; however, right now the query is producing more than one record for those clients with more than one contact date.

Not sure if I need add some special code to the "criteria" area in the Design View, or somewhere else. Your help is appreciated.

 

[neileg]

Use an aggregate (totals) query and use Max on the contact date.

 

[duckster]

Sorry I'm a newbie...

Do you mean I should create another query? And where do I put the term: "Max" ?

Thanks again.

 

[neileg]

Start off with the select query you already have. Click the sigma Σ button. You will see a new line appear in the grid that says Group By. Chang this to Max in your date column.

 

[duckster]

Thanks, I tried that, and changed the "group by" in the ContactDate field to "Max"; however, when I run the query it still shows more than one contact date for each client (if the client has more than one contact date).

I just need the query to show the most recent...and not the earlier contact dates...not sure what the problem is still...thanks again

 

[neileg]

Aggregate functions work on identical records. If you are including fields that have different values in different records these won't be aggregated so you'll get multiple values.

To achieve what you want, you may have to create one query that just has ClientID and ContactDate and use Max on this. You can then join this query back to your table using both fields in order to extract any additional information which would have caused the problem I explained above.