Using Totals queries in Microsoft Access 365

A standard SELECT query is ideal when you want to see every matching record in detail. However, many reporting tasks require summarized information instead:

• Total hours worked per project
• Number of orders per customer
• Average donation amount per campaign

In Microsoft Access 365, you use a Totals query to transform a regular Select query into a grouped summary. A Totals query lets you group records (for example, by project or customer) and then apply aggregate functions such as Sum, Count, or Avg to produce group totals.

You begin by creating a normal Select query in Design View. After you choose the fields you want, you convert the query into a Totals query using the Totals button (Σ) on the Ribbon.

When you enable Totals, Access adds a new row to the query grid called the Total row. For each field in your query, you choose whether the field:

• Defines the grouping (using Group By), or
• Is summarized with an aggregate function such as Sum or Count, or
• Is used only for criteria (using Where).

The following table summarizes the aggregate options available in Totals queries.

• Microsoft Access Aggregate Functions
  

  Function	Description
  Sum	Adds all values in the field.
  Avg	Returns the average (mean) value.
  Min	Returns the smallest value.
  Max	Returns the largest value.
  Count	Counts the records in each group (or non-null values in a field).
  StDev	Calculates the sample standard deviation.
  Var	Calculates the sample variance.
  First	Returns the first value in the group (based on the underlying sort order).
  Last	Returns the last value in the group.
  Expression	Uses a calculated expression (for example, multiplying or combining fields).
  Where	Uses the field only for criteria; the field does not appear in the result set.

The following steps show how to build a Totals query in Microsoft Access 365 using Query Design view. The example assumes a table that tracks hours worked per project.

1. Open your database
   • Start Microsoft Access 365 and open the database that contains your data table.
2. Create a new Select query
   • On the Ribbon, choose the Create tab.
   • Click Query Design.
   • In the Show Table dialog box, select the table you want to summarize.
   • Click Add, then click Close.
3. Add fields to the query
   • In the upper pane, double-click each field you want to include in the query grid. For example, for a project-hours scenario you might add:
     • ProjectName (or ProjectID)
     • HoursWorked
     • WorkDate (optional, for date filters)
4. Enable the Totals row
   • On the Design tab (Query Tools), locate the Show/Hide group.
   • Click the Totals button (Σ).
   • A new Total row appears in the query grid.
5. Set grouping and aggregate functions
   • In the Total row:
     • For ProjectName (or ProjectID), choose Group By.
     • For HoursWorked, choose Sum to total hours per project.
     • For date or filtering-only fields, choose Where and add criteria as needed.
6. Add criteria (optional)
   • You can limit the rows included in each group total. For example:
     • To include only work done in 2024, place the cursor in the WorkDate column and use the Criteria row with a condition such as:
       >= #1/1/2024# AND <= #12/31/2024#
7. Run and review the Totals query
   • Click the Run button (red exclamation mark) on the Ribbon.
   • The results datasheet displays one row per group (for example, one row per project), with the calculated totals in the aggregated fields.
8. Save the query
   • Save the query with a clear, descriptive name such as Total Hours by Project.
   • You can reuse this query in forms, reports, and dashboards.

Example output – Totals query for a sales table:

• The table SalesData contains:
  • Category – product category
  • SaleAmount – revenue per sale
  • SaleDate – date of sale

A Totals query with Category set to Group By and SaleAmount set to Sum could produce:

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

1. Start from a standard SELECT query that shows ProjectName and HoursWorked for each time entry. Click the Totals (Σ) button to convert it into a Totals query.
2. In the Total row of the grid:
   • Confirm that ProjectName is set to Group By.
   • Change the Total row for HoursWorked from Group By to Sum.
3. (Optional) Add a date field, set its Total row value to Where, and supply a date range to restrict which time entries are included.
4. Run the query. The result shows one row per project, with a single field that displays total hours worked per project.

This pattern—Group By for categories, Sum/Count/Avg for numeric measures, and Where for criteria-only fields—is a best practice when designing Totals queries in Access 365.

Using the Totals row in Datasheet view

• Getting quick totals for a column
  When you just need a quick summary and do not want to build a saved query, Access offers a Totals row in Datasheet view:
  1. Open the table or query in Datasheet view, then on the Home tab, in the Records group, click Totals.
  2. A new row labeled Total appears at the bottom of the datasheet. Click in this row under any numeric or currency column to display a drop-down list of calculations (Sum, Avg, Count, Min, Max, etc.).
  3. Choose the calculation you need. Access immediately shows the result in the Totals row. If you add or edit records, the totals update automatically.

The Datasheet Totals row respects any filters you apply. If you filter a table down to five rows, only those five rows are used in the calculation. This makes it a convenient tool for quick “what-if” checks, while saved Totals queries remain the best option for reusable reports and forms.

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 you use in Totals queries to summarize data and present group totals.