Largest Date

[Emma]

Hi I have a query which gets Client ID and Case Note Dates. So the list is
something like:4 05/05/2009
4 06/05/2009
5 05/04/2009
5 06/02/2009
etc..
So how do I make the list smaller so only the largest dates are shown
ie 4 06/05/2009
5 06/02/2009

I would like to use a criteria in the query

 

[Wayne-I-M]

Hi Emma

This will do what you want

SELECT TableName.ClientID, Max(TableName.CaseNoteDates) AS MaxOfCaseNoteDates
FROM TableName
GROUP BY TableName.ClientID;


Of course change Table Name and other "stuff" to what you really have in
your appliction

 

[Emma]

Hi Wayne here's what I have right now,DateDiff("d",[Case Note
Date],Now()),60. I'll try adding the Max factor, can you help me?

 

[Emma]

Here's my code it's returning the correct Client Id's but instead of a Date
it's giving me the number of days greater then 60. How do I return the
original date?

SELECT [Case Note Client].[Client ID], Max(DateDiff("d",([Case Note
Date]),Now())) AS [Case Note Date Max]
FROM [Case Note Client]
GROUP BY [Case Note Client].[Client ID]
HAVING (((Max(DateDiff("d",([Case Note Date]),Now())))>60));

Hi Wayne here's what I have right now,DateDiff("d",[Case Note
Date],Now()),60. I'll try adding the Max factor, can you help me?

Hi Emma

This will do what you want

SELECT TableName.ClientID, Max(TableName.CaseNoteDates) AS MaxOfCaseNoteDates
FROM TableName
GROUP BY TableName.ClientID;


Of course change Table Name and other "stuff" to what you really have in
your appliction

 

[Wayne-I-M]

HI Emma

Not really sure what all the >60 is about (you must do though) but to get
the latest date for each Client ID use this

SELECT [Case Note Client].[Client ID], Max([Case Note Client].[Case Note
Date]) AS [MaxOfCase Note Date]
FROM [Case Note Client]
GROUP BY [Case Note Client].[Client ID];

Note there is no sorting or anything like that on this - you can adjust it
later aftet you get basicaqlly what you need

HtH




--
Wayne
Manchester, England.

Here's my code it's returning the correct Client Id's but instead of a Date
it's giving me the number of days greater then 60. How do I return the
original date?

SELECT [Case Note Client].[Client ID], Max(DateDiff("d",([Case Note
Date]),Now())) AS [Case Note Date Max]
FROM [Case Note Client]
GROUP BY [Case Note Client].[Client ID]
HAVING (((Max(DateDiff("d",([Case Note Date]),Now())))>60));

Hi Wayne here's what I have right now,DateDiff("d",[Case Note
Date],Now()),60. I'll try adding the Max factor, can you help me?

Hi Emma

This will do what you want

SELECT TableName.ClientID, Max(TableName.CaseNoteDates) AS MaxOfCaseNoteDates
FROM TableName
GROUP BY TableName.ClientID;


Of course change Table Name and other "stuff" to what you really have in
your appliction
--
Wayne
Manchester, England.



:

Hi I have a query which gets Client ID and Case Note Dates. So the list is
something like:4 05/05/2009
4 06/05/2009
5 05/04/2009
5 06/02/2009
etc..
So how do I make the list smaller so only the largest dates are shown
ie 4 06/05/2009
5 06/02/2009

I would like to use a criteria in the query

 

[Emma]

Hi Wayne, yes the max is working and now I'm trying to find the Max(Case Note
Date) > 60 So essentialy I'd like to have a report of Dates and Client ID's
where the Case Note was written 60 days or more and only the max case note(so
I only get one Case note date). Does this make any kind of sense?

 

[Wayne-I-M]

LoL - no you lost with that one.

Do you want to see only max case note that are at least 60 days old
or
Do you want to a date that is 60 days after the max case was written
or
etc
etc

sorry, I don't understand what you need

 

[John Spencer MVP]

If I understand you want the client id and the latest case note that is 60
days or older from today's date. If that is the case, the SQL should look
something like the following

