Access 2010 More Query Design Options

Access 2010

More Query Design Options

Introduction

Modifying Queries

Watch the video (2:09). Need help?

To Modify your Query:

• 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

Sorting Queries

A multi-level sort. The records will be sorted by City first.

To Apply a Multi-Level 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 anascending 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 multi-level 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

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 very 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 checkbox 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 checkbox in the field's Show: row again.


More Types of Queries
Watch the video (2:09). Need help?
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.
Watch the video to learn how to create a totals query.
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 that 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 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 and 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 that 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 torun 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, saveyour query by clicking the Save command in the Quick Access Toolbar. When prompted to name it, type in the desired name and 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, orsearch term. When you open a parameter query, Access will prompt you for a search term, and show you  query results that reflect that search.

     How to Create a Parameter Query

     A parameter query is one of the simplest and most useful advanced queries you can create. They allow you to create a query that can be updated easily to reflect a new search term. When you open a parameter query, Access will prompt you for a search term, and show you query results that reflect that search.
     When you’re running parameter queries, search terms act as variable criteria, which are query criteria thatchange each time you run the query. For instance, say that we own a bakery and want to create a query that will quickly look up orders that were placed on a certain date. We could create a parameter query with variable criteria in the Date field. That way, each time we run the query, a dialog box will appear to prompt us to enter the date we’d like our query to search for.
     A prompt in a parameter query
     We’ll enter the date we want, then Access will run the query using the date we entered as a search term.

     To Create and Run a Parameter Query:

     1. Create a query as you normally would, modifying the table joins if necessary, selecting the fields to include in your query, and adding any non-variable criteria to the appropriate fields in the Criteria: row.
     2. Locate the field or fields that you would like to include variable criteria, and place your cursor in the Criteria:row.
     3. Type the phrase you would like to appear in the prompt that will pop up every time you run your query. Make sure to enclose the phrase in brackets [ ]. For example, in our parameter query that searches for orders placed on a certain date, we might type our criteria like this: [What date?].
     4. On the Query Design tab, click the Run command to run your query. A dialog box will appear with the prompt you specified. Enter your search term, and click OK to view your query results.
     Running a parameter criteria
     To run an existing parameter query, simply open it.

     Tips for Writing Parameter Queries

     • Ideally, the prompt you create for your query should make it clear what type of information the search term should be, and what format it should be entered in. For example, to guarantee that people enter a search for a date in the format we use in our database, we could write the following in the Criteria: row of the Date field:[What date? (mm/dd/yy)] .
     A more detailed parameter prompt
     • The simplest parameter query will give you an exact match criteria, meaning that the query will search for theexact text you enter in the prompt. However, you can turn any type of criteria into a variable criteria. Simply type your prompt text in brackets in the part of the criteria where you would normally put a search term.
       
       For example, in a normal query, we could find orders that were placed between two dates by using the criteriaBetween x AND y, and replacing the x and y with the first and second dates, respectively. To turn this into a parameter criteria, we would simply replace the x and y with the text we want to appear in the prompt. Our variable criteria might look like this: Between [Enter the start date:] AND [Enter the ending date:]. These prompts would appear:
     A parameter query using more complex criteria
     

• Find Duplicates Query

How to Create a Find Duplicates Query

To Create a Find Duplicates Query:

1. Select the Create tab on the Ribbon and locate the Queries group.
2. Click the Query Wizard command. 
The Query Wizard Command
3. The New Query dialog box will appear. Select Find Duplicates Query from the list of queries and click OK.
Choosing to create a find duplicates query
4. Select the table you want to search for duplicate records and click Next. We’re searching for duplicate customer records, so we’ll select the Customers table.
Selecting the table to search for duplicates
5. Choose the fields you wish to search for duplicate information by selecting them, then clicking the right arrow button . Only select fields that should not be identical in non-duplicate records. For instance, since we’re searching for duplicate customers, we’ll only select the First Name and Last Name fields, as it’s unlikely that multiple people with the exact same first and last name would place orders at our bakery. When you’ve added the desired fields, click Next.
Selecting the fields to search for duplicate information
6. Select additional fields to view in your query results. Choose fields that will help you distinguish between the duplicate records and choose which one you want to keep.In our example, we’ll add all of the fields relating to customer addresses, plus the email address and phone number fields, as records with identical customer names might contain non-identical information in these fields. When you’re satisfied with the fields you’ve chosen, click Next.
Selecting the other fields to view in our results
7. Access will suggest a name for your query, but if you’d like, you can type in a different name. When you’re satisfied with the query name, click Next to run your query.
Naming the query
8. If Access found any duplicate records in your query, they will be displayed in the query results. Review the records and delete any outdated or incorrect records as needed.
Duplicate records in the query results

Tips for Resolving Duplicate Records

• Save your duplicate records queries and run them often.
• Investigate potential duplicate records by looking at linked data in other tables. You can do this by searching for these records’ record ID numbers in related tables. Is one record linked to mostly old orders while another contains recent ones? The latter is likely to be the current one.
• Once you decide which record to delete, make sure you won’t be losing any information you might need. In our example, before we deleted our duplicate record, we found all of the orders linked to that record’s ID numberand replaced it with the ID number of the record we decided to keep.

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