In an access query, how do I set criteria for "most recent" date?

[Guest]

I'm trying to find the last time I reviewed a portfolio for each of my
clients. This would be similar to finding the most recent order from each
customer. I'm trying to create a criteria for a query where I have grouped
by client. There doesn't appear to be a function for most recent date.
Thanks, John

 

[Tom Ellison]

Dear John:

Do you have a date or date/time value in the table? The most recent date
would be obtained by the aggregate function MAX() (maximum) taken on that
column.

If your table does not keep track of the date/time of each row, then the
information that would provide this capability is missing.

If you provide complete details of what you want, I could be more specific.

Tom Ellison

 

[Guest]

Dear Tom:
Thanks for your response. I went through a hundred help topics and never
saw that mentioned. However, when I typed in Max() in the date column, I got
a "wrong number of arguments" error message. I'm obviously a beginner.

 

[Tom Ellison]

Dear John:

Please note that, in my post, I suggested you use the "MAX() (maximum) taken
on that column." Put the name of the column inside the parentheses. That's
how you get the MAXimum of that column. If the name of the column has a
space or any character other than letters or digits, put it inside square
brackets:

[column name]

Does that make it work?

Tom Ellison

 

[Guest]

Dear Tom:
This time I got "cannot have aggregate function in where clause
(tblPortfolioReview.PReviewDate=Max("PReviewDate")).

Dear John:

Please note that, in my post, I suggested you use the "MAX() (maximum) taken
on that column." Put the name of the column inside the parentheses. That's
how you get the MAXimum of that column. If the name of the column has a
space or any character other than letters or digits, put it inside square
brackets:

[column name]

Does that make it work?

Tom Ellison

Dear Tom:
Thanks for your response. I went through a hundred help topics and never
saw that mentioned. However, when I typed in Max() in the date column, I
got
a "wrong number of arguments" error message. I'm obviously a beginner.

 

[Tom Ellison]

Dear John:

Please post the SQL code of the whole query you have so far. I need to see
what's going on.

Tom Ellison

Dear Tom:
This time I got "cannot have aggregate function in where clause
(tblPortfolioReview.PReviewDate=Max("PReviewDate")).

Dear John:

Please note that, in my post, I suggested you use the "MAX() (maximum)
taken
on that column." Put the name of the column inside the parentheses.
That's
how you get the MAXimum of that column. If the name of the column has a
space or any character other than letters or digits, put it inside square
brackets:

[column name]

Does that make it work?

Tom Ellison

Dear Tom:
Thanks for your response. I went through a hundred help topics and
never
saw that mentioned. However, when I typed in Max() in the date column,
I
got
a "wrong number of arguments" error message. I'm obviously a beginner.

:

Dear John:

Do you have a date or date/time value in the table? The most recent
date
would be obtained by the aggregate function MAX() (maximum) taken on
that
column.

If your table does not keep track of the date/time of each row, then
the
information that would provide this capability is missing.

If you provide complete details of what you want, I could be more
specific.

Tom Ellison


I'm trying to find the last time I reviewed a portfolio for each of
my
clients. This would be similar to finding the most recent order
from
each
customer. I'm trying to create a criteria for a query where I have
grouped
by client. There doesn't appear to be a function for most recent
date.
Thanks, John

 

[Guest]

Dear Tom: I hope this is what you asked for, as I said, I'm a beginner.
Thanks, John.
PS to get this, I went to the query on the view menu chose SQL View, copied
and pasted below.

