• 5
• What Every Developer Needs to Know About Query Basics
• What Is a Query and When Should You Use One?
• Everything You Need to Know About Selecting Fields
• Removing a Field from the Query Grid
• Inserting a Field After the Query Is Built
• Moving a Field to a Different Location on the Query Grid
• Saving Your Query
• Ordering Your Query Result
• Sorting by More than One Field
• Refining Your Query with Criteria
• Working with Dates in Criteria
• Understanding Updatability of Query Results
• Building Queries Based on Multiple Tables
• Pitfalls of Multitable Queries
• Row Fix Up in Multitable Queries
• Creating Calculated Fields
• Getting Help from the Expression Builder
• Summarizing Data with Totals Queries
• Excluding Fields from the Output
• Nulls and Query Results
• Refining Your Queries with Field and Query Properties
• Field Properties: Changing the Behavior of a Field
• Field List Properties
• Query Properties: Changing the Behavior of the Overall Query
• Building Parameter Queries: When You Don't Know the Criteria at Design Time
• Practical Examples: Building Queries Needed By the Computer Consulting Firm Time and Billing Application
• Summary

5

What Every Developer Needs to Know About Query Basics

What Is a Query and When Should You Use One?

A Select query is a stored question about the data stored within the tables of your database. Select queries are the foundation of much of what you do within 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 simply a query of a table or tables that displays data only. It does not modify data in any way. More advanced Select queries are used to summarize data, provide 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. This chapter covers Select queries. Other types of queries are covered in Chapter 12.

Everything You Need to Know About Selecting Fields

Creating a basic query is easy because Microsoft has provided you with an intuitive drag-and-drop interface. There are two ways to start a new query in Access 95. The first way involves selecting the Query tab from the database window and then clicking New. The New Query dialog appears (see Figure 5.1). This dialog enables you to select whether you want to build the query from scratch or use one of the wizards to assist you. The Simple Query Wizard walks you through the steps involved in creating a basic query. The other wizards help you create three specific types of queries: Crosstab, Find Duplicates, or Find Unmatched. If you select Design view rather than one of the wizards, the Show Table dialog appears (see Figure 5.2). This dialog enables you to select the tables or queries that supply data to your query. Access does not care whether you select tables or queries as the foundation for your queries. You can select tables or queries by double-clicking on the name of the table or query you want to add, or by clicking on the table and then clicking Add. You can select multiple tables or queries by using the Shift key to select a contiguous range of tables or the Ctrl key to select noncontiguous tables. After you have selected the desired tables or queries, click Add and then click Close. This brings you to the Query Design window shown in Figure 5.3.

Figure 5.1. Use the New Query dialog to select a wizard for the query you want to create, or choose Design to make a query on your own.

Figure 5.2. Selecting the Design view displays the available tables in your database upon which you can create a query.

Figure 5.3.The Query Design window is an easy to use (and learn) Query By Design grid.

An alternative to the method just described is to select a table from the Tables tab and then select New Query from the New Object tool drop-down on the toolbar. This is a very efficient method of starting a new query based on only one table because the Show Table dialog never appears.

You are now ready to select the fields you want to include in the query. The query shown in Figure 5.3 illustrates a query based on the tblClients table included in the CHAP5.MDB database on the sample code CD. 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 are designing. The bottom half of the window shows any fields that will be included in the query output. A field can be added to the query design grid on the bottom half of the query window in several ways:

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

[ic:example]Open the Northwind database that ships with Access. If you want to prevent the Startup form from appearing, hold down your Shift key as you open the database. Click on the Query tab and then click New. Add the Customers table to the query. Follow these steps to select the CustomerID, CompanyName, ContactName, ContactTitle, Region, and Phone fields from Customers:

1. Click on the CustomerID field.
   
   
2. Hold down your Shift key and click on the ContactTitle field. This should select the CustomerId, CompanyName, ContactName, and ContactTitle fields.
   
   
3. Scroll down the list of fields, using the vertical scrollbar, until the Region field is visible.
   
   
4. Hold down your Ctrl key and click on the Region field.
   
   
5. With the Ctrl key still held down, click on the Phone field. All six 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 grid on the bottom. All six fields should appear in the query grid. You might need to use the horizontal scrollbar to view some of the fields on the right.

