Creating
a Summary Query
In reports,
you have the ability to group records based upon the data in a certain
field and generate summaries on numeric fields. This can also be
done in a query by creating a Summary Query. A good example would
be calculating the amount customers have spent in your business.
However, since
we do not yet have a field that contains the total for each transaction,
we will use the Expression Builder to set this up. In this example,
the query will consist of the Last Name field from the Customers
table and a new field, Order Total, which will comprise of the product
of the price and the quantity. (Click the image to see a video demonstration
of the formula being built.)
Once the formula
is set up, we click on the Totals button on our toolbar. 
The query will
group by the Last Name. For the other field, Order Total, we will
choose Sum from the drop-down list. This will calculate the sum
of all order totals for each name on the list. Here is the result.
|
