finding MAXdate

[Guest]

I have this query (yes I know "date" is a reserved word). I would like it to find the maximum date per "cont_eduID" and only if it
is over a year old return the results. This query is finding all the dates that are older than a year and then selecting the maximum
of those (per "cont_eduID") and returning the results. anyone know how I can correct this?
Thanks a bunch!

SELECT login.first_name, login.last_name, Max(contedu.date)
FROM login INNER JOIN contedu ON login.ID = contedu.cont_eduID
WHERE contedu.date < NOW() - 365.25 GROUP BY login.last_name, login.first_name;

 

[Tom Ellison]

Dear Jimmy:

Putting a test on contedu.date in the WHERE clause causes the query to
consider only those rows that are more than a year old, and find the
maximum out of those dates. I'm pretty sure what you want is:

SELECT login.first_name, login.last_name, Max(contedu.date)
FROM login
INNER JOIN contedu ON login.ID = contedu.cont_eduID
HAVING MAX(contedu.date) < DateAdd('y', -1, DATE()
GROUP BY login.last_name, login.first_name;

In this I have used the DATE function instead of NOW so it is not time
sensitive (effectively changing the test to show all rows where the
MAX(contedu.date) is before today's date a year ago and ignoring the
time of day.

I also use the DateAdd function instead of 365.25 days, so that it
simply uses 365 days except leap years. Likely this will be more like
what you want. You should look up the functions I used and see what
difference they make, and see if that isn't more like what you want.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Spencer (MVP)]

Would a having clause work for you?

SELECT login.first_name, login.last_name, Max(contedu.date) as LastDate
FROM login INNER JOIN contedu ON login.ID = contedu.cont_eduID
GROUP BY login.last_name, login.first_name
HAVING Max(Contedu.[Date]) < DateAdd("yyyy",-1,Date())

This should return records where the max date is over a year ago for the login name.

 

[Tom Ellison]

John is right to put the HAVING after the GROUP BY. Not my best day,
I think.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Would a having clause work for you?

SELECT login.first_name, login.last_name, Max(contedu.date) as LastDate
FROM login INNER JOIN contedu ON login.ID = contedu.cont_eduID
GROUP BY login.last_name, login.first_name
HAVING Max(Contedu.[Date]) < DateAdd("yyyy",-1,Date())

This should return records where the max date is over a year ago for the login name.

I have this query (yes I know "date" is a reserved word). I would like it to find the maximum date per "cont_eduID" and only if it
is over a year old return the results. This query is finding all the dates that are older than a year and then selecting the maximum
of those (per "cont_eduID") and returning the results. anyone know how I can correct this?
Thanks a bunch!

SELECT login.first_name, login.last_name, Max(contedu.date)
FROM login INNER JOIN contedu ON login.ID = contedu.cont_eduID
WHERE contedu.date < NOW() - 365.25 GROUP BY login.last_name, login.first_name;