I have a main form with client details on it and a subform showing when I last communicated with them and any comments they made.

I'm trying to set up a query that checks if the last time I
communicated with them was more than a month ago. How do I get Access to only look at the last communication date for a particular client and not all of them?? I feel I ought to be able to use the "Max" function, but I can't get it to work.

Please help - it's driving me crazy!
Thanks.

this should do the trick, assuming that you have multiple entries for Fred.

i.e

Name ComDate
Fred 02/02/02
Bob 02/02/02
Fred 03/02/02

SELECT Test.Name, Max(Test.ComDate) AS MinComDate
FROM Test
GROUP BY Test.Name
HAVING (((Test.Name)="Fred"));


hopefully this is what ur trying to d..

Mrx

Thanks for the suggestion, but it's not quite what I'm trying to do. I think perhaps I didn't explain the situation correctly.

My main form (Learners) has client details and my subform (Learner Communication) has multiple communication dates for this client.

I need to contact my clients about once a month. My plan is to print off a list at the beginning of each week telling me who I have not been in contact with for more than a month, but as I said, I can't set up the query to only check the most recent communication date for each client.

Any other suggestions would be gratefully received.
Thanks.

You can do this with a coorelated subquery or with nested queries. Here's the nested query method:

Query1:

Select ClientID, Max(ContactDate) As MaxContactDate
From YourTable;

Query2:

Select q.ClientID, t.ContactDate
From Query1 as q Inner Join YourTable as t ON q.ClientID = t.ClientID AND q.MaxContactDate = t.ContactDate
Where q.MaxContactDate < Date() -30;

To run Query1, you will need to add GROUP BY ClientID at the end of the SQL statement.


In fact you can discard Query2 by modifying Query1 like this:-

SELECT ClientID, Max(ContactDate) AS MaxContactDate
FROM YourTable
GROUP BY ClientID
HAVING Max(ContactDate) < Date()-30