Totals
QueriesNulls
and Query ResultsAlthough 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.
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.
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.
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.
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:
You can double-click the asterisk to include all fields within the table in the query result. Although this “trick” is handy, in that changes to the table structure magically affect the query’s output, I believe that it is dangerous. When the asterisk is selected, all table fields are included in the query result, regardless of whether they are needed. Including all these fields can cause major performance problems in a LAN, WAN, or client/server application.
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:
ID
field.Job Title
field. This should select the ID
, Company
, First Name
, Last Name
, E-mail Address
, and Job Title
fields.Country/Region
field is visible.Home Phone
field.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.
The easiest way to run a query is to click the Run button on the ribbon (which looks like an exclamation point). You can click the View button to run a query, but this method works only for Select
queries, not for Action queries. The View button has a special meaning for Action queries (explained in Chapter 12). Clicking Run is preferable because you don’t have to worry about what type of query you’re running. After running a Select
query, you should see what looks like a datasheet, with only the fields you selected. To return to the query’s design, click the View button.
Introduced with Access 2002 are shortcut keys that allow you to easily toggle between the various query views: Ctrl+>, Ctrl+.(period), Ctrl+<, and Ctrl,(comma). Ctrl+> and Ctrl+.(period) take you to the next view; Ctrl+< and Ctrl,(comma) take you to the previous view.
To remove a field from the query design grid, follow these steps:
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:
Country/Region
field. The entire column of the query design grid should become black, and the cursor turns into a downward-pointing arrow.Country/Region
field from the query design grid.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.
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:
Follow these steps to move more than one column at a time:
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.
Moving a column in the Datasheet view doesn’t modify the query’s underlying design. If you move a column in Datasheet view, subsequent reordering in the Design view isn’t reflected in the Datasheet view. In other words, Design view and Datasheet view are no longer synchronized, and you must reorder both by hand. This actually serves as an advantage in most cases. As you will learn later, if you want to sort by the Country/Region
field and then by the Company
field, the Country/Region
field must appear to the left of the Company
field in the design of the query. If you want the Company
field to appear to the left of the Country/Region
field in the query’s result, you must make that change in Datasheet view. The fact that Access maintains the order of the columns separately in both views allows you to easily accomplish both objectives.
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
.
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:
To sort in ascending order by the Company
field, follow these steps:
Company
field.Company
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:
Country/Region
field from the query design grid by clicking the thin gray button above the Country/Region
column.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.Last Name
field from the query design grid by clicking the thin gray button above the Last Name
column.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.Country/Region
field to Ascending.Last Name
field to Ascending.First Name
field to Ascending.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
The asterisk (*) is a wildcard. Used in the example “Like Sales*
“, it will return all records that begin with Sales
and are followed by any remaining characters.
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.
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.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.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.
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.
Understanding how Access updates query results is essential; otherwise, you might mistakenly update table data without even realizing you did so. Updating multitable queries is covered later in this chapter in the sections “Pitfalls of Multitable Queries” and “Row Fix-Up in Multitable Queries.”
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:
Customers
, Orders
, and Order Details
by holding down the Ctrl key and clicking on each table name. Then select Add.Customers: Country/Region, City
Orders: Order Date
Order Details: Unit Price, Quantity
Country/Region
and then City
in ascending order. Your finished query design should look like the one in Figure 4.13.
To remove a table from a query, click anywhere on the table in the top half of the query design grid and press the Delete key. You can add tables to the query at any time by clicking the Show Table button on the ribbon. If you prefer, you can select the Navigation Pane and then click and drag tables directly from the Navigation Pane to the top half of the query design grid.
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 more information about referential integrity, refer to Chapter 3, “Relationships: Your Key to Data Integrity.”
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.
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.
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:
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).
You can enter any valid expression in the Field row of your query design grid. Notice that field names included in an expression are automatically surrounded by square brackets, unless your field name has spaces. If a field name includes any spaces, you must enclose the field name in brackets; otherwise, your query won’t run properly. This is just one of the many reasons why field and table names shouldn’t contain spaces.
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.
Totals
QueriesBy 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:
Figure 4.27. Selecting the type of calculation for the Total row from a drop-down list.
Group By
in the Total row.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.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:
Group By
for all fields.Total Price
field and use the drop-down list to select Sum (refer to Figure 4.27).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.
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.
Null
s and Query ResultsNull
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 Null
s 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 Null
s 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 Null
s in either the Parts
or Labor
fields contain a Null
in the result.
Figure 4.32. The Design view of a query that propagates Null
s in the query result.
Figure 4.33. The result of running a query illustrating Null
s.
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 Null
s to zero.
Null
s really cause trouble when the results of one query containing Null
s are used in another query; a snowball effect occurs. You can easily miss the problem and output reports with inaccurate results. Using the NZ()
function eliminates this kind of problem. You can use the NZ()
function to replace the Null
values with zeros or zero-length strings. Be careful when doing this, though, because this function might affect other parts of your query that use 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. A query tree refers to the fact that a query can be based on other queries. Placing the criteria at the top of the query tree means that, if queries are based on other queries, the criteria should be placed in the highest-level queries.
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:
If you click a field within the query design grid that has its Show check box cleared, only the query properties will display when you bring up the property sheet for that field, not the field properties. If you mark the Show check box with the property sheet open, the field properties will then display.
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.
In the case of the Input Mask
property, it is important that the Input Mask
of the query not be in conflict with the Input Mask
of the table. You can use the Input Mask
of the query to further restrict the Input Mask
of the table, but not to override it. If the query’s Input Mask
conflicts with the table’s Input Mask
, the user will not be able to enter data into the table.
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.
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.
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.
Parameter queries offer significant flexibility; they allow the user to enter specific criteria at runtime. What’s typed in the Query Parameters dialog box must exactly match what’s typed within the brackets; otherwise, Access prompts the user 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 11, “Advanced Report Techniques.”
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 an Access query is extremely simple. Here are the steps involved:
Smart Tags
property (see Figure 4.40).
Figure 4.40. The Field Properties window with the Smart Tags
property selected.
Figure 4.41. The Smart Tags dialog box allows you to select the smart tag you want to add.
Smart Tags
property (see Figure 4.42).
Figure 4.42. After you click OK, a smart tag appears in the Smart Tags
property.
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.
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.
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:
Customers
, Orders
, Products
, Order Details
, and Employees
tables to the query.Country/Region
and City
fields from the Customers
table.SalesPerson:Employees![Last Name] & ", " & Employees![First Name]
.Order Date
from the Orders
table.Product Name
from the Products
table.Total:[Order Details]!UnitPrice * [Order Details]!Quantity
.You now have a query on which you can base your pivot table.
When you are adding tables to the query, some may be joined with outer joins. You must change these joins to inner joins for the query to run properly. Chapter 12 covers the process of converting the outer joins to inner joins.
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:
To display your initial pivot table, take the following steps:
Country/Region
field so that it appears as a Row
field.City
field so that it appears as a second Row
field to the right of the Country
field.Order Date By Month
field so that it appears as a Column
field.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.
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:
Figure 4.48. The query that appears after changing the Total cell for the data field to Sum.
Figure 4.49. The underlying query after modifying it to summarize the data.
Figure 4.50. The PivotTable view after modifying the underlying query to summarize the 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:
Figure 4.51. The list contains an item for each field value.
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:
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.
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.
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
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.
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).
3.145.158.173