Access 2010

Introduction

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 would like your query results to automatically be sorted a certain way? If you know how to use Access's query options, 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. You'll also learn 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.

Modifying queries

Video: Modifying Queries in Access 2010

Watch the video (2:09).

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.

To modify your query:

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:

• On the Home tab of the Ribbon, click the View command. Select Design View from the drop-down menu that appears.
  Switching to Design View with the View command on the Ribbon
• In the bottom-right corner of your Access window, locate the small view icons. Click the Design View icon, which is the icon farthest to the right.
  Switching to Design View using the View Icon

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 these—these views permit advanced functions that you will not need to use for this tutorial or for most Access functions.

Sorting queries

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 that 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, then, customers will be sorted first by the City they live in and then by the Zip Code within that city.

A multilevel sort. The records will be sorted by City first.

To apply a multilevel sort:

1. Open the query, and switch to Design View.
2. Locate the field you would like to sort first. In the Sort: row, click the drop-down arrow to select either an ascending or descending sort.
   Applying an Ascending sort to a field
3. Repeat the process in the other fields to add additional sorts. Remember, the sorts are applied from left to right, so any additional sorts must be applied to fields located to the right of your primary sort. If necessary, you can rearrange the fields by clicking a field and dragging it to a new location.
   A multilevel sort
4. To apply the sort, click the Run command.
   The Run Query command
5. Your query results will appear with the desired sort.
   The sorted query results

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. 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.

The Advanced Filter/Sort command

Hiding fields within queries

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, since we wanted to make sure that 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 hide a field within a query: 

1. Open the query, and switch to Design View.
2. Locate the field you would like to hide.
3. Click the check box in the Show: row to uncheck it.
   Unchecking a field to hide it
4. To see the updated query, select the Run command. The field will be hidden.

To unhide a hidden field, simply return to Design View and click the check box in the field's Show: row again.

More types of queries

Video: Creating a Totals Query in Access 2010

Watch the video (2:09).

By this point, you should understand how to create a simple one- 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.

Totals queries

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 about 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:

• Count, which counts the number of the same items in a field
• Sum, which adds the numbers in that field
• Average, which finds the average of the numbers that occur in that field
• Maximum, which returns the highest value that has been entered in that field
• Minimum, which returns the lowest value that has been entered in that field
• First, which returns the first, or earliest, value that has been entered in that field
• Last, which returns the last, or most recent, value that has been entered in that field

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, not 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.

To create a totals query:

1. Create or open a query you would like to use as a totals query. 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.
2. In the Query Design tab, locate the Show/Hide group, then select the Totals command.
   The Totals Command
3. A row will be added to the table in the Design Grid, with all values in that row set to Group By. Select the cell in the Total: row of the field you would like to perform a calculation on, and click the drop-down arrow that appears.
   Selecting the totals row of the field we want to perform a calculation on
4. Select the calculation you would like to be performed in that field. In our example, we want to add the quantities of products we've sold, so we'll select the Sum option.
   Setting the Totals calculation to Sum
5. When you are satisfied with your query design, select the Run command on the Query Tools Design tab to run the query.
   The Run Query command
6. The query results will be displayed in the query's Datasheet View, which looks like a table. If desired, save your query by clicking the Save command in the Quick Access Toolbar. When prompted to name it, type in the desired name, then click OK.
   Saving the totals query. Note the sums in the far-right field.

More query options

We offer mini-lessons on creating additional types of queries in our Extras section. Below is a list of the queries we currently cover.

• Parameter Query
  A parameter query allows you to create a query that can be updated easily to reflect a new criterion, or search term. When you open a parameter query, Access will prompt you for a search term and then show you query results that reflect that search.
• Find Duplicates Query
  A find duplicates query lets you find all duplicate records in your database so you can delete them. Duplicate records can negatively affect the integrity of your database.

Other query-building resources

• Review our Query Criteria Quick Reference Guide for a list of criteria you can use in building queries. You can also download a printable version of the guide.

Challenge!

1. If you haven't already, download our sample database and open it.
2. Open the Customers Who've Ordered from Nearby Towns query, and switch to Design View.
3. Add a Totals row to the query.
4. Set the Totals row in the Orders Table ID field to Count. This will let us count how many orders each customer has placed.
5. In the Customers table in the Object Relationship Pane, double-click the word City to add another City field to the design grid below.
6. Click and drag the City field you just added so it is to the left of the First Name field. It should now be the leftmost field in the design grid.
7. Apply the following multilevel sort:
   • In the leftmost City field, apply an ascending sort.
   • In the Last Name field, apply an ascending sort.
8. Hide the leftmost City field.
9. Run the query. If you did it correctly, there should be 14 records in the query results. The first record should look like this.