More Query Design Options

In Access query design is essential for getting the data you want. Use query design in Access to design almost any query you want.

More types of queries

By this point, you should understand how to create a simple one-table or multi-table query using multiple criteria. Additional queries offer you the ability to perform even more complex actions with your database. One of these is the totals query, which lets you perform calculations with your data.

Watch the video below to learn more about creating a totals query.

Totals queries

Sometimes setting simple criteria won't give you the results you need, especially when you're working with numerical values. You may want to see your query results grouped or counted in some way. For example, let's say we want to find out how many of each menu item at our bakery has been ordered—how many Almond Croissants, Apple Pies, and so on. To do this, we could create a totals query to find the sum of the quantities for each item.

First, the totals query will group all similar menu items from separate orders (for example, Almond Croissants). Then, the Sum function will add the values in the Quantity field to calculate the total number sold for that item.

The Sum function helped us find the desired information in this example, but in other situations you may need to use a different function to find the answer you need. There are several functions you can choose from:

In our example above, we created a subtotal for each menu item in our query. If you wanted to create a grand total for all of the items, you would need to add a totals row. Review our lesson on Modifying Tables to learn how.