Chapter 4. What Every Developer Needs to Know About Query Basics

IN THIS CHAPTER

• Why This Chapter Is Important
• What Is a Query, and When Should You Use One?
• Everything You Need to Know About Query Basics
• Ordering Your Query Result
• Refining Your Query with Criteria
• Working with Dates in Criteria
• Understanding How You Can Update Query Results
• Building Queries Based on Multiple Tables
• Creating Calculated Fields
• Getting Help from the Expression Builder
• Summarizing Data with Totals Queries
• Excluding Fields from the Output
• Understanding Nulls and Query Results
• Refining Your Queries with Field, Field List, and Query Properties
• Building Parameter Queries When You Don’t Know the Criteria at Design Time
• Adding Smart Tags to Your Queries Needed by the Time and Billing Application for a Computer Consulting Firm
• Creating a Pivot Table or Pivot Chart from a Query
• Understanding Query Specifications and Limitations
• Practical Examples: Building Queries Needed by the Time and Billing Application for a Computer Consulting Firm

Why This Chapter Is Important

Although tables act as the ultimate foundation for any application that you build, queries are very important as well. Most of the forms and reports that act as the user interface for your application are based on queries. An understanding of queries, what they are, and when and how to use them is imperative for your success as an Access application developer. This chapter teaches you the basics of working with queries. After reading this chapter, you will know how to build queries, add tables and fields to the queries that you create, sort the query output, and apply criteria to limit the data that appears in the query output. You will also be familiar with tips and tricks and important “gotchas” of working with queries.

What Is a Query, and When Should You Use One?

Microsoft Access offers several different types of queries. This chapter focuses on the most basic type of query, the Select query. A Select query is a stored question about the data stored in your database’s tables. Select queries are the foundation of much of what you do in Access. They underlie most of your forms and reports, allowing you to view the data you want, when you want. You use a simple Select query to define the tables and fields whose data you want to view and also to specify the criteria to limit the data that the query’s output displays. A Select query is a query of a table or tables that just displays data; it doesn’t modify data in any way. You use more advanced Select queries to summarize data, supply the results of calculations, or cross-tabulate your data. You can use Action queries to add, edit, or delete data from your tables, based on selected criteria, but this chapter covers Select queries. Chapter 12, “Advanced Query Techniques,” covers other types of queries.

Everything You Need to Know About Query Basics

Creating a basic query is easy because Microsoft has provided a user-friendly, drag-and-drop interface. There are two ways to start a new query in Access 2007. The first way is to select the Create tab and then click to select the Query Wizard button in the Other group. The New Query dialog box appears (see Figure 4.1). The Simple Query Wizard walks you through the steps for creating a basic query. The other wizards help you create three specific types of queries: Crosstab, Find Duplicates, or Find Unmatched. The second method is to click to select the Create tab and then click to select the Query Design button in the Other group. The Show Table dialog box appears (see Figure 4.2). This dialog box lets you select which tables and queries you want to include in the query.

Figure 4.1. Use the New Query dialog box to select a wizard for the query you want to create.

Figure 4.2. When you click to select the Query Design icon in the Other group of the Create tab, the Show Table dialog box appears.

Adding Tables to Your Query

As mentioned previously, if you select Query Design rather than one of the wizards, the Show Table dialog box appears (see Figure 4.2). In this dialog box, you can select the tables or queries that supply data to your query. Access doesn’t care whether you select tables or queries as the foundation for your queries. You can select them by double-clicking on the name of the table or query you want to add or by clicking on the table or query and then clicking Add. You can select multiple tables or queries by using the Shift key to select a contiguous range of tables or queries, or the Ctrl key to select noncontiguous tables or queries. When you have selected the tables or queries you want, click Add and then click Close. This brings you to the Query Design window shown in Figure 4.3.

Figure 4.3. The Query Design window presents an easy-to-use (and learn) query design grid.

Adding Fields to Your Query

You’re now ready to select the fields you want to include in the query. The query shown in Figure 4.3 is based on the tblClients table and the tblProjects table included in the CHAP4.ACCDB database on the sample code website. Notice that the query window is divided into two sections. The top half of the window shows the tables or queries that underlie the query you’re designing; the bottom half shows any fields that will be included in the query output. You can add a field to the query design grid on the bottom half of the query window in several ways:

• Double-click the name of the field you want to add.
• Click and drag a single field from the table in the top half of the query window to the query design grid below.
• Select multiple fields at the same time by using your Shift key (for a contiguous range of fields) or your Ctrl key (for a noncontiguous range). You can double-click the title bar of the field list to select all fields and then click and drag any one of the selected fields to the query design grid.

Create a database based on the Northwind 2007 template database that comes with Access (see Figure 4.4). If you want to prevent the Startup form from appearing, hold down your Shift key as you click the Create button and until the database opens. Click to select the Create tab. Select Query Design from the Other group. The Show Table dialog box appears. Add the Customers table to the query and close the Show Table dialog.

Figure 4.4. Create a database based on the Northwind 2007 template database that comes with Access.

Follow these steps to select eight fields from the Customers table:

1. Click the ID field.
2. Hold down your Shift key and click the Job Title field. This should select the ID, Company, First Name, Last Name, E-mail Address, and Job Title fields.
3. Scroll down the list of fields, using the vertical scrollbar, until the Country/Region field is visible.
4. Hold down your Ctrl key and click the Home Phone field.
5. With the Ctrl key still held down, click the Country/Region field. All eight fields should now be selected.

Click and drag any of the selected fields from the table on the top half of the query window to the query design grid on the bottom. All eight fields should appear in the query design grid (see Figure 4.5). You might need to use the horizontal scrollbar to view some of the fields on the right.

