Aggregate function errors

[Ann Scharpf]

I seem to have a lot of problems with writing queries that generate the error:

You tried to execute a query that does not include the specified expression
'[xxx]' as part of an aggregate function.

Neither of the Access classes I took explained what triggers this type of
error and the posts I've seen have not explained it in a general way. Can
anyone explain what TYPE of query error causes this problem?

Right now the query that is getting the error is this:

SELECT Max([PPEdate]) AS LastPPEDate, [CompletedPPEs]/26 AS PctYrComplete
FROM eBizHrsRecordedWithMIPRandTaskPrefix;

eBizHrsRecordedWithMIPRandTaskPrefix is a query that has a record per
employee time record. There is a field for the number of pay periods that
have been completed. I want to divide this value by 26 (total PPEs per
year).

Is it possible to do this in a single query? Thanks for any help you can
give me on a general explanation of the error and a specific response on this
query.



Ann Scharpf

 

[George Nicholson]

Try something like
Avg([CompletedPPEs])/26 AS PctYrComplete
Max([CompletedPPEs])/26 AS PctYrComplete
or
Count([PPEdate])/26 AS PctYrComplete
for your second field.

As to why, the following is "afaik":

In a gross generalization, fields in a Totals query can have one of 4
possible assignments:
Where: this will not be output & is just used for criteria
GroupBy: pretty straightforward
Aggregate functions (Sum, Count, Max, etc...)
Expression: If this is a calculated field based on data in a FROM
source, the calculation needs to incorporate an aggregate function.

Your error was caused by your 2nd (calculated) field, an expression, that
had no aggregate function. Adding an aggregate expression (as above)
removes the error condition.

HTH,

 

[Baz]

When you use an aggregate function in a query (such as Max) you are telling
Access to take the records which meet the query's criteria and to
"aggregate" them into some kind of summary e.g. Max, Min, Avg (average) or
whatever. Obviously this will result in the query returning less results
than there are records meeting the criteria. For example:

SELECT Max(order_date) AS latest_order FROM orders

This returns just one result, because there is only one maximum date,
regardless of how many orders are in the table.

If we now do this:

SELECT customer_id, Max(order_date) AS latest_order FROM orders

This will give the error that is troubling you. Because of the Max
function, Access wants to aggregate the records, but it doesn't know what to
do about the customer_id: there are lots of customer_id's in the orders
table, but you haven't told Access how to aggregate them. Maybe you want
Max(customer_id), or Min(customer_id), Access simply doesn't know. In
reality, you are more likely to want to do this:

SELECT customer_id, Max(order_date) AS latest_order FROM orders GROUP BY
customer_id

This tells Access to find Max(order_date) for each distinct customer_id in
the table, so this query will return as many results as there are distinct
customer_id's. So, if the orders table contains the following:

customer_id order_date
1 01/07/2007
2 03/07/2007
1 15/06/2007

The above query will give this:

customer_id latest_order
1 01/07/2007
2 03/07/2007

So, we didn't need to aggregate the customer_id, because we used it for
grouping instead.

The bottom line is, if you use an aggregate function in a query's SELECT
list, then *every* field in the SELECT list must *either* be aggregated,
*or* it must be used for grouping. If you simply leave it there without
doing either of these things, Access simply does not know what you want to
do with it and the error results.

I seem to have a lot of problems with writing queries that generate the error:

You tried to execute a query that does not include the specified expression
'[xxx]' as part of an aggregate function.

Neither of the Access classes I took explained what triggers this type of
error and the posts I've seen have not explained it in a general way. Can
anyone explain what TYPE of query error causes this problem?

Right now the query that is getting the error is this:

SELECT Max([PPEdate]) AS LastPPEDate, [CompletedPPEs]/26 AS PctYrComplete
FROM eBizHrsRecordedWithMIPRandTaskPrefix;

eBizHrsRecordedWithMIPRandTaskPrefix is a query that has a record per
employee time record. There is a field for the number of pay periods that
have been completed. I want to divide this value by 26 (total PPEs per
year).

Is it possible to do this in a single query? Thanks for any help you can
give me on a general explanation of the error and a specific response on this
query.



Ann Scharpf

 

[Ann Scharpf]

Thanks very much for taking the time to answer my question!
--
Ann Scharpf

Try something like
Avg([CompletedPPEs])/26 AS PctYrComplete
Max([CompletedPPEs])/26 AS PctYrComplete
or
Count([PPEdate])/26 AS PctYrComplete
for your second field.

As to why, the following is "afaik":

In a gross generalization, fields in a Totals query can have one of 4
possible assignments:
Where: this will not be output & is just used for criteria
GroupBy: pretty straightforward
Aggregate functions (Sum, Count, Max, etc...)
Expression: If this is a calculated field based on data in a FROM
source, the calculation needs to incorporate an aggregate function.

Your error was caused by your 2nd (calculated) field, an expression, that
had no aggregate function. Adding an aggregate expression (as above)
removes the error condition.

HTH,

I seem to have a lot of problems with writing queries that generate the
error:

You tried to execute a query that does not include the specified
expression
'[xxx]' as part of an aggregate function.

Neither of the Access classes I took explained what triggers this type of
error and the posts I've seen have not explained it in a general way. Can
anyone explain what TYPE of query error causes this problem?

Right now the query that is getting the error is this:

SELECT Max([PPEdate]) AS LastPPEDate, [CompletedPPEs]/26 AS PctYrComplete
FROM eBizHrsRecordedWithMIPRandTaskPrefix;

eBizHrsRecordedWithMIPRandTaskPrefix is a query that has a record per
employee time record. There is a field for the number of pay periods that
have been completed. I want to divide this value by 26 (total PPEs per
year).

