Max function

[Stephen Glynn]

I've got two tables, Clients and Appointments. I've written a query,
joining the two on ClientID, that gives me a list of every appointment
I've had with each client over the last 12 months. I want it to show
me only the most recent appointment I've had with each client, though.

I'd have thought that 'max ([Appointments].[AppointmentDate])' would do
it, but I a get an error message telling me that I've tried "to execute
a query that does not include the specified expression "clientid" as
part of an aggregate function".

I don't understand either what that's trying to tell me or what I should
be doing.

Steve

 

[Al Camp]

Stephen,
When I have trouble with an SQL statement, I just work it out on the
query design grid. When it works the way I want, then I View/SQL and
"steal/copy" the SQL statement to paste into my module code... or whatever.

Your query should be a "Totals" query, and I would expect it to look like
this (just an example)
ClientID ClientName ApptDate
GroupBy GroupBy Max

That should deliver just one record per customer, and the last ApptDate.
Take a look at the SQL view, and you may see where your original statement
went awry.
hth
Al Camp

 

[Ken Snell [MVP]]

Post the SQL statement of the query that is giving you the error.

 

[Stephen Glynn]

Thanks. That's fixed it.

Steve

Stephen,
When I have trouble with an SQL statement, I just work it out on the
query design grid. When it works the way I want, then I View/SQL and
"steal/copy" the SQL statement to paste into my module code... or whatever.

Your query should be a "Totals" query, and I would expect it to look like
this (just an example)
ClientID ClientName ApptDate
GroupBy GroupBy Max

That should deliver just one record per customer, and the last ApptDate.
Take a look at the SQL view, and you may see where your original statement
went awry.
hth
Al Camp

I've got two tables, Clients and Appointments. I've written a query,
joining the two on ClientID, that gives me a list of every appointment
I've had with each client over the last 12 months. I want it to show me
only the most recent appointment I've had with each client, though.

I'd have thought that 'max ([Appointments].[AppointmentDate])' would do
it, but I a get an error message telling me that I've tried "to execute a
query that does not include the specified expression "clientid" as part of
an aggregate function".

I don't understand either what that's trying to tell me or what I should
be doing.

Steve

--


"It has been said," he began at length, withdrawing his eyes
reluctantly from an usually large insect upon the ceiling and
addressing himself to the maiden, "that there are few
situations in life that cannot be honourably settled, and
without any loss of time, either by suicide, a bag of gold, or
by thrusting a despised antagonist over the edge of a
precipice on a dark night."

 

[Ken Sheridan]

While an aggregate query will work provided that in addition to the
MAX(AppointmentDate) you only want to return those columns by which you can
group the data this is not always the case. Lets assume you also have a
Subject column in Appointments which will differ from appointment to
appointment. If you group by this column as well as the ClientID, ClientName
you'd get separate MAX dates for each subject with each client. What you
need in a case like that is a correlated subquery in the WHERE clause of the
outer query, e.g.

SELECT Clients.Client, A1.Subject, A1.AppointmentDate
FROM Clients INNER JOIN Appointments As A1
ON Clients.ClientID = A1.ClientID
WHERE A1.AppointmentDate =
(SELECT MAX(A2.AppointmentDate)
FROM Appointments As A2
WHERE A2.ClientID = A1.ClientID);

By correlating the subquery with the outer query, using aliases A1 and A2 to
distinguish the two instances of Appointments only the row for each client is
returned where the AppointmentDate matches the latest appointment date for
that client as returned by the subquery.