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

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" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:e6CQYX3iGHA.1936@xxxxxxxxxxxxxxxxxxxxxxx
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" <Joshcat99@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3B3CD180-6F33-4963-9855-8E4B6BA639A7@xxxxxxxxxxxxxxxx
In the drop-down list in the Totals row in a Query grid, what does
"expression" refer to? How do you use it?


.

Relevant Pages

  • Re: Box query
    ... Changing the having clause won't fix a problem that already happened ... I'm changing the aggregation expression. ... intersect is a simple expression ...
    (comp.databases.theory)
  • Re: DateDiff
    ... The purpose of the GROUP BY clause is to perform some sort of aggregation on some field. ... When I take out the group by Employee id, the query runs fine. ...
    (microsoft.public.access.queries)
  • Re: Querying mean values without using "Totals"
    ... You cannot use an aggregate in a WHERE clause. ... The WHERE clause is executed BEFORE any aggregation, so, the result ... Using the expression builder with the function "Avg" in the cell ... possible to have a aggregated function in a WHERE instance". ...
    (microsoft.public.access.queries)
  • Re: What does "expression" refer to in the Totals list in a Query?
    ... Expressions are fields in the SELECT clause ... and have no aggregation (such as First or Sum or Count.) ...
    (microsoft.public.access.queries)
  • Re: Where Between Date Range
    ... AS 2005 makes possible using sets in the WHERE clause, ... 2005 engine makes aggregation on the fly. ... Vladimir Chtepa. ... > Deepak Puri ...
    (microsoft.public.sqlserver.olap)