Query to exclude the maximum record

J

Jack Sheet

Hi all

My query does not work:

SELECT [Q_Tasks_Accs].[End]<(Max([Q_Tasks_Accs].[End])) AS NotLatestEnd
FROM Q_Tasks_Accs;

I manage to save the query, but when I run it I get:

"You tried to execute a query that does not include
'[Q_Tasks_Accs].[End]<Max([Q_Tasks_Accs].[End])' as part of an aggregate
function".

What's that all about?

[Q_Tasks_Accs].[End] is a field of dates. I wanted to create an identical
query that returns all except the latest date.

Thanks
 
J

Jack Sheet

UFF! I rethought it and can see that it would not work.

So I rewrote the query as:

SELECT Q_Tasks_Accs.End AS NotLatestEnd
FROM Q_Tasks_Accs
WHERE (((Q_Tasks_Accs.End)<(Max([Q_Tasks_Accs].[End]))));

believing it to have corrected the problem, only to get another error
message:

"Cannot have an aggregate function in WHERE
clause(Q_Tasks_Accs.End)<(Max([Q_Tasks_Accs].[End]))"

What to try next?
 
D

David F Cox

Not tested, may not be best way:-

SELECT [Q_Tasks_Accs].[End] AS NotLatestEnd
FROM Q_Tasks_Acc
WHERE [Q_Tasks_Accs].[End]
NOT IN
( SELECT (Max([Q_Tasks_Accs].[End]) ) FROM Q_Tasks_Acc )
 
J

Jack Sheet

Thanks.. That seems to work

David F Cox said:
Not tested, may not be best way:-

SELECT [Q_Tasks_Accs].[End] AS NotLatestEnd
FROM Q_Tasks_Acc
WHERE [Q_Tasks_Accs].[End]
NOT IN
( SELECT (Max([Q_Tasks_Accs].[End]) ) FROM Q_Tasks_Acc )


Jack Sheet said:
Hi all

My query does not work:

SELECT [Q_Tasks_Accs].[End]<(Max([Q_Tasks_Accs].[End])) AS NotLatestEnd
FROM Q_Tasks_Accs;

I manage to save the query, but when I run it I get:

"You tried to execute a query that does not include
'[Q_Tasks_Accs].[End]<Max([Q_Tasks_Accs].[End])' as part of an aggregate
function".

What's that all about?

[Q_Tasks_Accs].[End] is a field of dates. I wanted to create an
identical query that returns all except the latest date.

Thanks
Click to expand...
Click to expand...
 
K

Ken Sheridan

As well as the subquery David gave you, you were actually very close to a
solution with your second attempt. You just needed to get the MAX value in
a subquery:

SELECT End AS NotLatestEnd
FROM Q_Tasks_Accs
WHERE End <
(SELECT MAX(End)
FROM Q_Tasks_Accs);

Ken Sheridan
Stafford, England

Jack Sheet said:
UFF! I rethought it and can see that it would not work.

So I rewrote the query as:

SELECT Q_Tasks_Accs.End AS NotLatestEnd
FROM Q_Tasks_Accs
WHERE (((Q_Tasks_Accs.End)<(Max([Q_Tasks_Accs].[End]))));

believing it to have corrected the problem, only to get another error
message:

"Cannot have an aggregate function in WHERE
clause(Q_Tasks_Accs.End)<(Max([Q_Tasks_Accs].[End]))"

What to try next?

Jack Sheet said:
Hi all

My query does not work:

SELECT [Q_Tasks_Accs].[End]<(Max([Q_Tasks_Accs].[End])) AS NotLatestEnd
FROM Q_Tasks_Accs;

I manage to save the query, but when I run it I get:

"You tried to execute a query that does not include
'[Q_Tasks_Accs].[End]<Max([Q_Tasks_Accs].[End])' as part of an aggregate
function".

What's that all about?

[Q_Tasks_Accs].[End] is a field of dates. I wanted to create an identical
query that returns all except the latest date.

Thanks
Click to expand...
Click to expand...
 
J

Jack Sheet

Thanks, Ken
An ancillary problem, to produce a query that lists the max items for each
item in another field.

I have a Select query that lists, among other things, two fields:
ID_Clients
EndDate

Each entry in ID_Clients may be listed several times

I want to generate a Select query based on the above query that lists each
value of ID_Clients just once, by selecting the record for that ID_Clients
that has the maximum value in the field EndDate.

So, sample source data

ID_Clients......EndDate
AAA.............2005-04-30
BBB............. 2005-06-30
AAA.............2006-04-30
BBB............. 2004-06-30
CCC.............2006-09-30

