Access 2010 offers many 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 how many of something exists within your database? Or what if you want your query results to automatically be sorted a certain way? If you know how to use its query options, you can design almost any query you want in Access.
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.
We will be showing you how to design and run queries with examples from our sample database. If you would like to follow along, download our example and use it to follow the procedures demonstrated in this lesson.
Access offers many 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 or 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 offers other query views, like Pivot Table View, Pivot Chart View, and SQL View. You can ignore them; these views permit advanced functions 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, every single time you view it.
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 full of customers and their addresses, you might choose to first sort the records by city, then further sort them 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 below example, customers will be sorted first by the City they live in 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. From the Home tab on the Ribbon, select the Advanced drop-down command in the Sort & Filter group. 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 that 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 too might have even 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 check box in the field's Show: row again.
By this point, you should understand how to create a simple 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.
Sometimes setting simple criteria won't give you the results you need, especially when you're working with numbers. You may want to see your query results grouped or counted in some way. Access 2010 offers several options that make these functions possible. Perhaps the easiest of these is the Totals command.
When you use the Totals function in your query, the data in your fields will be grouped by value, meaning all items of one type are listed together. For instance, in a totals query on the items sold at our bakery, each type of item sold would be listed on a single row, no matter how many times that item had been sold.
Once your records are grouped, you can perform calculations with them. These calculations include:
These calculations will apply to the rows containing your grouped items. For example, if you decided to use Sum to find out how many of each item on a menu has been ordered, you would get a subtotal for each item in your query rather than a grand total of all of the items combined.
To add a calculation like a grand total to your query or table, review the instructions for creating a Totals row in our Modifying Tables lesson.
We offer shorter lessons on creating additional types of queries in our Extras section. Below is a list of the queries we currently cover.