SELECT tblPortfolioReview.[CPAcc't#], tblPortfolioReview.PReviewDate,
tblPortfolioReview.ReviewNotes, tblPortfolioReview.PMMtg,
tblPortfolioReview.[Short Name]
FROM tblPortfolioReview
ORDER BY tblPortfolioReview.[CPAcc't#], tblPortfolioReview.PReviewDate DESC;

Dear John:

Please post the SQL code of the whole query you have so far. I need to see
what's going on.

Tom Ellison

Dear Tom:
This time I got "cannot have aggregate function in where clause
(tblPortfolioReview.PReviewDate=Max("PReviewDate")).

Dear John:

Please note that, in my post, I suggested you use the "MAX() (maximum)
taken
on that column." Put the name of the column inside the parentheses.
That's
how you get the MAXimum of that column. If the name of the column has a
space or any character other than letters or digits, put it inside square
brackets:

[column name]

Does that make it work?

Tom Ellison


Dear Tom:
Thanks for your response. I went through a hundred help topics and
never
saw that mentioned. However, when I typed in Max() in the date column,
I
got
a "wrong number of arguments" error message. I'm obviously a beginner.

:

Dear John:

Do you have a date or date/time value in the table? The most recent
date
would be obtained by the aggregate function MAX() (maximum) taken on
that
column.

If your table does not keep track of the date/time of each row, then
the
information that would provide this capability is missing.

If you provide complete details of what you want, I could be more
specific.

Tom Ellison


I'm trying to find the last time I reviewed a portfolio for each of
my
clients. This would be similar to finding the most recent order
from
each
customer. I'm trying to create a criteria for a query where I have
grouped
by client. There doesn't appear to be a function for most recent
date.
Thanks, John

 

[Tom Ellison]

Dear John:

That's the way to get what I asked for. I'm glad you figured it out. I
didn't know you didn't know.

SELECT [CPAcc't#], PReviewDate,
ReviewNotes, MMtg, [Short Name]
FROM tblPortfolioReview T
WHERE PReviewDate =
(SELECT MAX(PreviewDate)
FROM tblPortfolioReview T1
WHERE T1.[CPAcc't#] = T.[CPAcc't#])
ORDER BY [CPAcc't#]

I have dropped tblPortfolioReview.PReviewDate DESC from the ORDER BY. Since
there will only be one PReviewDate shown, there's no reason to sort by it as
well. I expect this will meet your approval.

The above is a "correlated subquery". It uses "aliasing" for the two
independent instances of the table, T and T1. I mention this so you can
study the techniques online or through the index of any books you may have
on queries.

Tom Ellison

Dear Tom: I hope this is what you asked for, as I said, I'm a beginner.
Thanks, John.
PS to get this, I went to the query on the view menu chose SQL View,
copied
and pasted below.

SELECT tblPortfolioReview.[CPAcc't#], tblPortfolioReview.PReviewDate,
tblPortfolioReview.ReviewNotes, tblPortfolioReview.PMMtg,
tblPortfolioReview.[Short Name]
FROM tblPortfolioReview
ORDER BY tblPortfolioReview.[CPAcc't#], tblPortfolioReview.PReviewDate
DESC;

Dear John:

Please post the SQL code of the whole query you have so far. I need to
see
what's going on.

Tom Ellison

Dear Tom:
This time I got "cannot have aggregate function in where clause
(tblPortfolioReview.PReviewDate=Max("PReviewDate")).

:

Dear John:

Please note that, in my post, I suggested you use the "MAX() (maximum)
taken
on that column." Put the name of the column inside the parentheses.
That's
how you get the MAXimum of that column. If the name of the column has
a
space or any character other than letters or digits, put it inside
square
brackets:

[column name]

Does that make it work?

Tom Ellison


Dear Tom:
Thanks for your response. I went through a hundred help topics and
never
saw that mentioned. However, when I typed in Max() in the date
column,
I
got
a "wrong number of arguments" error message. I'm obviously a
beginner.

:

Dear John:

Do you have a date or date/time value in the table? The most
recent
date
would be obtained by the aggregate function MAX() (maximum) taken
on
that
column.

If your table does not keep track of the date/time of each row,
then
the
information that would provide this capability is missing.

If you provide complete details of what you want, I could be more
specific.

Tom Ellison


I'm trying to find the last time I reviewed a portfolio for each
of
my
clients. This would be similar to finding the most recent order
from
each
customer. I'm trying to create a criteria for a query where I
have
grouped
by client. There doesn't appear to be a function for most recent
date.
Thanks, John

 

[Guest]

Dear Tom:
Thanks so much for all your effort on this project. The new query works
fine - I created a report from it and achieved the desired result. It will
also be a big help in a couple of other ways, so I really appreciate the
willingness of people like you to participate in these discussion groups.

John Melville

Dear John:

That's the way to get what I asked for. I'm glad you figured it out. I
didn't know you didn't know.

SELECT [CPAcc't#], PReviewDate,
ReviewNotes, MMtg, [Short Name]
FROM tblPortfolioReview T
WHERE PReviewDate =
(SELECT MAX(PreviewDate)
FROM tblPortfolioReview T1
WHERE T1.[CPAcc't#] = T.[CPAcc't#])
ORDER BY [CPAcc't#]

I have dropped tblPortfolioReview.PReviewDate DESC from the ORDER BY. Since
there will only be one PReviewDate shown, there's no reason to sort by it as
well. I expect this will meet your approval.

The above is a "correlated subquery". It uses "aliasing" for the two
independent instances of the table, T and T1. I mention this so you can
study the techniques online or through the index of any books you may have
on queries.

Tom Ellison

Dear Tom: I hope this is what you asked for, as I said, I'm a beginner.
Thanks, John.
PS to get this, I went to the query on the view menu chose SQL View,
copied
and pasted below.

SELECT tblPortfolioReview.[CPAcc't#], tblPortfolioReview.PReviewDate,
tblPortfolioReview.ReviewNotes, tblPortfolioReview.PMMtg,
tblPortfolioReview.[Short Name]
FROM tblPortfolioReview
ORDER BY tblPortfolioReview.[CPAcc't#], tblPortfolioReview.PReviewDate
DESC;

Dear John:

Please post the SQL code of the whole query you have so far. I need to
see
what's going on.

Tom Ellison


Dear Tom:
This time I got "cannot have aggregate function in where clause
(tblPortfolioReview.PReviewDate=Max("PReviewDate")).

:

Dear John:

Please note that, in my post, I suggested you use the "MAX() (maximum)
taken
on that column." Put the name of the column inside the parentheses.
That's
how you get the MAXimum of that column. If the name of the column has
a
space or any character other than letters or digits, put it inside
square
brackets:

[column name]

Does that make it work?

Tom Ellison


Dear Tom:
Thanks for your response. I went through a hundred help topics and
never
saw that mentioned. However, when I typed in Max() in the date
column,
I
got
a "wrong number of arguments" error message. I'm obviously a
beginner.

:

Dear John:

Do you have a date or date/time value in the table? The most
recent
date
would be obtained by the aggregate function MAX() (maximum) taken
on
that
column.

If your table does not keep track of the date/time of each row,
then
the
information that would provide this capability is missing.

If you provide complete details of what you want, I could be more
specific.

Tom Ellison


I'm trying to find the last time I reviewed a portfolio for each
of
my
clients. This would be similar to finding the most recent order
from
each
customer. I'm trying to create a criteria for a query where I
have
grouped
by client. There doesn't appear to be a function for most recent
date.
Thanks, John