Figure 4.5. After you add fields to the query, they appear on the query design grid.

Removing a Field from the Query Design Grid

To remove a field from the query design grid, follow these steps:

1. Find the field you want to remove.
2. Click the small horizontal gray button (column selector) immediately above the name of the field. The entire column of the query design grid should become black (see Figure 4.6).

   Figure 4.6. Removing a field from the query design grid.

   

3. Press the Delete key or click Delete Columns in the Query Setup group of the Design tab.

Assume that you have decided to remove the Region field from the query design grid. Use the horizontal scrollbar to see the Country/Region field on the query design grid, and then do the following:

1. Click the column selector immediately above the Country/Region field. The entire column of the query design grid should become black, and the cursor turns into a downward-pointing arrow.
2. Press the Delete key to remove the Country/Region field from the query design grid.

Inserting a Field After the Query Is Built

The process for inserting a field after a query is built differs, depending on where you want to insert the new field. If you want to insert it after the existing fields, the easiest method is to double-click the name of the field you want to add. If you prefer to insert the new field between two existing fields, the best approach is to click and drag the field you want to add, dropping it onto the field you want to appear to the right of the inserted field.

To insert the Business Phone field between the Job Title and Home Phone fields, click and drag the Business Phone field from the table until it’s on top of the Home Phone field. This technique inserts the field in the correct place. To run the query, click Run on the ribbon.

Moving a Field to a Different Location on the Query Design Grid

Although the user can move a column while in a query’s Datasheet view, sometimes you want to permanently alter the position of a field in the query output. You can do this as a convenience to the user or, more importantly, because you will use the query as a foundation for forms and reports. The order of the fields in the query becomes the default order of the fields on any forms and reports you build using any of the wizards. You can save yourself quite a bit of time by ordering your queries effectively.

To move a single column, follow these steps:

1. Select a column while in the query’s Design view by clicking its column selector (the button immediately above the field name).
2. Click the selected column a second time and then drag it to a new location on the query design grid.

Follow these steps to move more than one column at a time:

1. Drag across the column selectors of the columns you want to move.
2. Click any of the selected columns a second time and then drag them to a new location on the query design grid.

Move the First Name and Last Name fields so that they appear before the Company field. Do this by clicking and dragging from the column selector for First Name to the column selector for Last Name. Both columns should be selected. Click again on the column selector for either column and then click and drag until the thick black line jumps to the left of the Company field.

Saving and Naming Your Query

To save your query at any time, click the Save button on the Quick Access toolbar. If the query is a new one, Access prompts you to name your query. Query names should begin with the tag qry so that you can easily recognize and identify them as queries. It’s important to understand that, when you save a query, you’re saving only the query’s definition, not the actual query result.

Return to the design of the query. To save your work, click Save on the Quick Access toolbar that appears to the right of the Microsoft Access button. When prompted for a name, call the query qryCustomers.

Ordering Your Query Result

When you run a new query, notice that the query output appears in no particular order, but generally, you want to order it. You can do this by using the Sort row of the query design grid.

To order your query result, follow these steps:

1. In Design view, click within the query design grid in the Sort cell of the column you want to sort (see Figure 4.7).

   Figure 4.7. Changing the order of the query result.

   

2. Use the drop-down combo box to select an ascending or descending sort.

To sort in ascending order by the Company field, follow these steps:

1. In Design view, click in the Sort row of the query design grid for the Company field.
2. Open the Sort drop-down combo box.
3. Select Ascending.
4. Run your query and view the results. Your records should now be ordered by the Company field.
5. If you want to return to the query’s design, click View on the toolbar.

Sorting by More Than One Field

Quite often, you want to sort your query output by more than one field. The columns you want to sort must be placed in order from left to right on the query design grid, with the column you want to act as the primary sort on the far left and the secondary, tertiary, and any additional sorts following to the right. If you want the columns to appear in a different order in the query output, you must move them manually in Datasheet view after you run the query.

Sort the query output by the Country/Region field and, within individual country groupings, by the Last Name and First Name fields. Because sorting always occurs from left to right, you must place the Country/Region field before the LastName and FirstName fields. Therefore, you must move the Country/Region field. Follow these steps:

1. Select the Country/Region field from the query design grid by clicking the thin gray button above the Country/Region column.
2. After you have selected the Country/Region field, move your mouse back to the thin gray button and click and drag to the left of the First Name and Last Name fields. A thick gray line should appear to the left of the First Name field.
3. Release the mouse button.
4. Select the Last Name field from the query design grid by clicking the thin gray button above the Last Name column.
5. After you have selected the Last Name field, move your mouse back to the thin gray button and click and drag to the left of the First Name field. A thick gray line should appear to the left of the First Name field.
6. Release the mouse button.
7. Change the sort of the Country/Region field to Ascending.
8. Change the sort of the Last Name field to Ascending.
9. Change the sort of the First Name field to Ascending.
10. Run the query. The records should be ordered by country/region and, within the country grouping, by last name and first name.

Refining Your Query with Criteria

So far, you have learned how to select the fields you want and how to indicate the sort order for your query output. One of the important features of queries is the capability to limit your output by selection criteria. Access allows you to combine criteria by using any of several operators to limit the criteria for one or more fields. The operators and their meanings are covered in Table 4.1.

Table 4.1. Access Operators and Their Meanings

Criteria entered for two fields on a single line of the query design grid are considered an And, which means that both conditions need to be true for the record to appear in the query output. Entries made on separate lines of the query design grid are considered an Or, which means that either condition can be true for the record to be included in the query output. Take a look at the example in Figure 4.8; this query would output all records in which the Job Title field begins with either Marketing or Owner, regardless of the last name. It outputs the records in which the Job Title field begins with Sales only for the customers whose last names begin with the letters M through R inclusive.