SELECT [Case Note Client].[Client ID]
, Max([Case Note Client].[Case Note Date]) AS [MaxOfCase Note Date]
FROM [Case Note Client]
WHERE [Case Note Date] < DateAdd("D",-60,Date())
GROUP BY [Case Note Client].[Client ID]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Emma]

Thanks John, you are the smartest guy ever! It totally worked, Thanks!

If I understand you want the client id and the latest case note that is 60
days or older from today's date. If that is the case, the SQL should look
something like the following

SELECT [Case Note Client].[Client ID]
, Max([Case Note Client].[Case Note Date]) AS [MaxOfCase Note Date]
FROM [Case Note Client]
WHERE [Case Note Date] < DateAdd("D",-60,Date())
GROUP BY [Case Note Client].[Client ID]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi Wayne, yes the max is working and now I'm trying to find the Max(Case Note
Date) > 60 So essentialy I'd like to have a report of Dates and Client ID's
where the Case Note was written 60 days or more and only the max case note(so
I only get one Case note date). Does this make any kind of sense?

 

[John Spencer MVP]

Not smart. Lucky.

The lucky part comes from correctly guessing what you wanted. The answer was
easy.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks John, you are the smartest guy ever! It totally worked, Thanks!

If I understand you want the client id and the latest case note that is 60
days or older from today's date. If that is the case, the SQL should look
something like the following

SELECT [Case Note Client].[Client ID]
, Max([Case Note Client].[Case Note Date]) AS [MaxOfCase Note Date]
FROM [Case Note Client]
WHERE [Case Note Date] < DateAdd("D",-60,Date())
GROUP BY [Case Note Client].[Client ID]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi Wayne, yes the max is working and now I'm trying to find the Max(Case Note
Date) > 60 So essentialy I'd like to have a report of Dates and Client ID's
where the Case Note was written 60 days or more and only the max case note(so
I only get one Case note date). Does this make any kind of sense?

:

Hi Emma

This will do what you want

SELECT TableName.ClientID, Max(TableName.CaseNoteDates) AS MaxOfCaseNoteDates
FROM TableName
GROUP BY TableName.ClientID;


Of course change Table Name and other "stuff" to what you really have in
your appliction
--
Wayne
Manchester, England.



:

Hi I have a query which gets Client ID and Case Note Dates. So the list is
something like:4 05/05/2009
4 06/05/2009
5 05/04/2009
5 06/02/2009
etc..
So how do I make the list smaller so only the largest dates are shown
ie 4 06/05/2009
5 06/02/2009

I would like to use a criteria in the query

 

[Wayne-I-M]

:-(

sad

--
Wayne
Manchester, England.

Thanks John, you are the smartest guy ever! It totally worked, Thanks!

If I understand you want the client id and the latest case note that is 60
days or older from today's date. If that is the case, the SQL should look
something like the following

SELECT [Case Note Client].[Client ID]
, Max([Case Note Client].[Case Note Date]) AS [MaxOfCase Note Date]
FROM [Case Note Client]
WHERE [Case Note Date] < DateAdd("D",-60,Date())
GROUP BY [Case Note Client].[Client ID]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi Wayne, yes the max is working and now I'm trying to find the Max(Case Note
Date) > 60 So essentialy I'd like to have a report of Dates and Client ID's
where the Case Note was written 60 days or more and only the max case note(so
I only get one Case note date). Does this make any kind of sense?

:

Hi Emma

This will do what you want

SELECT TableName.ClientID, Max(TableName.CaseNoteDates) AS MaxOfCaseNoteDates
FROM TableName
GROUP BY TableName.ClientID;


Of course change Table Name and other "stuff" to what you really have in
your appliction
--
Wayne
Manchester, England.



:

Hi I have a query which gets Client ID and Case Note Dates. So the list is
something like:4 05/05/2009
4 06/05/2009
5 05/04/2009
5 06/02/2009
etc..
So how do I make the list smaller so only the largest dates are shown
ie 4 06/05/2009
5 06/02/2009

I would like to use a criteria in the query