What does "expression" refer to in the Totals list in a Query?

[Guest]

In the drop-down list in the Totals row in a Query grid, what does
"expression" refer to? How do you use it?

 

[Allen Browne]

A totals query aggregates data.
For each field, you specify how to aggregate that column's data, e.g.:
- Group By means to return each value;
- Sum means add up all values for the grouped by fields.

Expression means: don't aggregate on this field, just include a column with
this value. For example, if you type into the Field row:
Expr1: 99
and choose Expression, you get a 99 on every row of the query.

Since the expression is tacked on after aggregation, it can refer to other
fields that are in the query results. Any Group By field is in the results,
so if you group by Surname and FirstName, you can use an expression such as:
Expr1: [FirstName] & " " & [Surname]

If you have a field called Amount that you Sum, Access will alias the column
as SumOfAmount. You can therefore refer to that in your Expression. For
example, to calculate 8% sales tax on the amount:
Expr1: [SumOfAmount] * 0.08

If you look at the SQL statement, these expressions are fields in the SELECT
clause that are not in the GROUP BY clause.

 

[Allen Browne]

The last sentence skipped an important point:

Expressions are fields in the SELECT clause
that are not in the GROUP BY clause,
and have no aggregation (such as First or Sum or Count.)

A totals query aggregates data.
For each field, you specify how to aggregate that column's data, e.g.:
- Group By means to return each value;
- Sum means add up all values for the grouped by fields.

Expression means: don't aggregate on this field, just include a column
with this value. For example, if you type into the Field row:
Expr1: 99
and choose Expression, you get a 99 on every row of the query.

Since the expression is tacked on after aggregation, it can refer to other
fields that are in the query results. Any Group By field is in the
results, so if you group by Surname and FirstName, you can use an
expression such as:
Expr1: [FirstName] & " " & [Surname]

If you have a field called Amount that you Sum, Access will alias the
column as SumOfAmount. You can therefore refer to that in your Expression.
For example, to calculate 8% sales tax on the amount:
Expr1: [SumOfAmount] * 0.08

If you look at the SQL statement, these expressions are fields in the
SELECT clause that are not in the GROUP BY clause.

In the drop-down list in the Totals row in a Query grid, what does
"expression" refer to? How do you use it?

 

[Gary Walter]

The last sentence skipped an important point:

Expressions are fields in the SELECT clause
that are not in the GROUP BY clause,
and have no aggregation (such as First or Sum or Count.)

In addition to Allen's sage advice...

you can add a *subquery* as an expression
that contains aggregation within it.

small point, but good to know I think....

 

[Marshall Barton]

Nice explamation Allen.

I think one of the more powerful uses of this is to
incorporate your own aggregate functions (e.g. DConcat,
Median,etc) into a query.
--
Marsh
MVP [MS Access]

The last sentence skipped an important point:

Expressions are fields in the SELECT clause
that are not in the GROUP BY clause,
and have no aggregation (such as First or Sum or Count.)

A totals query aggregates data.
For each field, you specify how to aggregate that column's data, e.g.:
- Group By means to return each value;
- Sum means add up all values for the grouped by fields.

Expression means: don't aggregate on this field, just include a column
with this value. For example, if you type into the Field row:
Expr1: 99
and choose Expression, you get a 99 on every row of the query.

Since the expression is tacked on after aggregation, it can refer to other
fields that are in the query results. Any Group By field is in the
results, so if you group by Surname and FirstName, you can use an
expression such as:
Expr1: [FirstName] & " " & [Surname]

If you have a field called Amount that you Sum, Access will alias the
column as SumOfAmount. You can therefore refer to that in your Expression.
For example, to calculate 8% sales tax on the amount:
Expr1: [SumOfAmount] * 0.08

If you look at the SQL statement, these expressions are fields in the
SELECT clause that are not in the GROUP BY clause.

In the drop-down list in the Totals row in a Query grid, what does
"expression" refer to? How do you use it?

 

[neilsolaris]

I've stuck on a problem that I think relates to this thread. I'd be very grateful if someone could help me please.

I'm using Access 2007, and I've created a database for an orchestra. I've created a query with a group by player ID and engagement ID, and entered the total fee as a sum. That all works well. However, I want another column to calculate the VAT on that total fee, if the player has a VAT number. I've tried entering the following - Expr1: IIf([T_MusiciansDetails]![VAT_Number]<>"",[NetTotal]*0.2,"") - both as "grouped by" and "expression". Neither worked. Grouped by splits all the payment types for the VAT players. If I enter it as an expression I get a pop up error 'You tried to execute a query that does not include the specified expression 'IIF(Not [T_MusiciansDetails]![VAT_Number] ="",[T_Payments]![Amount]*0.2 ,"")'. What am I doing wrong? Many thanks for your help.