Figure 4.8. Adding And and Or conditions to a query.

Design a query to find all the sales agents in Brazil or France. The criteria you build should look like those in Figure 4.9.

Figure 4.9. The criteria to select sales agents whose country is either Brazil or France.

1. Notice that the criterion for the Country/Region field is "Brazil" Or "France" because you want both Brazil and France to appear in the query output. The criterion for the Job Title field is "Sales Agent". Because the criteria for both the Country/Region and Job Title fields are entered on the same line of the query design grid, both must be true for the record to appear in the query output. In other words, the customer must be in either Brazil or France and must also be a sales agent.
2. Modify the query so that you can output all the customers for whom the job title begins with Sales. Try changing the criteria for the Job Title field to Sales. Notice that no records appear in the query output because no contact titles are just Sales. You must enter "Like Sales*" for the criteria. Now you get the Sales Agents, Sales Associates, Sales Managers, and so on. You still don’t see the Assistant Sales Agents because their titles don’t begin with Sales. Try changing the criteria to "Like *Sales*". Now all the Assistant Sales Agents appear.

Working with Dates in Criteria

Access gives you significant power for adding date functions and expressions to your query criteria. Using these criteria, you can find all records in a certain month, on a specific weekday, or between two dates. Table 4.2 lists several examples.

Table 4.2. Sample Date Criteria

The Weekday(Date, [FirstDayOfWeek]) function works based on your locale and how your system defines the first day of the week. Weekday() used without the optional FirstDayOfWeek argument defaults to vbSunday as the first day. A value of 0 defaults the FirstDayOfWeek to the system definition. Other values can be set also.

Figure 4.10 illustrates the use of a date function. Notice that DatePart("q",[Order Date]) is entered as the expression, and the value of 2 is entered for the criteria. Year([Order Date)] is entered as another expression, with the number 2007 as the criteria. Therefore, this query outputs all records in which the order date is in the second quarter of 2007.

Figure 4.10. Using the DatePart() and Year() functions in a query.

Understanding How You Can Update Query Results

If you haven’t realized it yet, you can usually update the results of your query. This means that if you modify the data in the query output, the data in the tables underlying the query is permanently modified.

Build a query based on the Customers table. Add the ID, Company, Address, City, and Country/Region fields to the query design grid; then run the query. Change the address of a particular customer and make a note of the customer ID of the customer whose address you changed. Make sure you move off the record so that Access writes the change to disk. Close the query, open the actual table in Datasheet view, and find the record whose address you modified. Notice that the change you made was written to the original table; the reason is that a query result is a dynamic set of records that maintains a link back to the original data. You get this result whether you’re on a standalone machine or on a network.

Building Queries Based on Multiple Tables

If you have properly normalized your table data, you probably want to bring the data from your tables back together by using queries. Fortunately, you can do this quite easily with Access queries.

The query in Figure 4.11 joins the Customers, Orders, and Order Details tables, pulling fields from each. Notice that the ID and Company fields are selected from the Customers table, the Order ID and Order Date from the Orders table, and the Unit Price and Quantity from the Order Details table. After running this query, you should see the results shown in Figure 4.12. By creating a multitable query, you can look at data from related tables, along with the data from the Order Details table.

Figure 4.11. A query joining the Customers, Orders, and Order Details tables.

Figure 4.12. The results of querying multiple tables.

Build a query that combines information from the Customers, Orders, and Order Details tables. To do this, build a new query by following these steps:

1. Click to select the Create tab.
2. Click Query Design in the Other group. The Show Table dialog box appears.
3. From the Show Table dialog box, select Customers, Orders, and Order Details by holding down the Ctrl key and clicking on each table name. Then select Add.
4. Click Close.
5. Some of the tables included in the query might be hiding below. If so, scroll down using the vertical scrollbar to view any tables that aren’t visible. Notice the join lines between the tables; they’re based on the relationships set up in the Relationships window.
6. Select the following fields from each table:

   Customers: Country/Region, City
   Orders: Order Date
   Order Details: Unit Price, Quantity

7. Sort by Country/Region and then City in ascending order. Your finished query design should look like the one in Figure 4.13.

   Figure 4.13. The query design from the example.

   

8. Run the query. Data from all three tables should be included in the query output.

Pitfalls of Multitable Queries

You should be aware of some pitfalls of multitable queries; they involve updating as well as which records you see in the query output.

Remember that you can’t update certain fields in a multitable query. These are the join fields on the “one” side of a one-to-many relationship (unless the Cascade Update Referential Integrity feature has been activated). You also can’t update the join field on the “many” side of a relationship after you’ve updated data on the “one” side. More importantly, which fields can be updated, and the consequences of updating them, might surprise you. If you update the fields on the “one” side of a one-to-many relationship, you must be aware of that change’s impact. You’re actually updating that record in the original table on the “one” side of the relationship; several records on the “many” side of the relationship will be affected.

For example, Figure 4.14 shows the result of a query based on the Customers, Orders, and Order Details tables. I have changed "Company AA" to "Business AA" on a specific record of my query output. You might expect this change to affect only that specific order detail item. Pressing the down-arrow key to move off the record shows that all records associated with Company AA have been changed (see Figure 4.15). This happened because all the orders for Company AA were actually getting their information from one record in the Customers table—the record for ID 27. This is the record I modified while viewing the query result.

Figure 4.14. Changing a record on the “one” side of a one-to-many relationship.

Figure 4.15. The result of changing a record on the “one” side of a one-to-many relationship. Notice that the Company Name field has been updated for all records with 27 as the ID.

