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


G

Guest

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

Advertisements

A

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.
 
A

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.)


Allen Browne said:
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.

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

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....
 
M

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]


Allen said:
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.)


Allen Browne said:
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.

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

Advertisements

Joined
May 15, 2020
Messages
2
Reaction score
0
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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Refer to Total in query 1
referring to a totals query on a form 2
"Totals" button in query - what is it used for? 4
Totals Query 1
totals query 1
Totals Query 10
Totals Query 2
Totals Query 3

Top