SQLConfiguration SQLConfiguration

Refining Queries  «Prev 

Using Totals queries

Creating a Totals query

  1. Here you are with a standard select query. You will now get the chance to make it into a Totals query summing up the hours for each projects. To start click the Totals toolbar button.
  2. Now you can see the new Total row in the lower half of the query grid. For the Hours column, click on the Totals row entry.
  3. From the list of aggregate functions displayed, click the correct entry to sum up the hours..
  4. That’s it. Now run the query and view the results.
  5. Here are results.

Getting Quick Totals for a Column

Access has a nearly hidden feature that lets you make quick, basic calculations with an entire column of numeric values.
For example, you can use this to get the average price from a table of products, or the total contributions from a table of donations.
Here is how it works:
  1. Choose Home.Records.Totals. An extra row appears at the bottom of the datasheet, with the word "Total" at the far left.
  2. Click in the totals row, under the column you want to use for your calculation. A drop-down list appears with different types of calculations
  3. Choose the type of calculation you want to perform. Access shows the calculated value under the column. If you add a record or modify a value in the column, Access update the totals immediately. You can repeat step 2 and 3 to pick totals for as many columns as you want, The totals row is a simple, straightforward tool. One nice feature is that it respects your filtering settings, so if you have filtered the table to show just five rows, only five rows are used to calculate the totals.