To get this experience firsthand, try changing the data in the City field for one of the records in the query result. Notice that the record (as well as several other records) is modified. This happens because the City field actually represents data from the “one” side of the one-to-many relationship. In other words, when you’re viewing the Country/Region and City fields for several records in the query output, the data for the fields might originate from one record. The same goes for the Order Date field because it’s also on the “one” side of a one-to-many relationship. The only field in the query output that can’t be modified is TotalPrice, a calculated field. Practice modifying the data in the query result and then returning to the original table and noticing which data has changed.

The second pitfall of multitable queries is figuring out which records result from such a query. So far, you have learned how to build only inner joins. Join types are covered in detail in Chapter 12, but for now, it’s important to understand that the query output contains all customers, regardless if they have orders, and all orders, regardless if they have order detail. This means that not all customers and all orders are listed. In Chapter 12, you’ll learn how to build queries in which you can list only customers who have orders. You’ll also learn how to list only the customers without orders.

Row Fix-Up in Multitable Queries

The Row Fix-Up feature is automatically available to you in Access. As you fill in key values on the “many” side of a one-to-many relationship in a multitable query, the nonkey values are automatically looked up in the parent table. Most database developers refer to this as enforced referential integrity. A foreign key must first exist on the “one” side of the query to be entered successfully on the “many” side. As you can imagine, you don’t want to be able to add an order to your database for a nonexistent customer.

For example, the query in Figure 4.16 is based on the Customers and Orders tables. The fields included in the query are Customer ID from the Orders table; Company, Address, and City from the Customers table; and Order ID from the Orders table. If the Customer ID associated with an order is changed, the Company, Address, and City are looked up from the Customers table and immediately displayed in the query result. Notice in Figure 4.17 how the information for Business AA is displayed in the query result. Figure 4.18 shows that the Company, Address, and City change automatically when the Customer ID is changed to Company AA. Don’t be confused by the combo box used to select the customer ID. The presence of the combo box within the query is a result of Access’s auto-lookup feature, covered in Chapter 2, “What Every Developer Needs to Know About Databases and Tables.” The customer ID associated with a particular order is actually being modified in the query. If the user adds a new record to the query, Access fills in the customer information as soon as the user selects the customer ID associated with the order.

Figure 4.16. This query illustrates the use of Row Fix-Up in a query with multiple tables.

Figure 4.17. The query result before selecting another customer ID.

Figure 4.18. The result of an auto-lookup after the customer ID is changed. The information on the “one” side of the relationship is “fixed up” to display information for the appropriate customer.

Creating Calculated Fields

One of the rules of data normalization is that the results of calculations shouldn’t be included in your database. You can output the results of calculations by building those calculations into your queries, and you can display the results of the calculations on forms and reports by making the query the foundation for a form or report. You can also add controls to your forms and reports containing the calculations you want. In certain cases, this can improve performance. (This topic is covered in more depth in Chapter 16, “Debugging: Your Key to Successful Development.”)

The columns of your query result can hold the result of any valid expression, including the result of a user-defined function. This makes your queries extremely powerful. For example, you can enter the following expression:

Left([First Name],1) & "." & Left([Last Name],1) & "."

This expression would give you the first character of the first name followed by a period, the first character of the last name, and another period. An even simpler expression would be this one:

[Unit Price]*[Quantity]

This calculation would simply take the Unit Price field and multiply it by the Quantity field. In both cases, Access would automatically name the resulting expression. For example, the calculation that results from concatenating the first and last initials is shown in Figure 4.19. Notice that in the figure, the expression has been given a name (often referred to as an alias). To give the expression a name, such as Initials, you must enter it as follows:

Initials:Left([First Name],1) & "." & Left([Last Name],1) & "."

Figure 4.19. The result of the expression Initials:Left([FirstName],1) & "." & Left([LastName],1) & "." in the query.

The text preceding the colon is the name of the expression—in this case, Initials. If you don’t explicitly give your expression a name, it defaults to Expr1.

Follow these steps to add a calculation that shows the unit price multiplied by the quantity:

1. Scroll to the right on the query design grid until you can see a blank column.
2. Click in the Field row for the new column.
3. Type Total Price:[Unit Price]*Quantity. (These fields come from the Order Details table.) If you want to see more easily what you’re typing, press Shift+F2 (Zoom). The dialog box shown in Figure 4.20 appears. (Access will supply the space after the colon and the square brackets around the field names if you omit them.)

   Figure 4.20. Expanding the field with the Zoom function (Shift+F2).

   

4. Click OK to close the Zoom window.
5. Run the query. The total price should appear in the far-right column of the query output. The query output should look like the one in Figure 4.21.

   Figure 4.21. The result of the total price calculation.

   

Getting Help from the Expression Builder

The Expression Builder is a helpful tool for building expressions in your queries, as well as in many other situations in Access. To invoke the Expression Builder, click in the Field cell of your query design grid and then click Builder on the Ribbon (see Figure 4.22). Notice that the Expression Builder is divided into three columns. The first column shows the objects in the database. After selecting an element in the left column, select the elements you want to paste from the middle and right columns.

Figure 4.22. The Expression Builder makes it easier for you to create expressions in your query.

The example in Figure 4.23 shows Functions selected in the left column. Within Functions, both user-defined and built-in functions are listed; here, the Functions object is expanded with Built-In Functions selected. In the center column, Date/Time is selected. After you select Date/Time, all the built-in date and time functions appear in the right column. If you double-click a particular function—in this case, the DatePart function—the function and its parameters are placed in the text box at the top of the Expression Builder window. Notice that the DatePart function has four parameters: interval, date, firstweekday, and firstweek. If you know what needs to go into each of these parameters, you can simply replace the parameter placemarkers with your own values. If you need more information, you can invoke help on the selected function and learn more about the required parameters. In Figure 4.24, two parameters are filled in: the interval and the name of the field being evaluated. After you click OK, the expression is placed in the Field cell of the query.