The easiest way to run a query is to select the Run button from the toolbar (which looks like an exclamation point). You can click on the Query View button on the toolbar to run a query, but this method works only for Select queries, not for Action queries. The Query View button has a special meaning for Action queries (see Chapter 12, "Advanced Query Concepts"). Clicking Run is preferable because you don't have to worry about what type of query you are running. After running a Select query, you should see what looks like a datasheet, with only the fields that you selected. To return to the design of the query, click on the Query View button.

Removing a Field from the Query Grid

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

1. Locate the field that you want to remove.
   
   
2. Click on the small gray button (column selector) immediately above the name of the field. The entire column of the query grid should become black (see Figure 5.4).
   
   
3. Press the Delete key or select Delete from the edit menu.
   
   

Figure 5.4. Removing a field from the query grid.

[ic:example]Let's assume that you have decided to remove the Region field from the query grid. Use the horizontal scrollbar to see the Region field on the query grid.

1. Click on the column selector located immediately above the Region field. The entire column of the query grid should become black.
   
   
2. Press the Delete key, which removes the Region field from the query 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 the new field to be inserted. If you want the new field to be inserted after the existing fields, it is easiest to double-click on the name of the field you want to add. If you prefer to insert the new field between two existing fields, it is best to click and drag the field you want to add, dropping it on the column that you want to appear to the right of the inserted column.

[ic:example]To insert the Country field between the ContactTitle and Phone fields, click and drag the Country field from the table until it is on top of the Phone field. This inserts the field in the correct place. To run the query, click Run on the toolbar.

Moving a Field to a Different Location on the Query Grid

Although the user can move a column while in the Datasheet view of a query, sometimes you want to permanently alter the position of a field in the query output. This can be done 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 setting up your queries effectively.

Follow these steps to move a single column:

1. To select a column while in Design view of the query, click on 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 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 grid.
   
   

[ic:example]Move the ContactName and ContactTitle fields so that they appear before the CompanyName field. Do this by clicking and dragging from the column selector for the ContactName field to the column selector for the ContactTitle field. 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 CompanyName field.

Moving a column in the datasheet view does not modify the underlying design of the query. If you move a column in datasheet view, subsequent reordering in the design view is not reflected in the datasheet view. In other words, design view and datasheet view are no longer synchronized, and you must reorder both by hand.

Saving Your Query

To save your query at any time, click on the Save button on the toolbar. You are prompted to name your query. Query names should begin with the tag qry so that you can easily recognize and identify them as queries. It is important to understand that when you save a query, you are saving only the definition of the query and not the actual query result.

[ic:example]Return to the design of the query. To save your work, click Save on the toolbar. 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. You generally want to order the query output. You can accomplish this using the Sort row of the query grid. To order your query result, follow these steps:

1. Click within the query grid in the Sort cell of the column that you want to sort (see Figure 5.5).
   
   
2. Use the drop-down combo box to select an ascending or descending sort.
   
   

Figure 5.5. Changing the order of the query result.

[ic:example]To sort in ascending order by the ContactTitle field, follow these steps:

1. Click in the Sort row of the query grid for the ContactTitle 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 in order by the ContactTitle field.
   
   
5. If you want to return to the design of the query, click Query 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 that you want to sort must be placed in order from left to right on the query 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, they need to be moved manually after the query is run.

[ic:example]Sort the query output by the Country field and, within country, by the ContactTitle field. Because sorting always occurs from left to right, you must place the Country field before the ContactTitle field. Therefore, you must move the Country field. The following steps are required:

1. Select the Country field from the query grid by clicking on the thin gray button above the Country column.
   
   
2. After you have selected the Country field, move your mouse back to the thin gray button and click and drag to the left of ContactTitle. A thick gray line should appear to the left of the ContactTitle field.
   
   
3. Release the mouse button.
   
   
4. Change the sort of the Country field to Ascending.
   
   
5. Run the query. The records should be in order by country and, within country, by contact title.
   
   

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 ability to limit your output by selection criteria. Access allows you to combine criteria using any of several operators to limit the criteria for one or more fields. The operators and their meanings are covered in Table 5.1.

