Using Access Totals Queries

Your standard Select query is great for displaying every record in a result set. There are times, when you just want to display aggregate data, such as 1) summations or 2) counts. A typical example might be when you need to total all the hours that have been spent on a particular project. As with other types of queries, you will start with a Select query. The fields you select should include those you want to use for calculating and displaying aggregate values. After you pick the fields, you will turn the query into a "Totals query" by clicking on the Totals toolbar button

You will now be presented with a new row called the Total row. In the Total row, you will specify each of the fields as a "Group By", which tells Access how to group the records. The other items you can specify are aggregate functions. Aggregate function are how you perform a particular task, such as summing the values. You can perform the following aggregate functions:

• Microsoft Access Aggregate Functions
  

  Function	Description
  Sum	Summation of the field.
  Avg	Average value.
  Min	Minimum value.
  Max	Maximum value.
  Count	Count the records for the group.
  StDev	Standard deviation.
  Var	Variance.
  First	First value of the field in the group.
  Last	Last value of the field in the group.
  Expression	This can be a calculated value to display other information.
  Where	If you are using the field strictly for criteria purposes and don’t want it to show up, use this function.

In Microsoft Access 365, a Totals Query allows you to perform aggregate calculations (e.g., Sum, Count, Average) on data in an existing table. Here’s how to create one:

1. Open Your Database
   • Launch Microsoft Access 365 and open the database containing the table you want to query.
2. Open the Query Design View
   • Click on the Create tab in the Ribbon.
   • Select Query Design.
   • In the Show Table dialog box, choose the table you want to query.
   • Click Add, then click Close.
3. Add Fields to the Query
   • Double-click or drag the fields from the table that you want to include in your query.
   • Typically, you'll include a grouping field (e.g., "Category") and a field for aggregation (e.g., "Sales Amount").
4. Enable the Totals Row
   • Click on the Design tab in the Ribbon.
   • Click on the Totals button (Σ) in the Show/Hide group.
   • A new Total row appears in the query grid.
5. Choose an Aggregate Function
   • In the Total row under each field:
     • For the field you want to group by (e.g., "Category"), choose Group By.
     • For the field you want to summarize (e.g., "Sales Amount"), choose an aggregate function such as:
       • Sum (Total sales)
       • Avg (Average value)
       • Count (Number of records)
       • Min (Minimum value)
       • Max (Maximum value)
6. Apply Criteria (Optional)
   • You can add criteria to filter results. For example:
     • If you only want results for the year 2024, enter `>= #1/1/2024# AND <= #12/31/2024#` in the Criteria row under a date field.
7. Run the Query
   • Click the Run button (red exclamation mark) in the Ribbon.
   • The query results will display the grouped and aggregated data.
8. Save the Query
   • Click File > Save As.
   • Name your query (e.g., "Total Sales by Category").
   • Click OK.

Example Use Case If you have a table SalesData with fields:

• Category (Product category)
• SaleAmount (Revenue per sale)
• SaleDate (Date of sale)

A "Totals Query" could calculate total sales per category:

Category	Sum of SaleAmount
Electronics	$15,000
Furniture	$8,500

1. From a standard select query, you will make it into a Totals query that sums up the hours for each project.
   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 the Total row entry, which is currently Group By.
3. From the list of aggregate functions^[1] displayed, click "Sum".
4. Run the query. Click the View button to display the 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.

In the next lesson, you will learn what Crosstab queries are and how to create them.

[1]aggregate function: In Microsoft Access, an aggregate function performs a calculation on a group of values and returns a single value. Common aggregate functions include Sum, Avg, Count, Min, and Max, which can be used in queries to summarize data and provide insights.