Figure 4.23. The Expression Builder with the DatePart function selected and pasted in the expression box.

Figure 4.24. A function pasted by Expression Builder with the parameters updated with appropriate values.

Summarizing Data with Totals Queries

By using Totals queries, you can easily summarize numeric data. You can use Totals queries to calculate the Sum, Average, Count, Minimum, Maximum, and other types of summary calculations for the data in your query result. These queries let you calculate one value for all the records in your query result or group the calculations as desired. For example, you could determine the total sales for every record in the query result, as shown in Figure 4.25, or you could output the total sales by country and city (see Figure 4.26). You could also calculate the total, average, minimum, and maximum sales amounts for all customers in the United States. The possibilities are endless.

Figure 4.25. Total sales for every record in the query result.

Figure 4.26. Total sales by country and city.

To create a Totals query, follow these steps:

1. Add to the query design grid the fields or expressions you want to summarize. It’s important that you add the fields in the order in which you want them grouped. For example, Figure 4.27 shows a query grouped by country and then city.

   Figure 4.27. Selecting the type of calculation for the Total row from a drop-down list.

   

2. Click Totals on the ribbon to add a Total row to the query. By default, each field in the query has Group By in the Total row.
3. Click in the Total row on the query design grid.
4. Open the combo box and choose the calculation you want (see Figure 4.27).
5. Leave Group By in the Total cell of any fields you want to group by, as shown in Figure 4.27. Remember to place the fields in the order in which you want them grouped. For example, if you want the records grouped by country and then by sales representative, you must place the Country/Region field to the left of the Employee ID field on the query design grid. On the other hand, if you want records grouped by Employee ID and then by country, you must place the Employee ID field to the left of the Country field on the query design grid.
6. Add the criteria you want to the query.

Figure 4.28 shows the design of a query that finds the total, average, maximum, and number of sales by country and city; Figure 4.29 shows the results of running the query. As you can see, Totals queries can give you valuable information.

Figure 4.28. A query that finds the total, average, maximum, and number of sales by country and city.

Figure 4.29. The results of running a query with many aggregate functions.

If you save this query and reopen it, you’ll see that Access has made some changes to its design. The Total cell for the Sum is changed to Expression, and the Field cell is changed to the following:

TotalSales: Sum([Unit Price]*[Quantity])

If you look at the Total cell for the Avg, it’s also changed to Expression. The Field cell is changed to the following:

AverageSales: Avg([Unit Price]*[Quantity])

Access modifies the query in this way when it determines that you’re using an aggregate function on an expression having more than one field. You can enter the expression either way. Access stores and resolves the expression as noted.

Modify the query to show the total sales by country, city, and order date. Before you continue, save your query as qryCustomerOrderInfo and then close it. With the list of queries visible, click qryCustomerOrderInfo. Right-click the query and select Copy from the context-sensitive menu. Right-click the query again and select Paste. Access should prompt you for the name of the new query. Type qryCustomerOrderSummary and click OK. Right-click qryCustomerOrderSummary and select Design from the context-sensitive menu. Delete both the Unit Price and Quantity fields from the query output. To turn your query into a Totals query, follow these steps:

1. Click Totals on the Design tab of the ribbon. Notice that an extra line, called the Total line, is added to the query design grid; this line says Group By for all fields.
2. Group by country, city, and order date but total by the total price (the calculated field). Click the Total row for the Total Price field and use the drop-down list to select Sum (refer to Figure 4.27).
3. Run the query. Your result should be grouped and sorted by country, city, and order date, with a total for each unique combination of the three fields.
4. Return to the query’s design and remove the order date from the query design grid.
5. Rerun the query. Notice that now you’re summarizing the query by country and city.
6. Change the Total row to Avg. Now you’re seeing the average price times quantity for each combination of country and city. Change it back to Sum and save the query.

As you can see, Totals queries are both powerful and flexible. You can’t edit their output, but you can use them to view the sum, minimum, maximum, average, and count of the total price, all at the same time. You can easily modify how you’re viewing this information—by country, country and city, and so on—all at the click of your mouse.

Excluding Fields from the Output

At times, you need to include a column in your query that you don’t want displayed in the query output; this is often the case with columns used solely for criteria. Figure 4.30 shows an example. If you run this query, you get the total, average, count, and maximum sales grouped by both country and order date. However, you want to group only by country and use the order date only as criteria. Therefore, you need to set the Total row of the query to Where, as shown in Figure 4.31. The column used in Where has been excluded from the query result. You can easily determine this by noting that the check box in the Show row of the Order Date column is unchecked.

Figure 4.30. A query with criteria for the order date, before excluding fields from the query output.

Figure 4.31. The Total row of the Order Date field is set to Where, excluding the field from the query result.

Understanding Nulls and Query Results

Null values in your table’s fields can noticeably affect query results. A Null value is different from a zero or a zero-length string, which indicates that the data doesn’t exist for a particular field; a field contains a Null value when no value has yet been stored in the field. (As discussed in Chapter 2, you enter a zero-length string in a field by typing two quotation marks.)

Null values can affect the results of multitable queries, queries including aggregate functions (Totals queries), and queries with calculations. Null values can also affect the result of aggregate queries. For example, if you perform a count on a field containing Null values, the Access Database Engine includes in the count only records having non-Null values in that field. If you want to get an accurate count, it’s best to perform the count on a primary key field or some other field that can’t have Null values.