Table 5.1. Access operators and their meanings.

Operator

Meaning

Example

Result

Criteria entered for two fields on a single line of the query grid is 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 grid are considered an Or, which means that either condition can be true for the record to be included in the query output. Consider the example in Figure 5.6. This query would output all records where the ContactTitle field begins with either Marketing or Owner, regardless of the customer ID. It outputs the records where the ContactTitle field begins with Sales only for the customers with IDs that begin with the letters M through R inclusive.

Figure 5.6. Adding criteria to a query.

[ic:example]Design a query to find all the sales agents in Brazil or France. The criteria you build should look like those in Figure 5.7.

1. Notice that the criteria for the Country field is "Brazil" Or "France" because you want both Brazil and France to appear in the query output. The criteria for the ContactTitle field is "Sales Agent". Because the criteria for the Country field and the criteria for the ContactTitle field are entered on the same line of the query 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 contact title begins with Sales. Try changing the criteria for the ContactTitle field to Sales. Notice that no records appear in the query output. This is 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 do not begin with Sales. Try changing the criteria to "Like *Sales*". Now all the Assistant Sales Agents appear.
   
   

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

Working with Dates in Criteria

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

Table 5.2. Sample date criteria.

Expression

Meaning

Example

Result

The Weekday(date, [FirstDayOfWeek]) function is built to work based upon 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. (See the online help for WeekDay().)

Figure 5.8 illustrates the use of a date function. Notice that the expression DatePart("q",[OrderDate]) is entered as the field name. The value of 2 is entered for the criteria. The expression Year([OrderDate)] is entered as another field name. The number 1995 is entered for the criteria. This query outputs all records where the order date is in the second quarter of 1995.

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

Understanding Updatability of Query Results

If you have not yet realized it, the results of your query are usually updatable. This means that if you modify the data in the query output, the data in the tables underlying the query is permanently modified.

[ic:example]Build a query based on the Customers table. Add the CustomerID, CompanyName, Address, City, and Region fields to the query grid. Run the query. Change the address of a particular customer. Make a note of the customer ID of the customer whose address you changed. Make sure that you move off the record so that the change is written to disk. Close the query and open the actual table in Datasheet view. Find the record whose address you modified. Notice that the change you made was written to the original table. This is because a query result is a dynamic set of records that maintains a link back to the original data. This occurs whether you are on a stand-alone machine or on a network.

It is extremely important that you understand the updatability of a query result; otherwise, you might mistakenly update table data without even realizing that you did so. Updatability of multitable queries is covered later in this chapter in the sections "Pitfalls of Multitable Queries" and "Auto-Lookup in Multitable Queries."

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 using queries. Fortunately, you can accomplish this quite easily using Access queries.

An example is shown in Figure 5.9. This query joins the Customers, Orders, and Order Details tables, pulling fields from each. Notice that the CustomerID and CompanyName fields are selected from the Customers table, the OrderID and OrderDate from the Orders table, and the UnitPrice and Quantity from the Order Details table. Running this query, you should see the results shown in Figure 5.10. Notice that you get a record in the result of the query for every record in the Order Details table. In other words, there are 2,155 records in the Order Details table, and that is how many records appear in the query output. By creating a multitable query, you can look at data from related tables, along with the data from the Order Details table.

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

Figure 5.10. The results of querying multiple tables.

[ic:example]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. Select the Query tab from the database window.
   
   
2. Click New.
   
   
3. Select Design view.
   
   
4. From the Show Table dialog select Customers, Orders, and Order Details by holding down the Ctrl key and clicking on each table name. Then select Add.
   
   
5. Click Close.
   
   
6. Some of the tables included in the query might be hiding below. If this is the case, scroll down with the vertical scrollbar to view any tables that aren't visible. Notice the join lines joining the tables. These are based on the relationships that have been set up in the Relationships window.
   
   
7. Select the following fields from each table:
   
   Customers: Country, City
   
   Orders: Order Date
   
   Order Details: UnitPrice, Quantity
   
   
8. Sort by Country and then City. Your completed query design should look like the one in Figure 5.11.
   
   

Figure 5.11. The query results from the example.

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

