Report query for most recent date

  • Thread starter Thread starter Todd
  • Start date Start date
T

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
 
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
 
Awesome! Thank you, Karl!

KARL DEWEY said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top