Probably the most insidious problem with Nulls happens when you include them in calculations. Including a Null value in a calculation containing a numeric operator (+, -, /, *, and so on) results in a Null value. In Figure 4.32, for example, notice that the query includes a calculation that adds the values in the Parts and Labor fields. These fields have been set to have no default value and, therefore, contain Nulls unless something has been explicitly entered into them. Running the query gives you the results shown in Figure 4.33. Notice that all the records having Nulls in either the Parts or Labor fields contain a Null in the result.

Figure 4.32. The Design view of a query that propagates Nulls in the query result.

Figure 4.33. The result of running a query illustrating Nulls.

The solution to this problem is constructing an expression that converts the Null values to zero. The expression looks like this:

TotalCost: NZ([Parts])+NZ([Labor])

The NZ() function determines whether the Parts field contains a Null value. If the Parts field contains a Null value, the expression converts it to a zero and includes it in the calculation; otherwise, the expression uses the field’s value in the calculation. The same expression is used to evaluate the Labor field. The result of the modified query is shown in Figure 4.34.

Figure 4.34. The query with an expression to convert Nulls to zero.

Refining Your Queries with Field, Field List, and Query Properties

You can use field and query properties to refine and control the behavior and appearance of the columns in your query and of the query itself. Here’s how:

1. Click in a field to select the field, click in a field list to select the field list, or click in the Query Design window anywhere outside a field or the field list to select the query.
2. Click Property Sheet on the Design tab of the ribbon.
3. Modify the desired property.

Field Properties: Changing the Behavior of a Field

The properties of a field in your query include the Description, Format, Input Mask, and Caption of the column. The Description property documents the use of the field and controls what appears on the status bar when the user is in that column in the query result. The Format property is the same as the Format property in a table’s field; it controls the display of the field in the query result. The Input Mask property, like its table counterpart, actually controls how the user enters and modifies data in the query result. The Caption property in the query does the same thing as a Caption property of a field: It sets the caption for the column in Datasheet view and the default label for forms and reports.

You might be wondering how the properties of the fields in a query interact with the same properties of a table. For example, how does the Caption property of a table’s field interact with the Caption property of the same field in a query? All properties of a table’s field are automatically inherited in your queries. Properties explicitly modified in the query override those same properties of a table’s fields. Any objects based on the query inherit the properties of the query, not those of the original table.

Field List Properties: Changing the Properties of the Field List

Field List properties specify attributes of each table participating in the query. The two Field List properties are Alias and Source. The Alias property is used most often when the same table is used more than once in the same query. This is done in self-joins, covered in Chapter 12. The Source property specifies a connection string or database name when you’re dealing with external tables that aren’t linked to the current database.

Query Properties: Changing the Behavior of the Overall Query

Microsoft offers many properties, shown in Figure 4.35, that allow you to affect the behavior of the overall query. Some of the properties are discussed here; the rest are covered as applicable throughout this book.

Figure 4.35. Query properties that affect the behavior of a given query.

The Description property documents what the query does. The Default View property was introduced with Access 2002. This property determines which view will display by default whenever the query is run. Datasheet is the default setting; PivotTable or PivotChart are the other two Default View settings that are available. Output All Fields shows all the fields in the query results, regardless of the contents of the Show check box in each field. Top Values lets you specify the top x number or x percent of values in the query result. The Unique Values and Unique Records properties are used to determine whether only unique values or unique records are displayed in the query’s output. (These properties are also covered in detail in Chapter 12.)

Several other more advanced properties exist. The Run Permissions property has to do with user-level security and is covered in Mastering Microsoft Office Access 2003. Source Database, Source Connect String, ODBC Timeout, and Max Records all have to do with client/server issues and are covered in Alison Balter’s Mastering Access 2002 Enterprise Development. The Record Locks property concerns multiuser issues and is also covered in Alison Balter’s Mastering Access 2002 Enterprise Development. The Recordset Type property determines whether updates can be made to the query output. By default, this is set to the Dynaset type, allowing updates to the underlying data. Filter displays a subset that you determine, rather than the full result of the query. Order By determines the sort order of the query. The Orientation property determines whether the visual layout of the fields is left-to-right or right-to-left. The Subdatasheet Name property allows you to specify the name of the table or query that will appear as a subdatasheet within the current query. After you set the Subdatasheet Name property, the Link Child Fields and Link Master Fields properties designate the fields from the child and parent tables or queries that are used to link the current query to its subdatasheet. Finally, the Subdatasheet Height property sets the maximum height for a subdatasheet, and the Subdatasheet Expanded property determines whether the subdatasheet automatically appears in an expanded state.

Building Parameter Queries When You Don’t Know the Criteria at Design Time

You, or your application’s users, might not always know the parameters for query output when designing the query. Parameter queries let you specify different criteria at runtime so that you don’t have to modify the query each time you want to change the criteria.

For example, say you have a query, like the one shown in Figure 4.36, for which you want users to specify the date range of the data they want to view each time they run the query. The following clause has been entered as the criterion for the Order Date field:

Between [Enter Starting Date] And [Enter Ending Date]

Figure 4.36. This parameter query prompts for a starting date and an ending date.

This criterion causes two dialog boxes to appear when the user runs the query. The first one, shown in Figure 4.37, prompts the user with the criterion text in the first set of brackets (refer to Figure 4.36). The text the user types is substituted for the bracketed text. A second dialog box appears, prompting the user for whatever is in the second set of brackets. The user’s response is used as the criterion for that query.

Figure 4.37. This dialog box appears when the parameter query is run.

