Chapter 4: Selecting Data with Queries
Queries are an essential part of any database application. Queries are the tools that enable you and your users to extract data from multi-ple tables, combine it in useful ways, and present it to the user as a datasheet, on a form, or as a printed report.
You may have heard the old cliché, “Queries convert data to information.” To a certain extent, this statement is true (that’s why it’s a cliché). The data contained within tables is not particularly useful because, for the most part, the data in tables appears in no particular order. Also, in a properly normalized database, important information is spread out among a number of different tables. Queries are what draw these various data sources together and present the combined information in such a way that users can actually work with the data.
In this chapter, you learn what a query is and how to create them. Using the Sales (tblSales), Contacts (tblContacts), Sales Line Items (tblSalesLineItems), and Products (tblProducts) tables, you create several types of queries for the Access Auto Auctions database.
This chapter will use the database named Chapter04.accdb. If you haven’t already copied it onto your machine from the CD, you should do so now.
Understanding Queries
A database’s primary purpose is to store and extract information. Information can be obtained from a database immediately after you enter the data or days, weeks, or even years later. Of course, retrieving information from database tables requires knowledge of how the database is set up.
For example, printed reports are often filed in a cabinet, arranged by date and by a sequence number that indicates when the report was produced. To obtain a specific report, you must know its year and sequence number. In a good filing system, you may have a cross-reference book to help you find a specific report. This book may have all reports categorized alphabetically by type of report and, perhaps, by date. Such a book can be helpful, but if you know only the report’s topic and approximate date, you still have to search through all sections of the book to find out where to get the report.
Unlike manual databases, computer databases like Microsoft Access easily obtain information to meet virtually any criteria you specify.
This is the real power of a database—the capacity to examine the data in more ways than you can imagine. Queries, by definition, ask questions about the data stored in the database. Most queries are used to drive forms, reports, and graphical representations of the data contained in a database.
What is a query?
The word query comes from the Latin word quærere, which means “to ask or inquire.” Over the years, the word query has become synonymous with quiz, challenge, inquire, or question. So, think of a query as a question or inquiry posed to the database about information contained in its tables.
A Microsoft Access query is a question that you ask about the information stored in your Access tables. You build queries with the Access query tools, and then save it as a new object in your Access database. Your query can be a simple question about data within a single table, or it can be a more complex question about information stored in several tables. After you submit the question, Microsoft Access returns only the information you requested.
Using queries this way, you ask the Access Auto Auctions database to show you only trucks that were sold in the year 2007. To see the types of trucks sold for the year 2007, you need information from three tables: tblSales, tblSalesLineItems, and tblProducts. Figure 4-1 is a typical Query Design window. Although it may look complex, it’s actually very simple and easy to understand.
After you create and run a query, Microsoft Access retrieves and displays the requested records as a datasheet. This set of records is called a recordset, which is the set of records selected by a query. As you’ve seen, a datasheet looks just like a spreadsheet, with its rows of records and columns of fields. The datasheet (of the recordset) can display many records simultaneously.
You can easily filter information from a single table using the Search and Filter capabilities of a table’s datasheet view (Filter by Selection and Filter by Form). Queries allow you to view information from a single table, or from multiple tables at the same time (as in Figure 4-1). Many database queries extract information from several tables.
Figure 4-1
A typical three-table select query. This query displays the sales date, number of trucks, and type of truck for all trucks sold in the year 2007.
Clicking the Datasheet View button on the toolbar shows six records matching the query shown in Figure 4-1. This is a relatively easy-to-design query when you understand how to use the Access query designer. This simple query has many elements that demonstrate the power of the Access query engine: sorting a result set of records, specifying multiple criteria, and even using a complex Or condition in one of those fields.
You can build very complex queries using the same query designer. Suppose, for instance, that you want to send a notice to all previous buyers of more than one car in the past year that several new cars are available for auction. This type of query requires getting information from four tables: tblContacts, tblSales, tblSalesLineItems, and tblProducts. The majority of the information you need is in tblContacts and tblProducts.
In this case, you want Access to show you a datasheet of all Contact names and addresses where they have met your specified criteria (two or more cars purchased in 2007). In this case, Access retrieves customer names and cities from the tblContacts table and then obtains the number of cars from the tblProducts table, and the year of sale from the tblSales table. Figure 4-2 shows this relatively complex query. Access then takes the information that’s common to your criteria, combines it, and displays all the information in a single datasheet. This datasheet is the result of a query that draws from the tblContacts, tblSales, tblSalesLineItems, and tblProducts tables. The database query performed the work of assembling all the information for you. Figure 4-3 shows the resulting datasheet.
Figure 4-2
A more complex query returning customers that purchased more than one car in 2007.
Figure 4-3
The resulting datasheet of the query shown in Figure 4-2.
Types of queries
Access supports many different types of queries, grouped into six basic categories:
• Select: These are the most common types of query. As its name implies, the select query selects information from one or more tables, creating a recordset. Generally speaking, the data returned by select query is updatable and is often used to populate forms and reports.
• Total: These are special type of select queries. Total queries provide sums or other calculations (such as count) from the records returned by a select query. Selecting this type of query adds a Total row in the QBE (Query by Example) grid.
• Action: These queries enable you to create new tables (Make Tables) or change data (delete, update, and append) in existing tables. Action queries affect many records as a single operation.
• Crosstab: These queries can display summary data in cross-tabular form like a spreadsheet, with row and column headings based on fields in the table. The individual cells of the recordset are computed or calculated from data in the underlying tables.
• SQL: There are three SQL (Structured Query Language) query types—Union, Pass-Through, and Data Definition. These queries are used for advanced database manipulation, such as working with client/server SQL databases like SQL Server or Oracle. You create these queries by writing specific SQL statements.
• Top(n): Top(n) queries enable you to specify a number or percentage of records you want returned from any type (select, total, and so on) of query.
Query capabilities
Queries are flexible. They provide the capability of looking at your data in virtually any way you can imagine. Most database systems are continually evolving and changing over time. Very often, the original purpose of a database is very different from its current use.
Here is a sampling of what you can do with Access queries:
• Choose tables: Obtain information from a single table or from many tables that are related by some common data. Suppose you’re interested in seeing the customer name along with the items purchased by each type of customer. When using several tables, Access returns the data as a combined single datasheet.
• Choose fields: Specify which fields from each table you want to see in the recordset. For example, you can select the customer name, zip code, sales date, and invoice number from tblContacts and tblSales.
• Choose records: Select records based on selection criteria. For example, you may want to see records for only sellers in tblContacts.
• Sort records: You may want to sort records in a specific order. For example, you may need to see customers sorted by last name and first name.
• Perform calculations: Use queries to perform calculations on data. Perform calculations such as averaging, totaling, or counting fields and records.
• Create tables: Create a new table based on data returned by a query.
• Base forms and reports on queries: The recordset you create from a query may have just the right fields and data needed for a report or form. Basing a form or report on a query means that, every time you print the report or open the form, you will see the most current information in the tables.
• Create graphs based on queries: Create graphs from data returned by a query.
• Use a query as a source of data for other queries (subquery): Create additional queries based on records returned by another query. This is very useful for performing ad hoc queries, where you may repeatedly make small changes to the criteria. In this case, the second query is used to change the criteria while the first query and its data remain intact.
• Make changes to tables: Access queries can obtain information from a wide range of sources. You can retrieve data stored in dBASE, Paradox, Btrieve, and Microsoft SQL Server databases, as well as Excel spreadsheets, text files, and other data sources.
How recordsets work
Access takes the records that result from a query and displays them in a datasheet. The set of records is commonly called (oddly enough) a recordset. Physically, a recordset looks much like a table. A recordset is, in fact, a dynamic set of records. The set of records returned by a query is not stored within the database, unless you have directed access to build a table from those records.
When you close a query, the query’s recordset is gone; it no longer exists. Even though the recordset itself no longer exists, the data that formed the recordset remains stored in the underlying tables.
When you run a query, Access places the returned records into a recordset. When you save the query, only the structure of the query is saved, and not the returned records. Consider these benefits of not saving the recordset to a physical table:
• A smaller amount of space on a storage device (usually a hard drive) is needed.
• The query uses updated versions of records.
Every time the query is executed, it reads the underlying tables and re-creates the recordset. Because recordsets themselves are not stored, a query automatically reflects any changes to the underlying tables made since the last time the query was executed—even in a real-time, multiuser environment.
Creating a Query
After you create your tables and place data in them, you’re ready to work with queries. To begin a query, choose the Create ribbon, and click on the Query Design button in the Other group. Access opens the query designer in response.
Figure 4-4 shows two windows. The underlying window is the Query Designer. Floating on top of the designer is the Show Table dialog box. The Show Table window is modal, which means that you must do something in the dialog box before continuing with the query. Before you continue, you add the tables required for the query. In this case, tblProducts is highlighted to be added.
The Show Table dialog box shown in Figure 4-5 displays all tables and queries in your database. Double-click on tblProducts to add it to the query design. Close the Show Table dialog box after adding tblProducts. Figure 4-5 shows tblProducts added to the query.
Figure 4-4
The Show Table dialog box in the Query Design window.
Figure 4-5
The Query Design window with tblProducts added to the Query Designer.
To add additional tables to the query, right-click on the query’s design surface and select Show Table from the shortcut menu that appears. Alternatively, drag tables from the Navigation pane on to the Query Designer’s surface.
Removing a table from the Query Designer is easy. Just right-click on the table in the Query Designer and select Remove Table from the shortcut menu.
Using the Query window
The Query window has two main views: Design view and Datasheet view. The difference between them is self-explanatory: The Design view is where you create the query, and the Datasheet view displays the records returned by the query.
The Query Design window should now look like Figure 4-5, with tblProducts displayed in the top half of the Query Design window.
The Query Design window consists of two sections:
• The table/query entry pane (top)
• The Query by Example (QBE) design grid (bottom)
The upper pane is where tables or queries and their fields are displayed. Tables and queries are displayed as small windows inside the top pane (the proper name of this window is Field List). The Field List window can be resized by clicking on the edges and dragging it to a different size.
The Query by Example (QBE) grid holds the field names involved in the query and any criteria used to select records. Each column in the QBE grid contains information about a single field from a table or query contained within the upper pane.
Navigating the Query Design window
The two window panes are separated horizontally by a pane-resizing bar (see Figure 4-5). Move the bar up or down to change the relative sizes of the upper and lower panes.
Switch between the upper and lower panes by clicking the desired pane or by pressing F6 to switch panes. Each pane has horizontal and vertical scrollbars to help you move around.
You actually build the query by dragging fields from the upper pane to the QBE grid.
Using the Query Design ribbon
The Query Design ribbon (shown in Figure 4-6) contains many different buttons specific to building and working with queries.
Figure 4-6
The Query Design ribbon
This ribbon has many buttons that can be helpful when designing your queries. Although each button is explained as it is used in the chapters of this book, here are the main buttons:
• View: Switches between the Datasheet view and Design view. The View drop-down control also enables you to display the underlying SQL statement behind the query (more on this later).
• Save (in the Quick Access Toolbar): Saves the query. It is a good idea to save your work often, especially when creating complex queries.
• Make Table, Append, Update, and Crosstab: Specify the type of query you are building.
• Run: Runs the query. Displays a select query’s datasheet, serving the same function as the View button. However, when working with action queries, it actually performs the operations specified by the query.
• Show Table: Opens the Show Table dialog box.
The remaining buttons are used for more creating more advanced queries, printing the contents of the query, and displaying a query’s property sheet.
Using the QBE grid of the Query Design window
As you saw earlier, Figure 4-5 displays an empty QBE grid, which has six labeled rows:
• Field: Where field names are entered or added.
• Table: Shows the table the field is from (useful in queries with multiple tables).
• Sort: Enables sorting instructions for the query.
• Show: Determines whether to display the field in the returned recordset.
• Criteria: Criteria that filter the returned records.
• or: This row is the first of a number of rows to which you can add multiple query criteria.
You learn more about these rows as you create queries in this chapter.
Selecting Fields
There are several ways to add fields to a query. You can add fields one at a time, select and add multiple fields, or select and add all fields. You use your keyboard or mouse to add fields.
Adding a single field
You add a single field in several ways. One method is to double-click the field name in the Field List (also called a Table window); the field name immediately appears in the first available column in the QEB pane. Alternatively, drag a field from a table in the top portion of the query designer, and drop it on a column in the QBE grid. Dropping a field between two other fields in the QBE grid pushes other fields to the right.
Another way to add fields to the QBE grid is to click an empty Field cell in the QBE grid, and select the field name from the drop-down list in the cell, or type the field’s name into the cell. Figure 4-7 shows selecting the Cost field from the drop-down list. Once selected, simply move to the next field cell and select the next field you want to see in the query.
Figure 4-7
Adding fields in the QBE grid. Clicking the down arrow reveals a drop-down list from which you select a field.
You’ll find a similar list of all the tables in the query in a drop-down list in the Table row of the QBE grid.
After selecting the fields, run the query by clicking the Datasheet button or the Run button on the ribbon. Click the Design View button on the ribbon to return to the design window.
Adding multiple fields
You add multiple fields in a single action by selecting the fields from the Field List and dragging them to the QBE grid. The selected fields do not have to be contiguous (one after the other). Hold down the Shift key while selecting multiple fields. Figure 4-8 illustrates the process of adding multiple fields.
Figure 4-8
Selecting several fields to move to the QBE grid.
The fields are added to the QBE grid in the order in which they occur in the table.
You can also add all the fields in the table by clicking on the Field List’s header (where it says tblProducts in Figure 4-9) to highlight all the fields in the table. Then drag the highlighted fields to the QBE grid.
Alternatively, click and drag the asterisk (*) from the Field List to the QBE grid. Although this action does not add all the fields to the QBE grid, the asterisk directs Access to include all fields in the table in the query.
Figure 4-9
Adding the asterisk to the QBE grid selects all fields in the table.
Unlike selecting all the fields, the asterisk places a reference to all the fields in a single column. When you drag multiple columns, as in the preceding example, you drag names to the QBE grid. If you later change the design of the table, you also have to change the design of the query. The advantage of using the asterisk for selecting all fields is that changes to the underlying tables don’t require changes to the query. The asterisk means to select all fields in the table, regardless of the field names or changes in the number of fields in the table.
The downside of using the asterisk to specify all fields in a table is that the query, as instructed, returns all the fields in a table, whether or not every field is used on a form or report.
Displaying the Recordset
Click the Run button or the Datasheet button to view the query’s results (see Figure 4-10).
Figure 4-10
The datasheet view of the query
Working with records in Datasheet view is covered in detail in Chapter 6. As you can see in that chapter, filtering, sorting, rearranging, and searching within a datasheet is quite easy. Our simple select query did not transform the data in any way, so the data shown in Figure 4-10 is completely editable. We can modify existing data, delete rows, and even add new records to this data set, if we want.
When you’re working with data in the datasheet, all the table and field properties defined at the table level are in effect. Therefore, validation rules, default values, and other properties assert themselves even though the datasheet is the result of a query.
Earlier versions of Access referred to an updatable datasheet as a Dynaset. This expression emphasized the fact that the datasheet was dynamically linked to its underlying data sources. However, this expression has fallen by the wayside because, very often the data in a query’s datasheet is not updatable. For instance, transforming the data in any way, such as combining first and last names as a single field, makes the datasheet non-updatable. You’ll see data transformations later in this chapter and in many other chapters in this book.
At any time, clicking the Design View button on the ribbon returns you to Query Design mode.
Working with Fields
There are times when you want to work with the fields you’ve already selected—rearranging their order, inserting a new field, or deleting an existing field. You may even want to add a field to the QBE grid without showing it in the datasheet. Adding a field without showing it enables you to sort on the hidden field, or to use the hidden field as criteria.
Selecting a field in the QBE grid
Before you can move a field’s position, you must first select it. To select it, you will work with the field selector row.
The field selector row is the narrow gray area at the top of each column in the QBE grid at the bottom of the Query Designer. Recall that each column represents a field. To select the Category field, move the mouse pointer until a small selection arrow (in this case, a dark downward arrow) is visible in the selector row and then click the column. Figure 4-11 shows the selection arrow above the Category column just before it is selected.
Figure 4-11
Selecting a column in the QBE grid. The pointer changes to a downward-pointing arrow when you move over the selection row.
Select multiple contiguous fields by clicking the first field you wish to select and then dragging across the field selector bars of the other fields.
Changing field order
The left-to-right order in which fields appear in the QBE grid determines the order in which they appear in Datasheet view. You may want to move the fields in the QBE grid to achieve a new sequence of fields in the query’s results. With the fields selected, you can move the fields on the QBE design by simply dragging them to a new position.
Left-click on a field’s selector bar, and, while holding down the left mouse button, drag the field into a new position in the QBE grid.
Figure 4-12 shows the Category field highlighted. As you move the selector field to the left, the column separator between the fields ProductID and Description changes (gets wider) to show you where Category will go.
Figure 4-12
Moving the Category field to between ProductID and Description. Notice the QBE field icon below the arrow near the Description column.
Resizing columns in the QBE grid
The QBE grid generally shows five or six fields in the viewable area of your screen. The remaining fields are viewed by moving the horizontal scroll bar at the bottom of the window.
There are times that you may want to shrink some fields to be able to see more columns in the QBE grid. You adjust the column width to make them smaller (or larger) by moving the mouse pointer to the margin between two fields, and dragging the column resizer left or right (see Figure 4-13). An easier way to resize columns in the QBE grid is to double-click on the line dividing two columns in the grid. Access “auto-sizes” the column to fit the data displayed in the column.
Figure 4-13
Resizing columns in the QBE grid
Removing a field
Remove a field from the QBE grid by selecting the field (or fields) and pressing the Delete key. You can also right-click on a field’s selector and choose Cut from the shortcut menu.
Inserting a field
Insert new fields in the QBE grid by dragging a field from a Field List above the QBE grid and dropping it onto a column in the QBE grid. The new column is inserted to the left of the column you dropped the field on. Double-clicking a field in a Field List adds the new column at the far right position in the QBE grid.
Providing an alias for the field name
To make the query datasheet easier to read, you can provide aliases for the fields in your query. An alias becomes the field’s heading in the query’s datasheet, but does not affect the field’s name or how the data is stored and used by Access. Aliases are sometimes useful to help users better understand the data returned by a query. As you will see in Chapter 18, data in queries are often transformed by performing simple operations such as combining a person’s first and last name as a single field. In these situations, aliases are very useful because they provide an easily recognizable reference to the transformed data.
To follow along with this example, create a query using the fields from the tblProducts as shown in Figure 4-12. Follow these steps to establish an alias for the ProductID and Description fields:
1. Click to the left of the P of the ProductID column in the top row of the QBE grid.
2. Type Product-Number followed by a colon (:) to the left of ProductID.
3. Click to the left of the D in the Description column and enter Product Description: to the left of the field name.
When you run the query, the aliases you created appear as the column headings. Figure 4-14 shows both the query in Design view and the query’s datasheet. Notice that the ProductID and Description column sport their new aliases instead of their respective field names.
Figure 4-14
Aliases can be useful to help users understand data.
Aliases should be used with caution, however. Because an alias masks the name of the field underlying a datasheet, it’s easy to become confused which column headings are aliases and which are field names. It is a complete waste of time looking for a field named ProductDescription, based on a datasheet column heading. It would be nice if Access somehow distinguished between aliases and field names in Datasheet view, but the only way to know for sure is to examine the query’s design.
Showing a field
While performing queries, you may want to show only some of the fields in the QBE grid. Suppose, for example, you’ve chosen ContactType, FirstName, LastName, Address, City, and State. Then you decide that you want to temporarily look at the same data, without the ContactType and Address fields. You could start a new query adding all of the fields except Address and ContactType, or you can simply “turn off” the Address and ContactType fields by unchecking the check box in the Show row of each of these columns (see Figure 4-15).
Figure 4-15
The Show check box is unchecked for the Address and ContactType fields.
By default, every field you add to the QBE grid has its Show check box selected.
Another common reason to hide a field in the query is because the field is used for searching or sorting, but its value is not needed in the query. For instance, consider a query involving the invoices from the Access Auto Auctions database. For a number of reasons, the users may want to see the invoices sorted by the order date, even though the actual order date is irrelevant for this particular purpose. Simply include the OrderDate field in the QBE grid, set the sort order for the OrderDate field, and uncheck its Show box. Access sorts the data by the OrderDate field even though the field is not shown in the query’s results.
If you save a query that has an unused field (its Show box is unchecked and no criteria or sort order is applied to the field), Access eliminates the field from the query. The next time you open the query, the field will not be included in the query’s design.
Changing the Sort Order
When viewing a recordset, you often want to display the data in a sorted order. You may want to sort the recordset to make it easier to analyze the data (for example, to look at all the tblProducts sorted by category).
Sorting places the records in alphabetical or numeric order. The sort order can be ascending (0 to 9 and A to Z) or descending (9 to 0 and Z to A). You can sort on a single field or multiple fields.
You input sorting directions in the Sort row in the QBE grid. To specify a sort order on a particular field (such as LastName), perform these steps:
1. Position the cursor in the Sort cell in the LastName column.
2. Click the drop-down list that appears in the cell, and select the sort order (Ascending or Descending) you want to apply.
Figure 4-16 shows the QBE grid with ascending sorts specified for the LastName and FirstName fields. Notice that the LastName field is still showing the sort options available. Also notice that the word Ascending is being selected in the field’s Sort: cell.
Figure 4-16
An ascending sort has been specified for the LastName and FirstName fields.
You cannot sort on a Memo or an OLE object field.
The left-to-right order in which fields appear in the QBE grid is important when sorting on more than one field. Not only to the fields appear in the datasheet in left-to-right order, they are sorted in the same order (this is known as sort order precedence). The leftmost field containing sort criteria is sorted first, the first field to the right containing sort criteria is sorted next, and so on. In the example shown in Figure 4-16, the LastName field is sorted first, and then the FirstName field.
Figure 4-17 shows the results of the query shown in Figure 4-16. Notice that the data is sorted by the values in the LastName column, and the values in the FirstName column are sorted within each name in the LastName column. This is why Ann Bond appears before John Bond in the query’s data.
Figure 4-17
The order of the fields is critical when sorting on multiple fields.
Displaying Only Selected Records
So far, you’ve been working with all the records of the tblContacts and tblProducts tables. Most often users want to work only with records conforming to some criteria. Otherwise, too many records may be returned by a query, causing serious performance issues. For example, you may want to look only at contacts that are buyers and not sellers. Access makes it easy for you to specify a query’s criteria.
Understanding selection criteria
Selection criteria are simply filtering rules applied to data as it is extracted from the database. Selection criteria instruct Access which records you want to look at in the recordset. A typical criterion might be “all sellers,” or “only those vehicles that are not trucks,” or “cars with retail prices greater than $45,000.”
Selection criteria limit the records returned by a query. Selection criteria aid the user by selecting only the records a user wants to see, and ignoring all the others.
You specify criteria in the Criteria row of the QBE grid. You designate criteria as an expression. The expression can be as a simple example (like “Trucks” or “Not Trucks”) or can take the form of complex expressions using built-in Access functions.
Entering simple string criteria
Character-type criteria are applied to Text-type fields. Most often, you will enter an example of the text you want to retrieve. Here is a small example that returns only product records where the product type is “Cars”:
1. Add tblProducts and choose the Description, Category, and Cost fields.
2. Type CARS into the Criteria cell under the Category column.
3. Run the query.
Only cars are displayed—in this case, 25 records (see Figure 4-18). Observe that you did not enter an equal sign or place quotes around the sample text, yet Access added double quotes around the value. Access, unlike many other database systems, automatically makes assumptions about what you want.
Figure 4-18
Specifying character criteria. In this case, because you want to see only cars, you enter “CARS” as the criteria.
Figure 4-18 shows both the query design and the datasheet resulting from the query. This figure also illustrates one reason you may wish to hide a column in a query. There’s no point in displaying “Cars” and every row in the third column. In fact, because this query only returns information about cars, the user can very well assume that every record references a car and there’s no need to display a product category in the query. Unchecking the Category’s Show box in the queries design would remove the Category column from the datasheet, making the data easier to understand.
You could enter the criteria expression in any of these other ways:
• CARS
• = CARS
• “CARS”
• = “Cars”
By default, Access is not case sensitive, so any form of the word cars works just as well as this query’s criteria.
Figure 4-18 is an excellent example for demonstrating the options for various types of simple character criteria. You could just as well enter “Not Cars” in the criteria column, to return all products that are not cars (trucks, vans, and so on).
Generally, when dealing with character data, you enter equalities, inequalities, or a list of values that are acceptable.
This capability is a powerful tool. Consider that you have only to supply an example and Access not only interprets it but also uses it to create the query recordset. This is exactly what Query by Example means: You enter an example and let the database build a query based on the example.
To erase the criteria in the cell, select the contents and press Delete, or select the contents and Right Click Cut from the shortcut menu that appears. You can also right-click Paste to revert to the previous content (in this case, a blank cell).
Entering other simple criteria
You can also specify criteria for Numeric, Date, and Yes/No fields. Simply enter the example data in the criteria field.
It is also possible to add more than one criteria to a query. For example, suppose that you want to look only at contacts who are both sellers and buyers (“BOTH” type in the ContactType field), and those contacts have been customers since January 1, 2007 (where OrigCustDate is greater or equal to January 1, 2007). This query requires criteria in both the ContactType and OrigCustDate fields. To do this, it is critical that you place both examples on the same Criteria row. Follow these steps to create this query:
1. Create a new query starting with tblContacts.
2. Add ContactType, FirstName, LastName, State, and OrigCustDate to the QBE grid.
3. Enter BOTH in the Criteria cell in the ContactType column.
4. Enter >= 01/01/07 in the Criteria cell in the OrigCustDate column.
5. Run the query.
Figure 4-19 shows how the query should look.
Access displays records of contacts that are both sellers and buyers that became customers after January 1, 2007—in this example, two contact records.
Multi-criteria queries are covered in depth in Chapter 18.
Figure 4-19
Specifying character and date criteria in the same query
Access uses comparison operators to compare Date fields to a value. These operators include less than (<), greater than (>), equal to (=), or a combination of these operators. Notice that Access automatically adds pound sign (#) delimiters around the date value. Access uses these delimiters to distinguish between date and text data. The pound signs are just like the quote marks Access added to the “Cars” criteria. Because the OrigCustDate is a DateTime field, Access understands what you want and inserts the proper delimiters for you.
Operators and precedence are covered more in Chapter 5.
Printing a Query’s Recordset
After you create your query, you can easily print all the records in the recordset. Although you can’t specify a type of report, you can print a simple matrix-type report (rows and columns) of the recordset created by your query.
You do have some flexibility when printing a recordset. If you know that the datasheet is set up just as you want, you can specify some options as you follow these steps:
1. Use the datasheet you just created for both sellers and buyers that have been customers since 01/01/2007.
2. If you are not in the Datasheet view, switch to the Query Datasheet mode by clicking the Datasheet button on the ribbon.
3. Choose File⇒Print from the Query Datasheet window’s ribbon
4. Specify the print options that you want in the Print dialog box and click OK.
The printout reflects all layout options in effect when you print the dataset. Hidden columns do not print, and gridlines print only if the Gridlines option is on. The printout reflects the specified row height and column width.
Saving a Query
Click the Save button at the top of the Access screen to save your query. Access asks you for the name of the query if this is the first time the query has been saved.
After saving the query, Access returns you to the mode you were working in. Occasionally, you will want to save and exit the query in a single operation. To do this, click the Close Window button in the upper-right corner of the Query Designer. Access always asks you to confirm saving the changes before it actually saves the query.
Adding More Than One Table to a Query
Using a query to obtain information from a single table is common; often, however, you need information from several related tables. For example, you may want to obtain a buyer’s name and vehicle type purchased by the contact. This query requires four tables: tblContacts, tblSales, tblSalesLineItems, and tblProducts.
In Chapter 2, you learned the importance of primary and foreign keys and how they link tables together. You learned how to use the Relationships window to create relationships between tables. Finally, you learned how referential integrity affects data in tables.
After you create the tables for your database and decide how the tables are related to one another, you are ready to build multiple-table queries to obtain information from several related tables. The query combines data from multiple tables and presents the data as if it existed in one large table.
The first step in creating a multiple-table query is to add the tables to the Query window:
1. Create a new query by clicking the Query Design button in the Create ribbon tab.
2. Select tblContacts, tblSales, tblSalesLineItems, and tblProducts by double-clicking each table’s name in the Show Table dialog box.
3. Click the Close button in the Show Table dialog box.
You can also add each table by highlighting the table in the list separately and clicking Add.
Figure 4-20 shows the top pane of the Query Design window with the four tables you just added. Because the relationships were set at table level, the join lines are automatically added to the query.
You can add more tables, at any time, by choosing Query⇒Show Table from the Query Design Ribbon.
Figure 4-20
The Query Design window with four tables added. Notice the join lines are already present.
Working with the Table/Query Pane
As Figure 4-20 shows, a join line connects tables in the Query Designer. The join line connects the primary key in one table to the foreign key in another table.
These lines were predrawn because you already set the relationships between the tables earlier in Chapter 4.
The join line
A join line represents the relationship between two tables in the Access database. In this example, a join line goes from tblSales to tblContacts, connecting ContactID in the tblContacts table to the Buyer field in tblSales. There are other join lines connecting the other tables in this query.
The join line is automatically created because relationships were set in the relationship builder. If Access already knows about the relationship, it adds the join line when the tables are added to a query.
If Referential Integrity is set on the relationship, Access displays a thicker line where the join line connects to the table in the Query Designer. This variation in line thickness tells you that Referential Integrity is set between the two tables. If a one-to-many relationship exists, the many-side table is indicated by an infinity symbol (∞).
Access will auto join to tables if the following conditions are met:
• Both tables have fields with the same name.
• The same-named fields are the same data type (text, numeric, and so on).
• One of the field is a primary key in its table.
Access 2007 automatically attempts to join the tables if a relationship exists. Access cannot set referential integrity on the join line.
Manipulating the Field List window
Each Field List window begins at a fixed size, which shows approximately four fields and perhaps 12 characters for each field. Each Field List is a resizable window and can be moved within the Query Designer. If there are more fields than will show in the Field List window, a scroll bar enables you to scroll through the fields in the Field List.
After a relationship is created between tables, the join line remains between the two fields. As you move through a table selecting fields, the line moves relative to the linked fields. For example, if the scroll box moves down (toward the bottom of the window) in tblContacts, the join line moves up with the customer number, eventually stopping at the top of the table window.
When you’re working with many tables, these join lines can become confusing as they cross or overlap. As you scroll through the table, the line eventually becomes visible, and the field it is linked to becomes obvious.
Moving a table
Move the Field Lists by grabbing the title bar of a Field List (where the name of the table is) with the mouse and dragging the Field List to a new location. You may want to move the Field Lists for a better working view or to clean up a confusing query diagram.
You can move and resize the Field Lists anywhere in the top pane. Access saves the arrangement when you save and close the query. Generally speaking, the Field Lists will appear in the same configuration the next time you open the query.
Removing a table
There are times when you need to remove tables from a query. Any table can be removed from the Query window. Use the mouse to select the table you want to remove in the top pane of the Query window and press the Delete key. Or right-click on the Field List and choose Removed Table from the shortcut menu.
When you delete a table, join lines to that table are deleted as well. When you delete a table, there is no warning or confirmation dialog box. The table is simply removed from the screen, along with any of the table’s fields added to the QBE grid.
Adding more tables
You may decide to add more tables to a query or you may accidentally delete a table and need to add it back. You accomplish this task by clicking on the Show Table button on the Query Setup group in the Design ribbon. The Show Table dialog box appears in response to this action.
Adding Fields from More Than One Table
You add fields from more than one table to the query in exactly the same way as when you’re working with a single table. You can add fields one at a time, multiple fields as a group, or all the fields from a table.
If you type a field name in an empty Field cell that has the same name in more than one table, Access enters the field name from the first table that it finds containing the field name.
If you select the field from the drop-down list in the Field cell, you see the name of the table first, followed by a period and the field name. For example, the ProductID in tblSalesLineItems is displayed as tblSalesLineItems.ProductID. This helps you select the right field name. Using this method, you can select a common field name from a specific table.
The easiest way to select fields is still to double-click the field names in the top half of the Query Designer. To do so, you may have to resize the Field Lists to see the fields that you want to select.
Viewing the table names
When you’re working with two or more tables, the field names in the QBE grid can become confusing. You may find yourself asking, for example, just which table the field is from.
Access automatically maintains the table name that is associated with each field displayed in the QBE grid. Figure 4-21 shows the Query Designer with the name of each table displayed under the field name in the QBE grid.
Figure 4-21
The QBE grid with table names displayed. Notice that it shows all four table names.
After you add fields to a query, you can view the returned records at any time. Figure 4-22 shows the data returned by the query in Figure 4-21.
Figure 4-22
Datasheet view of data from multiple tables. This resulting recordset, from the query, contains 84 records.
Adding multiple fields
The process of adding multiple fields in a multi-table query is identical to adding multiple fields in a single-table query. When you’re adding multiple fields from several tables, you must add them from one table at a time. The easiest way to do this is to select multiple fields and drag them together down to the QBE grid.
You can select multiple contiguous fields by clicking the first field of the list and then clicking the last field while holding down the Shift key. You can also select noncontiguous fields in the list by holding down the Ctrl key while clicking individual fields with the mouse.
Selecting the * does have one drawback: You cannot specify criteria on the asterisk column itself. You have to add an individual field from the table and enter the criterion. If you add a field for a criterion (when using the *), the query displays the field twice—once for the * field and a second time for the criterion field. Therefore, you may want to deselect the Show cell of the criterion field.
Understanding Multi-Table Query Limitations
When you create a query with multiple tables, there are limits to which fields can be edited. Generally, you can change data in a query’s recordset, and your changes are saved in the underlying tables. The main exception is a table’s primary key—a primary key value cannot be edited if referential integrity is in effect and if the field is part of a relationship.
To update a table from a query, a value in a specific record in the query must represent a single record in the underlying table. This means that you cannot update fields in a query that transforms data because most transformations group records and fields display aggregate information. Each field in a transformed recordset represents multiple fields in the underlying tables. There is no way to change the data in a transformed field and have it reflected in the underlying tables.
Updating limitations
In Access, the records in your tables may not always be updateable. Table 4-1 shows when a field in a table is updateable. As Table 4-1 shows, queries based on one-to-many relationships are updateable in both tables (depending on how the query was designed).
Overcoming query limitations
Table 4-1 shows that there are times when queries and fields in tables are not updateable. As a general rule, any query that performs aggregate operations or uses an ODBC (Open DataBase Connectivity) data source is not updateable. Most other queries can be updated. When your query has more than one table and some of the tables have a one-to-many relationship, there may be fields that are not updateable (depending on the design of the query).
Updating a unique index (primary key)
If a query uses two tables involved in a one-to-many relationship, the query must include the primary key from the one-side table. Access must have the primary key value so that they can find the related records in the two tables.
Replacing existing data in a query with a one-to-many relationship
Normally, all the fields in the many-side table (such as the tblSales table) are updateable in a one-to-many query. All the fields (except the primary key) in the one-side table (tblCustomers) can be updated. Normally, this is sufficient for most database application purposes. Also, the primary key field is rarely changed in the one-side table because it is the link to the records in the joined tables.
Design tips for updating fields in queries
If you want to add records to both tables of a one-to-many relationship, include the foreign key from the many-side table and show the field in the datasheet. After doing this, records can be added starting with either the one-side or many-side table. The one side’s primary key field is automatically copied to the many side’s join field.
If you want to add records to multiple tables in a form (covered in Chapters 7 and 8), remember to include all (or most) of the fields from both tables. Otherwise, you will not have a complete record of data in your form.
Creating and Working with Query Joins
By default, an Access query returns only records where data exists on both sides of a relationship. This means, for instance, that a query that extracts data from the Contacts table and the Sales table only returns records where contacts have actually placed sales, and will not show contacts who haven’t yet placed a sale. If a contact record is not matched by at least one sales record, the contact data is not returned by the query. This means that, sometimes, the query does not return all of the records that you expect it to produce.
The situation described in the preceding paragraph is called an inner join, or an equi-join. Although this is the most common join type between tables in a query, there are instances where users want to see all of the data in a table (like the tblContacts table in the preceding example), whether or not those records are matched in another table. In fact, users often want to specifically see records that are not matched on the other side of the join. Consider a sales department that wants to know all the contacts who have not made a sale in the last year. You must modify the default query join characteristics in order to process this type of query.
You can create joins between tables in these three ways:
• By creating relationships between the tables when you design the database.
• By selecting two tables for the query that have a field in common that has the same name and data in both tables. The field is a primary key field in one of the tables.
• By creating joins in the query designer at the time you create the query.
The first two methods occur automatically in the Query Design window. Relationships between tables are displayed in the Query Designer when you add the related tables to a query. It also creates an automatic join between two tables that have a common field, provided that field is a primary key in one of the tables and the Enable Auto Join choice is selected (by default) in the Options dialog box.
If relationships are set in the relationship builder, you may add a table to a query and it will not automatically be related to another table, as in these examples:
• The two tables have a common field, but it is not the same name.
• A table is not related and cannot be logically related to the other table (for example, tblContacts cannot directly join the tblSalesLineItems table).
If you have two tables that are not automatically joined and you need to relate them, you join them in the Query Design window. Joining tables in the Query Design window does not create a permanent relationship between the tables. Rather, the join (relationship) applies only to the tables while the query operates.
Tables in a query have to be joined in some fashion or other. Including two tables with nothing in common (for instance, a query based on tblContacts and tblProducts, with nothing in between them) means that Access has no way to know which records in the tblContacts table match which records in the tblProducts table. Unless there is some way to relate the tables to one another, the query returns unusable data.
All tables in a query should be joined to at least one other table. If, for example, two tables in a query are not joined in some way, the query produces a Cartesian product (also known as the cross product) of the two tables. (This subject is discussed in the “Creating a Cartesian Product” section, later in this chapter). For now, note that a Cartesian product means that if you have five records in table 1 and six records in table 2, the resulting query will have 30 records (5 × 6) that will probably be useless.
Joining tables
Figure 4-23 shows a simple query containing tblSales, tblSalesLineItems, tblProducts, and tblCategories. Notice that the join line between tblProducts and tblCategories is thinner than the other join lines and does not include the 1 and infinity symbols. This is an auto-join, formed when the Categories table was added to the query.
Figure 4-23
An auto-join between tblProducts and tblCategories
No formal relationship yet exists between tblProducts and tblCategories. However, Access found the Category field in both the tables, determined that the Category data type is the same in both tables, and that the Category field in tblCategories is the primary key. Therefore, Access added an auto-join between the tables.
Tables are not joined automatically in a query if they are not already joined at the table level, if they do not have a common named field for a primary key, or if the AutoJoin option is off.
If Access had not auto-joined tblProducts and tblCategories (perhaps because the Category field was named differently in the tables), you easily add a join by dragging the Category field from one table and dropping it on the corresponding field in the other table.
Specify the type of join
The problem with auto-joins is that, but default, they exhibit equi-join behavior as the query executes. In the case of the query in Figure 4-23, if a product record exists that does not have an assigned category (for instance, a car that was never assigned to a category) the query does not return any records where a product record is not matched by a category. Figure 4-24 shows the result of this query.
Figure 4-24
You can’t tell that records are missing from this query.
The problem in Figure 4-24 is that you can’t even tell that records are missing. The only way you’d ever determine there should be more than 81 records is by carefully examining the sales records, or by composing another query that counts all sales, or performing some other audit operation.
You must modify the join characteristics between tblProducts and tblCategories to get an accurate picture of the Access Auto Auctions sales. Carefully right-click on the thin join line between tblProducts and tblCategories, and select the Join Properties command from the shortcut menu. This action opens the Join Properties dialog box (see Figure 4-25), enabling you to specify an alternate join between the tales.
Figure 4-25
Selecting an outer join for the query
In Figure 4-25, the third option (Include All Records from ‘tblProducts’ . . .) has been selected (the first option is the default). Options 2 and 3 are called outer joins and direct Access to retrieve all records from the left (or right) table involved in the join, whether or not those records are matched on the other side of the join.
Figure 4-26 shows the result of the new join. In the lower-right corner of this figure you see how an outer join appears in the Access query design, while the rest of the figure shows the recordset returned by the query.
Figure 4-26
A right outer join corrects the problem in Figure 4-25.
An outer join is represented by a join line with an arrow pointing at one of the tables involved in the join. In Figure 4-26, tblProducts is right-joined to tblCategories, which means all records from tblProducts are shown, whether or not there are matching records in tblCategories.
The larger portion of Figure 4-26 shows the recordset from the query. Notice that 84 records are now returned, and that the first three rows in the recordset have no Category value. The query now accurately reports the number of sales records.
Of course, you can easily create joins that make no sense, but when you view the data, you’ll get less-than-desirable results. If two joined fields have no values in common, you’ll have a datasheet in which no records are selected.
You can select either table first when you create a join.
You would never want to create a meaningless join. For example, you would not want to join the City field from the tblContact table to the tblSalesDate of tblSales. Although Access will enable you to create this join, the resulting recordset will have no records in it.
Deleting joins
To delete a join line between two tables, select the join line and press the Delete key. Select the join line by placing the mouse pointer on any part of the line and clicking once.
If you delete a join between two tables and the tables remain in the Query window unjoined to any other tables, the solution will have unexpected results because of the Cartesian product that Access creates from the two tables. The Cartesian product is effective for only this query. The underlying relationship remains intact.
Access enables you to create multiple-field joins between tables (more than one line can be drawn). The two fields must have data in common; if not, the query will not find any records to display.
Understanding Table Join Types
In Chapter 3, you learned about table relationships and relating two tables by a common field. Access understands all types of table and query relations, including these:
• One-to-one
• One-to-many
• Many-to-one
• Many-to-many
When you specify a relationship between two tables, you establish rules for the type of relationship, not for viewing the data based on the relationship.
To view data in two tables, they must be joined through common fields in the two tables. Tables with established relationships are automatically joined through the relationship. Within a query, you can create ad-hoc joins or change existing joins, and as you’ve already seen, Access often auto-joins tables for you. Just as there are different types of relationships, there are different types of joins. In the following sections, you learn about a number of different types of joins:
• Equi-joins (inner joins)
• Outer joins
• Self-joins
• Cross-product joins (Cartesian joins)
Inner joins (equi-joins)
The default join in Access is known as an inner join or equi-join. It tells Access to select all records from both tables that have the same value in the fields that are joined.
The Access manuals refer to the default join as both an equi-join and inner join (commonly referred to as an inner join in database relational theory). The Access Help system refers to it as an inner join. The terms equi-join and inner join are interchangeable; however, in the remainder of this chapter they are referred to as inner joins.
If records are found in either table that do not have matching records in the other table, they are excluded from the returned recordset and are not shown in the datasheet. Thus, an inner join between tables is simply a join where records are selected when matching values exist in the joined field of both tables.
You can create an inner join between the tblContacts and tblSales tables by bringing these two tables into a new query and clicking on the join line to activate the Join Property dialog box and selecting the first choice: Only Include Rows Where the Joined Fields from Both Tables Are Equal.
Remember that you are looking for all records from these two tables with matching fields. The ContactID field and Buyer contain the common field values, so the inner join does not show any records for contacts that have no sales or any sales that do not relate to a valid contactID number. Referential integrity prevents sales records that are not tied to a contact number. Of course, it’s possible to delete all sales from a contact or to create a new contact record with no sales records (possibly a seller instead of a buyer), but a sale should always be related to a valid contact (buyer). Referential integrity keeps a contact from being deleted or changed if there is a related sale.
It’s possible to have a buyer in tblContacts who has no sales. With referential integrity controlling the relationship, it is impossible, to have a sale with no buyer. If you create a query to show contacts and their sales, any record of a contact without a sale is not shown in the returned recordset.
Changing join properties
You can change the default behavior of tables joined through formal relationships. Because default joins in Access queries are always inner joins, a query may under-report the returned data, as shown in Figure 4-24.
A join property is a rule that is enforced by Access. This rule tells Access how to interpret exceptions between two tables. For example, as you saw earlier, should the noncorresponding records be shown?
Access has several types of joins, each with its own characteristics or behaviors. Access enables you to change the type of join quickly by changing its properties. You change join properties by selecting the join line between tables and double-clicking the line or right-clicking and selecting Join Properties from the shortcut menu. When you do so, the Join Properties dialog box appears (Figure 4-27).
Figure 4-27
The Join Properties dialog box
As Figure 4-27 shows, the Join Properties dialog box has two parts: the four combo boxes and three option buttons. For now, you focus on the three options buttons:
• Only include rows where the joined fields from both tables are equal. (This is the default.)
• Include ALL records from “tblContacts” and only those records from “tblSales” where the joined fields are equal.
• Include ALL records from “tblSales” and only those records from “tblContacts” where the joined fields are equal.
The first choice is commonly known as an inner join, and the other two are known as outer joins. These joins control the behavior of Access as it builds the recordset from the query.
Inner and outer joins
The Query Design window should now display two tables in the top pane of the Query window—tblContacts and tblSales, with four fields selected to display. If your query window does not have these two tables, create a new query and add them. The following sections use these tables as examples to explain how inner and outer joins operate.
Inner joins
You’ve already seen many examples of inner joins. The important thing to keep in mind about inner joins is that the records returned by an inner join will not include any records that are unmatched on either side of the join. A contact with no sales will not be shown; neither will a product without a specified category.
Creating a right outer join
Unlike inner joins (equi-joins), outer joins show all records in one table and any matching records in the other. The table or query that does not have a matching record simply displays an empty cell for the unmatched data when the recordset is displayed.
When you have created an outer join, the join line points to one of the tables (see Figure 4-26). The base of the arrow is attached to the “main” table—the one that returns all records. The arrow points to the right-joined (or left-joined) table—the one that may be missing a matching record.
So far the outer join examples you’ve seen have involved tables with no formal relationships. Figure 4-28 shows the results of an inner join between contacts and sales. Not all contacts have placed sales with Access Auto Auctions; perhaps they are sellers and not buyers.
The recordset contains 82 records and includes all contacts, whether or not they’ve placed sales.
Figure 4-28
A datasheet with a right outer join. It shows all contacts (buyers and sellers), including those with no sales.
Creating a left outer join
Once in the query design, again double-click the join line between the tblContacts and tblSales tables. Select the third choice from the Join Properties dialog box (Include All Records from tblSales). Then click the OK button. The join line now has an arrow pointing to tblContacts. This is known as a left outer join. (If the arrow points to the right in the top pane, the join is known as a right outer join; if the arrow points to the left, it’s a left outer join.)
If you create this left outer join query between the tables and select the Datasheet button to display the recordset, you will see that you again have 53 records. This simply means that there are no records in tblSales (sales without buyers). If there were one or more sales without buyers, this query would show them. The sales records, without buyers, would result from selecting the join property to include all records from tblSales (a left outer join in database terminology).
Any sales record without a buyer is known as an orphan record. Referential integrity can’t be set in the Relationships window if there is an orphan record. If you attempt to set Referential Integrity between tables and you cannot, simply remove any orphan records and then return to the Relationships window to set up referential integrity between the tables.
Creating a Cartesian product
If you add both the tblContacts and tblSales tables to a query but don’t specify a join between the tables, Access combines the first tblContact record with all the tblSales records; then it takes the second record and combines it with all the tblSales records and continues until all the tblContacts records have been combined with all of the tblSales records. Combining each record in one table with each record in the other table results in a Cartesian product (cross-product) of both tables. Because tblContacts has 58 records and tblSales has 53, the resulting recordset has 3,074 records.
Summary
This chapter has taken on the major topic of building select queries. Without a doubt, query creation is a daunting task, and one that takes a lot of practice. Even simple queries can return unexpected results, depending on the characteristics of the join between tables, and the criteria used to filter data in the underlying tables.
Queries are an integral and important part of any Access database application. Queries drive forms, reports, and many other aspects of Access applications.
Your best bet for mastering Access queries is to try increasingly difficult queries, and to always check your work. In the case of improperly joined tables, Access queries almost always under-report the data in the tables. You will discover the missing records only by carefully examining the data to ensure that your query is working properly.