Desired end result:
ID_Clients......EndDate
AAA.............2005-06-30
BBB.............2006-04-30
CCC.............2006-09-30

Any ideas how I should achieve this would be appreciated.
Thanks


Ken Sheridan said:
As well as the subquery David gave you, you were actually very close to a
solution with your second attempt. You just needed to get the MAX value
in
a subquery:

SELECT End AS NotLatestEnd
FROM Q_Tasks_Accs
WHERE End <
(SELECT MAX(End)
FROM Q_Tasks_Accs);

Ken Sheridan
Stafford, England

Jack Sheet said:
UFF! I rethought it and can see that it would not work.

So I rewrote the query as:

SELECT Q_Tasks_Accs.End AS NotLatestEnd
FROM Q_Tasks_Accs
WHERE (((Q_Tasks_Accs.End)<(Max([Q_Tasks_Accs].[End]))));

believing it to have corrected the problem, only to get another error
message:

"Cannot have an aggregate function in WHERE
clause(Q_Tasks_Accs.End)<(Max([Q_Tasks_Accs].[End]))"

What to try next?

Jack Sheet said:
Hi all

My query does not work:

SELECT [Q_Tasks_Accs].[End]<(Max([Q_Tasks_Accs].[End])) AS NotLatestEnd
FROM Q_Tasks_Accs;

I manage to save the query, but when I run it I get:

"You tried to execute a query that does not include
'[Q_Tasks_Accs].[End]<Max([Q_Tasks_Accs].[End])' as part of an
aggregate
function".

What's that all about?

[Q_Tasks_Accs].[End] is a field of dates. I wanted to create an
identical
query that returns all except the latest date.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

You could use a coordinated subquery.

SELECT Id_Clients, End AS LatestEnd
FROM Q_Tasks_Accs
WHERE End =
(SELECT MAX(End)
FROM Q_Tasks_Accs as Temp
WHERE Temp.Id_Clients = Q_Tasks_Accs.Id_Clients);

OR more efficient use a subquery in the FROM clause

SELECT Q.Id_Clients, Q.End AS LatestEnd
FROM Q_Tasks_Accs as Q INNER JOIN
(SELECT Id_Clients, MAX(End) as MaxDate
FROM Q_Tasks_Accs as Temp
GROUP BY ID_Clients) as T
Thanks, Ken
An ancillary problem, to produce a query that lists the max items for each
item in another field.

I have a Select query that lists, among other things, two fields:
ID_Clients
EndDate

Each entry in ID_Clients may be listed several times

I want to generate a Select query based on the above query that lists each
value of ID_Clients just once, by selecting the record for that ID_Clients
that has the maximum value in the field EndDate.

So, sample source data

ID_Clients......EndDate
AAA.............2005-04-30
BBB............. 2005-06-30
AAA.............2006-04-30
BBB............. 2004-06-30
CCC.............2006-09-30

Desired end result:
ID_Clients......EndDate
AAA.............2005-06-30
BBB.............2006-04-30
CCC.............2006-09-30

Any ideas how I should achieve this would be appreciated.
Thanks

Ken Sheridan said:
As well as the subquery David gave you, you were actually very close to a
solution with your second attempt. You just needed to get the MAX value
in
a subquery:

SELECT End AS NotLatestEnd
FROM Q_Tasks_Accs
WHERE End <
(SELECT MAX(End)
FROM Q_Tasks_Accs);

Ken Sheridan
Stafford, England

Jack Sheet said:
UFF! I rethought it and can see that it would not work.

So I rewrote the query as:

SELECT Q_Tasks_Accs.End AS NotLatestEnd
FROM Q_Tasks_Accs
WHERE (((Q_Tasks_Accs.End)<(Max([Q_Tasks_Accs].[End]))));

believing it to have corrected the problem, only to get another error
message:

"Cannot have an aggregate function in WHERE
clause(Q_Tasks_Accs.End)<(Max([Q_Tasks_Accs].[End]))"

What to try next?

Hi all

My query does not work:

SELECT [Q_Tasks_Accs].[End]<(Max([Q_Tasks_Accs].[End])) AS NotLatestEnd
FROM Q_Tasks_Accs;

I manage to save the query, but when I run it I get:

"You tried to execute a query that does not include
'[Q_Tasks_Accs].[End]<Max([Q_Tasks_Accs].[End])' as part of an
aggregate
function".

What's that all about?

[Q_Tasks_Accs].[End] is a field of dates. I wanted to create an
identical
query that returns all except the latest date.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
J

Jack Sheet

Thanks very much again John.

John Spencer said:
You could use a coordinated subquery.