Add a parameter to the query qryCustomerOrderSummary so that you can view only Total Price summaries within a specific range. Go to the criteria for Total Price and type Between [Please Enter Starting Value] and [Please Enter Ending Value]. This allows you to view all the records in which the total price is within a specific range. The bracketed text is replaced by actual values when the user runs the query. Click OK and run the query. You’re then prompted to enter both a starting and an ending value.

To make sure Access understands what type of data should be placed in these parameters, you must define the parameters. Do this by selecting Parameters from the Query menu to open the Query Parameters window. Another way to display the Query Parameters window is to right-click a gray area in the top half of the query design grid; then select Parameters from the ribbon while on the Design tab.

The text that appears within the brackets for each parameter must be entered in the Parameter field of the Query Parameters dialog box. The type of data in the brackets must be defined in the Data Type column. Figure 4.38 shows an example of a completed Query Parameters dialog box.

Figure 4.38. This completed Query Parameters dialog box declares two date parameters.

You can easily create parameters for as many fields as you want, and you add parameters just as you would add more criteria. For example, the query shown in Figure 4.39 has parameters for the Job Title, City, and Country/Region fields in the Employees table from the Northwind database. Notice that all the criteria are on one line of the query design grid, which means that all the parameters entered must be satisfied for the records to appear in the output. The criterion for the title is [Please Enter a Job Title]. This means that the records in the result must match the title entered when the query is run. The criterion for the City field is [Please Enter a City]. Only records with a city matching the city entered will appear in the result when the query is run. Finally, the criterion for the Country/Region field is [Please Enter a Country or Region]. This means that only records with the country or region entered when the query is run will appear in the output.

Figure 4.39. The Query Design window showing a query with parameters for three fields.

The criteria for a query can also be the result of a function; this technique is covered in Chapter 12.

Adding Smart Tags to Your Queries

You use smart tags to perform tasks that you would usually open other applications to perform. For example, you can use smart tags to schedule an appointment, email a letter, or add an Outlook contact, all based on data stored in an Access table or found in an Access query result. Taking things a step further, you can even determine the weather or get the latest news on each city that appears in the result of an Access query!

Adding a Smart Tag to a Query

Adding a smart tag to an Access query is extremely simple. Here are the steps involved:

1. Create a new query or open an existing query in Design view.
2. Select the field on which you want to base the smart tag. For example, if you want to use the smart tag to schedule an appointment, you would probably want to select the contact name field.
3. Show the Field properties for the field and click within the Smart Tags property (see Figure 4.40).

   Figure 4.40. The Field Properties window with the Smart Tags property selected.

   

4. Click the Build button (the ellipsis). The Smart Tags dialog box appears (see Figure 4.41).

   Figure 4.41. The Smart Tags dialog box allows you to select the smart tag you want to add.

   

5. Click to select the smart tag you want to add. For example, to send mail, schedule a meeting, open an existing contact, and add new contacts, select Person Name.
6. Click OK. A smart tag appears in the Smart Tags property (see Figure 4.42).

   Figure 4.42. After you click OK, a smart tag appears in the Smart Tags property.

   

Using a Smart Tag

After you have added a smart tag to a query, you will notice smart tag action buttons when you run the query (see Figure 4.43). Click the action button for a particular cell in the query result to see the actions available for that smart tag. In Figure 4.44, you can see that the Person Name smart tag has the Schedule a Meeting and Show my Calendar menu items associated with it. Figure 4.45 shows the result of selecting the Schedule a Meeting menu item.

Figure 4.43. After you add a smart tag, smart tag action buttons appear when you run the query.

Figure 4.44. Click the action button for a particular cell in the query result to see the actions available for that smart tag.

Figure 4.45. After you select the Schedule a Meeting menu item, a new appointment appears.

Creating a Pivot Table or Pivot Chart from a Query

Pivot tables and pivot charts provide great ways for you to summarize detailed data stored in your Access Database Engine and SQL Server databases. Pivot tables present your data in a spreadsheet-like format, whereas pivot charts automatically render pivot table views as line, bar, or area charts. Access 2002 introduced two views for queries: PivotTable and PivotChart.

Creating the Query to Display in PivotTable or PivotChart View

You must build a query that is appropriate to display in PivotTable or PivotChart view. Queries that lend themselves to be displayed in PivotTable or PivotChart view provide many ways for the user to manipulate his data. An example of such a query is one that contains information about country, city, salesperson, sales, and date of sale. You could determine sales by city and salesperson for each month, or you could determine sales in each country for each salesperson during the year 2006. As you can see, the idea of pivot tables is to let you slice and dice the data in any way you need to at a given moment in time.

For this example, create the following query within the Northwind database:

1. Create a new query in Design view.
2. Add the Customers, Orders, Products, Order Details, and Employees tables to the query.
3. Add the Country/Region and City fields from the Customers table.
4. Add an expression: SalesPerson:Employees![Last Name] & ", " & Employees![First Name].
5. Add the Order Date from the Orders table.
6. Add the Product Name from the Products table.
7. Add an expression: Total:[Order Details]!UnitPrice * [Order Details]!Quantity.

You now have a query on which you can base your pivot table.

Displaying the Query in PivotTable View

To switch to PivotTable view, click to select the Design tab and then open the View drop-down on the ribbon. Select PivotTable View from the drop-down. The empty PivotTable view appears, and the Access ribbon changes to include the PivotTable tab (see Figure 4.46).

Figure 4.46. When you switch to PivotTable view, the empty PivotTable view appears and the Access toolbar changes to the PivotTable toolbar.

The PivotTable Field List window also appears. You will add four types of fields to your pivot table. They include the following:

• Column fields—Often hold date fields; generally hold information with the fewest number of data items.
• Row fields—One or more fields that display data by attributes.
• Totals or Detail fields—The crosstab data itself. These are the numeric values that make up the meat of the pivot table.
• Filter fields—One or more optional fields that restrict that data appearing in the columns, rows, or both.

