Use Access' Totals query to create summary reports

In Access, you may find it difficult to assemble and summarize data from several tables. However, learn how to use the Totals Query—and get the last word on that stack of reports.

Access reports that summarize data from multiple tables can be difficult to design from scratch. For example, suppose you need a report that shows the total number of orders for each employee, as well as the total sales from those orders. Instead of using the Access report design window to design a report from three tables—Orders, Employees, and Order Details—you can use the report wizard to create the report from a single Totals Query. To do so, follow these steps:

  1. In the Database window, click Queries and then New.
  2. Click Design View and then OK.
  3. Press [Ctrl] while selecting Orders, Employees, and Order Details tables.
  4. Click Add.
  5. Double-click Employee ID and Last Name from the Employees table.
  6. Double-click Order Date from the Employees table.
  7. Enter the following expression in the next cell:
    OrderTotal: Sum([UnitPrice]*[Quantity])
  8. Press [Enter].
  9. Click the Totals button in the Query Design toolbar.
  10. In the Totals cell under Order Date, select Count.
  11. In the Sort cell under Expression, select Descending.
  12. Save the Query as OrderTotal.
  13. Use the Report Wizard to create the report using all fields in the OrderTotal query.

Your report will list all employees and their total number of orders and total sales in descending order.

Miss a tip?

Check out the Microsoft Access archive, and catch up on our most recent Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks

Free Newsletters, In your Inbox