To remove a table from a query, click anywhere on the table in the top half of the query grid and press the Delete key. You can add tables to the query at any time by selecting the Show Table button from the toolbar. If you prefer, you can also click and drag tables directly from the database window to the top half of the query grid.

Pitfalls of Multitable Queries

You should be aware of some pitfalls of multitable queries. They concern updatability as well as which records you see in the query output.

It is important to remember that certain fields in a multitable query are not updatable. 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 cannot update the join field on the Many side of the relationship after you have updated data on the One side of the relationship. More importantly, the fields that are updatable, 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 the impact of your change. You are actually updating that record in the original table on the One side of the relationship; many records on the Many side of the relationship will be affected. Let's look at a specific example. Figure 5.12 shows the result of a query based on the Customers, Orders, and Order Details tables. I have changed "Alfreds Futterkiste" to "Waldo Furniture" 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 Alfreds Futterkiste have been altered (see Figure 5.13). This occurs because all the orders for Alfreds Futterkiste were actually getting their information from one record in the Customers table—the record for customer ID ALFKI. This is the record that I modified while viewing the query result.

Figure 5.12. The process of changing a record on the One side of a One-to-Many relationship. After updating the company name, all records with the same customer ID are affected.

Figure 5.13. 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 for ALFKI.

[ic:example]To get this experience firsthand, try changing the data within the City field of one of the records in the query result. Notice that the record (as well as several other records) is modified. This occurs because the City field actually represents data from the One side of the One-to-Many relationship. In other words, when you are viewing the Country and City fields for several records in the query output, the data for the fields might be originating from one record. The same goes for the Order Date field because it is also on the One side of a One-to-Many relationship. The only field in the query output that cannot be modified is TotalPrice, a calculated field. Practice modifying the data in the query result. Return to the original table and notice what data has changed.

The second pitfall regarding multitable queries is figuring out what records result from such a query. Thus far, you have learned how to build only inner joins. Join types are covered in detail in Chapter 12. For now, it is important to understand that the query output contains only customers who have orders and orders that have order detail. This means that not all the customers or orders might be listed. In Chapter 12, you learn how to build queries in which you can list all customers, regardless of whether they have orders. You 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. 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 within the parent table. Most database developers refer to this as enforced referential integrity. When a foreign key is entered on the Many side of a query, it must first exist on the One side of the query in order 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 which no customer record exists. For example, the query in Figure 5.14 is based on the Customers and Orders tables. The fields included in the query are CustomerID from the Orders table; CompanyName, Address, and City from the Customers table; and OrderID and OrderDate from the Orders table. If the CustomerID associated with an order is changed, the CompanyName, Address, and City are looked up from the Customers table and immediately displayed in the query result. Notice in Figure 5.15 how the information for Alfreds Futterkiste displays in the query result. Figure 5.16 shows that the CompanyName, Address, and City change automatically when the CustomerID is changed. Don't be confused by the combo box that is used to select the customer ID. This is a result of Access's auto-lookup feature, which is covered in Chapter 3. The customer ID associated with a particular order is actually being modified in the query. If a new record is added to the query, the customer information is filled in as soon as the customer ID associated with the order is selected.

Figure 5.14. This query illustrates the use of Auto Fix Up in a query with multiple tables.

Figure 5.15. The query result before the selection of another customer ID.

Figure 5.16. 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 should not be included in your database. You can output the results of calculations by building those calculations into your queries. 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 desired calculations. In certain cases, this can serve to improve performance. This topic is covered further in Chapter 12.

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