To display your initial pivot table, take the following steps:

1. Drag the Country/Region field so that it appears as a Row field.
2. Drag the City field so that it appears as a second Row field to the right of the Country field.
3. Drag and drop the Order Date By Month field so that it appears as a Column field.
4. Drag and drop the Total field so that it appears as a Detail field. The resulting pivot table appears as shown in Figure 4.47.

   Figure 4.47. The pivot table that appears after dragging and dropping the Total so that it appears as a Detail field.

   

Displaying Summarized Data

Including all the detail data may be much more information than you need. You can alter the query design to show only summary information. Here’s how it works:

1. Switch to Design view.
2. Click the Totals button on the Design tab of the ribbon.
3. Group by all fields except the data field and any fields that you are using for a filter.
4. Change the Total cell for any fields you are filtering by to Where.
5. Change the Total cell for the data field to Sum. The resulting query appears as shown in Figure 4.48.

   Figure 4.48. The query that appears after changing the Total cell for the data field to Sum.

   

6. Run the query to verify the design (see Figure 4.49).

   Figure 4.49. The underlying query after modifying it to summarize the data.

   

7. Return to PivotTable view.
8. To add grand totals, click any one of the Total buttons to select all three columns and then click the AutoCalc button on the Design tab of the ribbon.
9. Select the desired calculation from the drop-down.
10. With the columns still selected, right-click and select Hide Details.
11. Observe the summarized data (see Figure 4.50).

    Figure 4.50. The PivotTable view after modifying the underlying query to summarize the data.

    

Filtering Pivot Table Data

By default, Access includes all data in the pivot table. You can filter the pivot table to display only selected values for a row or column. For example, you can filter to display data for only sales in specific countries. Here’s how it works:

1. Make sure that you have expanded the pivot table display to include the detail for the data on which you want to filter (see the next section, “Using Drill-Down”).
2. Click the arrow of the field button to filter. The list contains an item for each field value (see Figure 4.51).

   Figure 4.51. The list contains an item for each field value.

   

3. Click the (All) check box to deselect all fields.
4. Click to select the field values that you want to include in the output.
5. Click OK to close the list and apply the filter.

Using Drill-Down

Generally, the initial pivot table contains an excessive amount of detail. Here’s how you can modify the amount of detail to show summary information only:

1. Switch to Design view.
2. Click the Totals button on the Design tab of the ribbon to eliminate the data grouping.
3. Return to PivotTable view. The Total or Details Fields drop zone is empty because you changed the structure of the query.
4. Click the Field List button to display the PivotTable Field List.
5. Expand the Totals item.
6. Right-click the Sum of Total item and select Delete, which clears the data from the cells.
7. Drag the Years button outside the window to remove the columns for the years, leaving an empty No Totals column.
8. Click Show Details and then drag the No Totals column outside the window. At this point, the Column Fields and Totals or Detail Fields drop zones are empty.
9. Drag the Total column to the Columns drop zone.
10. Click the Show Details button and then the Hide Details button. You will see the data expand and collapse.

Exchanging Axes

If you are viewing Year across the top and Country down the side, and you decide to view Year across the side and Country across the top, simply drag and drop their name buttons to switch the positions in which they appear. You can also easily drag new items to the pivot table from the PivotTable list at any time or remove them from the pivot table entirely.

Switching to PivotChart View

When you define a PivotTable view, you automatically generate a PivotChart view. You will see how evident this is by using the View tool to switch to PivotChart view. The results appear as shown in Figure 4.52. You can use Ribbon buttons to add legends and to modify the chart type.

Figure 4.52. The PivotChart view shows the pivot table as a chart.

Understanding Query Specifications and Limitations

Queries have a number of specifications and limitations that you should be aware of. Fortunately, you will generally not find them too restricting. They are listed in Table 4.3.

Table 4.3. Query Specifications and Limitations

Practical Examples: Building Queries Needed by the Time and Billing Application for a Computer Consulting Firm

Build a query based on tblTimeCardHours. This query gives you the total billing amount by project for a specific date range. The query’s design is shown in Figure 4.53. Notice that this is a Totals query that groups by project and totals by using the following expression:

BillAmount: Sum([BillableHours]*[BillingRate])

Figure 4.53. The design of the qryBillAmountByProject query.

The DateWorked field is used as the Where clause for the query with this criteria:

Between [Enter Start Date] And [Enter End Date]

The two parameters of the criteria are declared in the Query Parameters dialog box (see Figure 4.54). Save this query as qryBillAmountByProject.

Figure 4.54. The Query Parameters window for qryBillAmountByProject.

The second query is based on tblClients, tblProjects, and tblTimeCardHours. This query gives you the total billing amount by client for a specific date range. The query’s design is shown in Figure 4.54. This query is a Totals query that groups by the company name from the tblClients table and totals by using the following expression:

BillAmount: Sum([BillableHours]*[BillingRate])

As with the first query, the DateWorked field is used as the Where clause for the query, and the parameters are defined in the Query Parameters dialog box. Save this query as qryBillAmountByClient.

These queries are included on the sample website in a database called CHAP4.ACCDB. Of course, if this were a completed application, you would build many other queries.

Figure 4.55. The design of the qryBillAmountByClient query.

Summary

This chapter covered the foundations of perhaps the most important function of a database: getting data from the database and into a usable form. You learned about the Select query used to retrieve data from a table, how to retrieve data from multiple tables, and how to use functions in your queries to make them more powerful by synthesizing data. In later chapters, you will extend your abilities with Action queries and queries based on other queries (also known as nested queries).