SELECT Id_Clients, End AS LatestEnd
FROM Q_Tasks_Accs
WHERE End =
(SELECT MAX(End)
FROM Q_Tasks_Accs as Temp
WHERE Temp.Id_Clients = Q_Tasks_Accs.Id_Clients);

OR more efficient use a subquery in the FROM clause

SELECT Q.Id_Clients, Q.End AS LatestEnd
FROM Q_Tasks_Accs as Q INNER JOIN
(SELECT Id_Clients, MAX(End) as MaxDate
FROM Q_Tasks_Accs as Temp
GROUP BY ID_Clients) as T
Thanks, Ken
An ancillary problem, to produce a query that lists the max items for
each
item in another field.

I have a Select query that lists, among other things, two fields:
ID_Clients
EndDate

Each entry in ID_Clients may be listed several times

I want to generate a Select query based on the above query that lists
each
value of ID_Clients just once, by selecting the record for that
ID_Clients
that has the maximum value in the field EndDate.

So, sample source data

ID_Clients......EndDate
AAA.............2005-04-30
BBB............. 2005-06-30
AAA.............2006-04-30
BBB............. 2004-06-30
CCC.............2006-09-30

Desired end result:
ID_Clients......EndDate
AAA.............2005-06-30
BBB.............2006-04-30
CCC.............2006-09-30

Any ideas how I should achieve this would be appreciated.
Thanks

Ken Sheridan said:
As well as the subquery David gave you, you were actually very close to
a
solution with your second attempt. You just needed to get the MAX
value
in
a subquery:

SELECT End AS NotLatestEnd
FROM Q_Tasks_Accs
WHERE End <
(SELECT MAX(End)
FROM Q_Tasks_Accs);

Ken Sheridan
Stafford, England

:

UFF! I rethought it and can see that it would not work.

So I rewrote the query as:

SELECT Q_Tasks_Accs.End AS NotLatestEnd
FROM Q_Tasks_Accs
WHERE (((Q_Tasks_Accs.End)<(Max([Q_Tasks_Accs].[End]))));

believing it to have corrected the problem, only to get another error
message:

"Cannot have an aggregate function in WHERE
clause(Q_Tasks_Accs.End)<(Max([Q_Tasks_Accs].[End]))"

What to try next?

Hi all

My query does not work:

SELECT [Q_Tasks_Accs].[End]<(Max([Q_Tasks_Accs].[End])) AS
NotLatestEnd
FROM Q_Tasks_Accs;

I manage to save the query, but when I run it I get:

"You tried to execute a query that does not include
'[Q_Tasks_Accs].[End]<Max([Q_Tasks_Accs].[End])' as part of an
aggregate
function".

What's that all about?

[Q_Tasks_Accs].[End] is a field of dates. I wanted to create an
identical
query that returns all except the latest date.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

If you only need the other column and the End column in the result set the
most efficient way is a simple aggregating query, grouped by the ID_Clients
column:

SELECT ID_Clients, MAX(End) As BigEnd
FROM Q_Tasks_Accs
GROUP BY ID_Clients;

If you need other columns in the result set then you do need to use a
correlated subquery:

SELECT ID_Clients, ClientName, End
FROM Q_Tasks_Accs AS QT1
WHERE End =
(SELECT MAX(End)
FROM Q_Tasks_Accs AS QT2
WHERE QT2.ID_Clients = QT1.ID_Clients);

You could also do the latter by joining the first query above to
Q_Tasks_Accs on the ID_Clients and End/BigEnd columns. In Access this can
sometimes give significantly improved performance over a single query with a
correlated subquery. Indexing plays a big part in the performance, though.

Ken Sheridan
Stafford, England
 
J

Jack Sheet

Thanks Ken
Getting there.

Ken Sheridan said:
If you only need the other column and the End column in the result set the
most efficient way is a simple aggregating query, grouped by the
ID_Clients
column:

SELECT ID_Clients, MAX(End) As BigEnd
FROM Q_Tasks_Accs
GROUP BY ID_Clients;

If you need other columns in the result set then you do need to use a
correlated subquery:

SELECT ID_Clients, ClientName, End
FROM Q_Tasks_Accs AS QT1
WHERE End =
(SELECT MAX(End)
FROM Q_Tasks_Accs AS QT2
WHERE QT2.ID_Clients = QT1.ID_Clients);

You could also do the latter by joining the first query above to
Q_Tasks_Accs on the ID_Clients and End/BigEnd columns. In Access this can
sometimes give significantly improved performance over a single query with
a
correlated subquery. Indexing plays a big part in the performance,
though.

Ken Sheridan
Stafford, England
Click to expand...
 
Top