Is it possible to do this in a single query? Thanks for any help you can
give me on a general explanation of the error and a specific response on
this
query.



Ann Scharpf

 

[Ann Scharpf]

Wow. Thanks for taking the time to write such a detailed and thorough
response! I am printing this out to keep with my Access reference
information. This helps a LOT.
--
Ann Scharpf

When you use an aggregate function in a query (such as Max) you are telling
Access to take the records which meet the query's criteria and to
"aggregate" them into some kind of summary e.g. Max, Min, Avg (average) or
whatever. Obviously this will result in the query returning less results
than there are records meeting the criteria. For example:

SELECT Max(order_date) AS latest_order FROM orders

This returns just one result, because there is only one maximum date,
regardless of how many orders are in the table.

If we now do this:

SELECT customer_id, Max(order_date) AS latest_order FROM orders

This will give the error that is troubling you. Because of the Max
function, Access wants to aggregate the records, but it doesn't know what to
do about the customer_id: there are lots of customer_id's in the orders
table, but you haven't told Access how to aggregate them. Maybe you want
Max(customer_id), or Min(customer_id), Access simply doesn't know. In
reality, you are more likely to want to do this:

SELECT customer_id, Max(order_date) AS latest_order FROM orders GROUP BY
customer_id

This tells Access to find Max(order_date) for each distinct customer_id in
the table, so this query will return as many results as there are distinct
customer_id's. So, if the orders table contains the following:

customer_id order_date
1 01/07/2007
2 03/07/2007
1 15/06/2007

The above query will give this:

customer_id latest_order
1 01/07/2007
2 03/07/2007

So, we didn't need to aggregate the customer_id, because we used it for
grouping instead.

The bottom line is, if you use an aggregate function in a query's SELECT
list, then *every* field in the SELECT list must *either* be aggregated,
*or* it must be used for grouping. If you simply leave it there without
doing either of these things, Access simply does not know what you want to
do with it and the error results.

I seem to have a lot of problems with writing queries that generate the error:

You tried to execute a query that does not include the specified expression
'[xxx]' as part of an aggregate function.

Neither of the Access classes I took explained what triggers this type of
error and the posts I've seen have not explained it in a general way. Can
anyone explain what TYPE of query error causes this problem?

Right now the query that is getting the error is this:

SELECT Max([PPEdate]) AS LastPPEDate, [CompletedPPEs]/26 AS PctYrComplete
FROM eBizHrsRecordedWithMIPRandTaskPrefix;

eBizHrsRecordedWithMIPRandTaskPrefix is a query that has a record per
employee time record. There is a field for the number of pay periods that
have been completed. I want to divide this value by 26 (total PPEs per
year).

Is it possible to do this in a single query? Thanks for any help you can
give me on a general explanation of the error and a specific response on this
query.



Ann Scharpf

 

[George Nicholson]

if you use an aggregate function in a query's SELECT

list, then *every* field in the SELECT list must *either* be aggregated,
*or* it must be used for grouping.

fwiw, i liked your response a lot better than mine

When you use an aggregate function in a query (such as Max) you are
telling
Access to take the records which meet the query's criteria and to
"aggregate" them into some kind of summary e.g. Max, Min, Avg (average) or
whatever. Obviously this will result in the query returning less results
than there are records meeting the criteria. For example:

SELECT Max(order_date) AS latest_order FROM orders

This returns just one result, because there is only one maximum date,
regardless of how many orders are in the table.

If we now do this:

SELECT customer_id, Max(order_date) AS latest_order FROM orders

This will give the error that is troubling you. Because of the Max
function, Access wants to aggregate the records, but it doesn't know what
to
do about the customer_id: there are lots of customer_id's in the orders
table, but you haven't told Access how to aggregate them. Maybe you want
Max(customer_id), or Min(customer_id), Access simply doesn't know. In
reality, you are more likely to want to do this:

SELECT customer_id, Max(order_date) AS latest_order FROM orders GROUP BY
customer_id

This tells Access to find Max(order_date) for each distinct customer_id in
the table, so this query will return as many results as there are distinct
customer_id's. So, if the orders table contains the following:

customer_id order_date
1 01/07/2007
2 03/07/2007
1 15/06/2007

The above query will give this:

customer_id latest_order
1 01/07/2007
2 03/07/2007

So, we didn't need to aggregate the customer_id, because we used it for
grouping instead.

The bottom line is, if you use an aggregate function in a query's SELECT
list, then *every* field in the SELECT list must *either* be aggregated,
*or* it must be used for grouping. If you simply leave it there without
doing either of these things, Access simply does not know what you want to
do with it and the error results.

I seem to have a lot of problems with writing queries that generate the error:

You tried to execute a query that does not include the specified expression
'[xxx]' as part of an aggregate function.

Neither of the Access classes I took explained what triggers this type of
error and the posts I've seen have not explained it in a general way.
Can
anyone explain what TYPE of query error causes this problem?

Right now the query that is getting the error is this:

SELECT Max([PPEdate]) AS LastPPEDate, [CompletedPPEs]/26 AS PctYrComplete
FROM eBizHrsRecordedWithMIPRandTaskPrefix;

eBizHrsRecordedWithMIPRandTaskPrefix is a query that has a record per
employee time record. There is a field for the number of pay periods
that
have been completed. I want to divide this value by 26 (total PPEs per
year).

Is it possible to do this in a single query? Thanks for any help you can
give me on a general explanation of the error and a specific response on this
query.



Ann Scharpf

 

[Baz]

fwiw, i liked your response a lot better than mine