Access offers several options that let you design and run queries that return exactly the information you're looking for. For instance, what if you need to find out how many of something exists within your database? Or what if you would like your query results to automatically be sorted a certain way? If you know how to use query options in Access, you can design almost any query you want.
In this lesson, you'll learn how to modify and sort your queries within Query Design view, as well as how to use the Totals function to create a query that can perform calculations with your data. You'll also learn about additional query-building options offered in Access.
Throughout this tutorial, we will be using a sample database. If you would like to follow along, you'll need to download our Access 2016 sample database. You will need to have Access 2016 installed on your computer in order to open the example.
Watch the video below to learn more about modifying queries.
Access offers several options for making your queries work better for you. In addition to modifying your query criteria and joins after you build your queries, you can choose to sort and hide fields in your query results.
When you open an existing query in Access, it is displayed in Datasheet view, meaning you will see your query results in a table. To modify your query, you must enter Design view, the view you used when creating it. There are two ways to switch to Design view:
Once in Design view, make the desired changes, then select the Run command to view your updated results.
You may notice that Access also offers SQL view. You can ignore this. SQL view allows you to create advanced functions that you will not need to use for this tutorial or for most Access functions.
Access allows you to apply multiple sorts at once while you're designing your query. This allows you to view your data exactly the way you want.
A sort that includes more than one sorted field is called a multilevel sort. A multilevel sort allows you to apply an initial sort, then further organize data with additional sorts. For instance, if you had a table filled with customers and their addresses, you might choose to first sort the records by city, then alphabetically by last name.
When more than one sort is included in a query, Access reads the sorts from left to right. This means the leftmost sort will be applied first. In the example below, customers will be sorted first by the City where they live and then by the Zip Code within that city.
You can also apply multilevel sorts to tables that don't have queries applied to them. On the Home tab on the Ribbon, select the Advanced drop-down command in the Sort & Filter group. From the menu that appears, select Advanced Filter/Sort and create the multilevel sort as you normally would. When you're finished, click the Toggle Filter command to apply your sort.
Sometimes you might have fields that contain important criteria, but you might not need to actually see the information from that field in the final results. For example, take one of the queries we built in our last lesson: a query to find the names and contact information of customers who had placed orders. We included Order ID numbers in our query because we wanted to make sure we only pulled customers who had placed orders.
However, we really didn't need to see this information in our final query results. In fact, if we were just looking for customer names and addresses, seeing the order number mixed in there might have been distracting. Fortunately, Access makes it easy to hide fields while still including any criteria they contain.
To unhide a hidden field, simply return to Design view and click the checkbox in the field's Show: row again.
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.
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.
For our example, we want to find the total number we've sold of each of our menu items, so we'll use a query showing us all of the menu items we've sold. If you want to follow along in our database, open the Menu Items Ordered query.
We offer mini-lessons on creating additional types of queries in the last lesson in this tutorial. Below is a list of the queries we currently cover.