Left([FirstName],1) & "." & Left([LastName],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

[UnitPrice]*[Quantity]

This calculation would simply take the UnitPrice field and multiply it by the Quantity field. In both cases, the resulting expression would be named automatically by Access. For example, as shown in Figure 5.17, the calculation resulting from the concatenation of the first and last initials is Expr1. To give the expression a name such as Initials, you must enter the expression as follows:

Initials:Left([FirstName],1) & "." & Left([LastName],1) & "."

The text preceding the colon is the name of the expression—in this case, Initials.

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

[ic:example]Add a calculation to show the unit price multiplied by the quantity. Follow these steps to accomplish this task:

1. Scroll to the right on the query grid until you can see a blank column.
   
   
2. Click in the Field row for the new column.
   
   
3. Type TotalPrice:UnitPrice*Quantity. If you want to see more easily what you are typing, press Shift+F2 (Zoom). The dialog shown in Figure 5.18 appears.
   
   

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

1. Click OK to close the Zoom window.
   
   
2. Run the query. The total sales amount should appear in the far right column of the query output. The query output should look like the output in Figure 5.19.
   
   

Figure 5.19. The result of the total sales calculation.

You can enter any valid expression in the Field row of your query grid. Notice that field names included in an expression are automatically surrounded by square brackets. This occurs automatically unless your field name contains spaces. If a field name includes any spaces, you must enclose the field name in brackets; otherwise, your query won't run properly—which is just one of the many reasons why field and table names should not contain spaces.

Getting Help from the Expression Builder

The Expression Builder is a tool that can be of great help in the process of building expressions within your queries, as well as in many other situations in Access. To invoke the Expression Builder, click in the Field cell of your query grid and then click Builder on the toolbar. The Expression Builder appears (see Figure 5.20). Notice that the Expression Builder is divided into three columns. The first column shows the objects contained within the database. After selecting an element in the leftmost column, select the elements that you want to paste from the middle and rightmost columns.

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

The example in Figure 5.21 shows Functions selected in the leftmost column. Within Functions, both user-defined and built-in functions are listed. The Functions object is expanded with Built-In Functions selected. In the center column, Date/Time is selected. After selecting Date/Time, all the built-in date and time functions appear in the rightmost column. If you double-click on 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 place markers with your own values. If you need more assistance, you can invoke help on the selected function and learn more about the required parameters. In Figure 5.22, two parameters are filled in: the interval and the name of the field being evaluated. After clicking OK, the expression is placed in the Field cell of the query.

Figure 5.21. The Expression Builder with the DatePart function selected.

Figure 5.22. A function pasted by Expression Builder.

Summarizing Data with Totals Queries

Totals queries enable you to easily summarize numeric data. Totals queries can be used to calculate the Sum, Average, Count, Minimum, Maximum, and other types of summary calculations for the data included in your query result. They enable you to 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 (see Figure 5.23), or you could output the total sales by country and city (see Figure 5.24). You could also calculate the total, average, minimum, and maximum sales amounts for all customers in the United States. The possibilities are endless.

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

Figure 5.24. Total sales by country and city.

The following steps are involved in creating a Totals query:

1. Add to the query grid the fields or expressions that you want to summarize. It is important that you add the fields in the order in which you want them grouped. For example, Figure 5.25 shows a query grouped by Country and then City.
   
   
2. Click Totals on the toolbar or select View|Totals. This adds a Total row to the query. Each field in the query has Group By in the total row.
   
   
3. Click in the Total row on the design grid.
   
   
4. Open the combo box and select the desired calculation (see Figure 5.25).
   
   
5. Leave Group By in the Total cell of any fields that you want to group by (see Figure 5.25). Place the fields in the order in which you want to see them displayed for readibility.
   
   

Figure 5.25. Selecting the type of calculation from a drop-down.

1. Add the desired criteria to the query.
   
   

Figure 5.26 shows the design of a query that finds the total, average, maximum, and number of sales by country. Figure 5.27 shows the results of running the query. As you can see, Totals queries can provide you with extremely valuable information.

Figure 5.26. A query that finds the average, minimum, maximum, and number of sales by country.

Figure 5.27. The result of running a query with numerous aggregate functions.

If you save this query and reopen it, you notice 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([UnitPrice]*[Quantity])

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

AverageSales: Avg([UnitPrice]*[Quantity])

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

[ic:example]Modify the query to show the total sales by country, city, and order date. Before you continue, save your query as qryCustomerOrderInfo. Close the query. With the Query tab of the database window visible, click qryCustomerOrderInfo. Select Copy from the toolbar and then select Paste. Access should prompt you for the name of the new query. Type qryCustomerOrderSummary and click OK. With qryCustomerOrderSummary selected, click on the Design command button. Delete both the UnitPrice and Quantity fields from the query output. To turn your query into a Totals query, follow these steps:

1. Click Totals on the toolbar. Notice that an extra line is added to the query grid. This line is called the Total line. Notice that this line says Group By for all fields.
   
   
2. You want to group by country, city, and order date but total by the total price (the calculated field). Click the Total row for the TotalPrice field. Use the drop-down to select Sum (see Figure 5.25).
   
   
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 design of the query and remove the order date from the query grid.
   
   
5. Rerun the query. Notice that now you are summarizing the query by country and city.
   
   
6. Change the Total row to Avg. Now you are seeing the average price times quantity for each combination of country and city. Change it back to Sum and save the query.
   
   

As you see, totals queries are extremely powerful and flexible. Their output is not editable, 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 whether you are 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 do not want to appear in the query output. This is often the case with columns used solely for criteria. Figure 5.28 shows an example. If this query were run as is, you would get the total and average sales grouped by both country and order date. In this case, you want to group only by country. The order date is to be used only for criteria. Therefore, it is necessary to set the Total row of the query to Where, as shown in Figure 5.29. The column used in the Where has been excluded from the query result.

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

Figure 5.29. The Total row of the query is set to Where.

Nulls and Query Results

Null values within the fields in your table can dramatically affect query results. A field contains a null value when no value has yet been stored in the field. A null value is different from a zero or a zero-length string. As discussed in Chapter 3, a zero-length string is entered 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 containing calculations. By default, when a multitable query is built, only records that have non-null values on the Many side of the relationship appear in the query result (discussed earlier in this chapter, in the "Pitfalls of Multitable Queries" section).

Null values can also affect the result of aggregate queries. For example, if you perform a count on a field containing null values, only records containing non-null values in that field are included in the count. If you want to get an accurate count, it is best to perform the count on a Primary Key field or some other field that cannot contain null values.

Probably the most insidious problem with nulls occurs when they are included in calculations. A null value, when included in a calculation containing a numeric operator (+, -, /, *, and so on), results in a null value. The query shown in Figure 5.30 offers an example. Notice that the query includes a calculation that adds the values in both the Parts and Labor fields. These fields have been set to have no default value and will, therefore, contain nulls unless something has been explicitly entered into them. Running the query gives you the results shown in Figure 5.31. Notice that all the records containing nulls in either the Parts or Labor fields contain a null in the result.

Figure 5.30. The Design View of a query that propagates Nulls in the query result.

Figure 5.31. The result of running a query illustrating nulls.

The solution to this problem lies in construction of an expression that converts the null values to zero. The expression looks like this:

TotalCost: IIf(IsNull([Parts]),0,[Parts])+IIf(IsNull([Labor]),0,[Labor])

The Immediate If statement (IIf) is used along with the IsNull function to determine whether the Parts field contains a null value. If the Parts field contains a null value, it is converted to a zero and is included in the calculation; otherwise, the field's value is used in the calculation. The same expression is used to evaluate the Labor field. The result of the modified query is shown in Figure 5.32.

Figure 5.32. The query with an expression to eliminate nulls.

Nulls really cause trouble when the results of one query containing nulls are used in another query. A snowball effect occurs. It is easy to miss the problem and output reports with inaccurate results. Using the IsNull() function eliminates this kind of problem. You can use the IsNull function to replace the null values with zeros or zero-length strings. Be careful when doing this, because it might affect other parts of your query that utilize this value for another calculation. Also, be sure to use any function in a query on the top level of the query tree only, because functions at lower levels might hinder query performance.

Refining Your Queries with Field and Query Properties

Field and query properties can be used to refine and control the behavior and appearance of the columns within your query and of the query itself. Here's how:

1. Click within a field to select it, click within a field list to select a field, or click in the Query Design window anywhere outside of a field or the field list to select the query.
   
   
2. Click Properties on the toolbar.
   
   
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 is used for documentation and to control what appears on the status bar when the user is within that column in the query result. The Format property is the same as the Format property within a field in a table. It controls the display of the field in the query result. Just like its table counterpart, the Input Mask property actually controls how data is entered and modified within the query result. Just as the Caption property of a field sets the caption for the column in Datasheet view and the default label for forms and reports, the Caption property in the query does the same thing.

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 field in a table interact with the Caption property of the same field in a query? All properties of a field in a table are automatically inherited into your queries. Properties explicitly modified in the query override those same properties of the fields in the table. Any objects based on the query inherit the properties of the query, not of the original table.

Field List Properties

Field List properties are used to 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, which are covered in Chapter 12. The Source property is used to specify a connection string or database name when you are dealing with external tables that are not linked to the current database.

Query Properties: Changing the Behavior of the Overall Query

Microsoft has provided many properties that allow you to affect the behavior of the overall query. They are shown in Figure 5.33. Some of the properties are discussed here. The rest are covered as applicable throughout this book.

Figure 5.33. Query properties that affect the behavior of the qryCustomer query.

The Description property is used to document what the query does. Top Values lets you specify the top x number or x percent of values in the query result. Output all Fields shows all the fields in the query results, regardless of the contents of the Show check box on each field. 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 Unique Values and Unique Records properties are used to determine whether only unique values or unique records are displayed in the output of the query. These properties are also covered in detail in Chapter 12. The Run Permissions property has to do with security and is covered in Chapter 31, "Complex Security Issues." Source Database, Source Connect String, and ODBC Timeout all have to do with client/server issues and are covered in Chapter 20, "Client/Server Techniques." The Record Locks property concerns multiuser issues and is covered in Chapter 18, "Developing for a Multiuser Environment."

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

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

An example would be a query where you want users to specify the date range that they want to view each time that they run the query. An example is shown in Figure 5.34. The following clause has been entered as the criteria for the OrderDate field:

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

This criteria causes two dialog boxes to appear when the query is run. The first dialog, shown in Figure 5.35, prompts the user with the text contained within the first set of brackets. The text that the user types is substituted for the bracketed text. A second dialog appears, prompting the user for whatever is within the second set of brackets. The user's responses are used as criteria for the query.

Figure 5.34. This Parameter query prompts for a starting date and an ending date.

Figure 5.35. This dialog appears when the Parameter query is run.

[ic:example]Add a parameter to the query qryCustomerOrderSummary so that you can view only TotalPrice summaries that are within a specific range. Go to the criteria for TotalPrice and type Between [Please Enter Starting Value] and [Please Enter Ending Value]. This allows you to view all the records where the total price is within a specific range. The bracketed text is replaced by actual values when the query is run. Click OK and run the query. You are prompted to enter both a starting and an ending value.

So that Access understands what type of data is to be placed in these parameters, you must define the parameters. Do this by selecting Parameters from the Query menu. The Parameters window appears. Another way to display the Parameters window is to click with the right mouse button on a gray area on the top half of the query grid, and then select Parameters from the context-sensitive pop-up menu.

The text typed within the brackets for each parameter must be typed exactly as it appears within the brackets to define each parameter. The type of data that is contained in the brackets must be defined in the Data Type column. Figure 5.36 shows an example of a completed Parameter window.

Figure 5.36. This completed Parameter window declares two date parameters.

Parameter queries provide significant flexibility. They enable the user to enter specific criteria at runtime. What is typed in the Parameters window must exactly match what is typed within the brackets; otherwise, Access prompts with additional dialog boxes.

You can add as many parameters as you like to a query, but the user might become bothered if too many dialog boxes appear. Instead, build a custom form that feeds the parameter query. This technique is covered in Chapter 12.

Practical Examples: Building Queries Needed By the Computer Consulting Firm Time and Billing Application

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

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

Figure 5.37. The design of the qryBillAmountByProject query.

The DateWorked field is used as the Where clause for the query. The criteria for the Where clause is

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

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

Figure 5.38. The 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 design of the query is shown in Figure 5.39. This query is a Totals query that groups by the company name from the tblClients table and totals using the following expression:

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

Figure 5.39. The design of the qryBillAmountByClient query.

As with the first query, the DateWorked fields is used as the Where clause for the query. The parameters are defined in the Parameters window. Save this query as qryBillAmountByClient.

These queries are included on the sample CD in a database called CHAP5.MDB. You will build many other queries as part of the computer consulting firm time and billing system.

Summary

This chapter covered the foundations of perhaps the most important function of a database: getting data from the database in a usable form. You learned about the Select query that is 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).