Report query for most recent date

[Todd]

In my database, we date-stamp into a table every time we update a client's
information. What this does is add a new record to tblNotes and sets
[NoteType] = "Client Update" and [NoteDate] = today's date.

What I need is a report that will show me all of my contacts that have not
had an update in the past 24 months. I'm guessing to accomplish this, I'll
need a query that only shows me the most recent [NoteDate] for any
[NoteType] that is set as "Client Update" for the client. I'm not sure how
to do this. Any help is greatly appreciated!

Todd

 

[KARL DEWEY]

Create a Totals query named ClientLastNote --
SELECT ClientID, Max([NoteDate]) AS MaxOfNoteDate
FROM tblNotes
GROUP BY ClientID;

Then create a query using your client table and ClientLastNote query. Click
in the client table on ClientID and drag to ClientLastNote ClientID. Click
on the line formed between the table and query, then double click the line to
open another window. Select the option to show all records from client table.

Drag down the field you need including MaxOfNoteDate.
For criteria under MaxOfNoteDate use <=DateAdd("m", -24, Date()) OR Is
Null

 

[Todd]

Awesome! Thank you, Karl!

Create a Totals query named ClientLastNote --
SELECT ClientID, Max([NoteDate]) AS MaxOfNoteDate
FROM tblNotes
GROUP BY ClientID;

Then create a query using your client table and ClientLastNote query.
Click
in the client table on ClientID and drag to ClientLastNote ClientID.
Click
on the line formed between the table and query, then double click the line
to
open another window. Select the option to show all records from client
table.

Drag down the field you need including MaxOfNoteDate.
For criteria under MaxOfNoteDate use <=DateAdd("m", -24, Date()) OR Is
Null

In my database, we date-stamp into a table every time we update a
client's
information. What this does is add a new record to tblNotes and sets
[NoteType] = "Client Update" and [NoteDate] = today's date.

What I need is a report that will show me all of my contacts that have
not
had an update in the past 24 months. I'm guessing to accomplish this,
I'll
need a query that only shows me the most recent [NoteDate] for any
[NoteType] that is set as "Client Update" for the client. I'm not sure
how
to do this. Any help is greatly appreciated!

Todd