Chapter 13
In This Chapter
Defining what queries are and what they can do
Posing questions (and getting answers) with filter and sort queries
Asking deep questions with queries
Making query magic with the Query Wizard
You know the old saying, “The only stupid question is the one you didn’t ask.” It’s supposed to mean that if you have a question, ask it — because if you don’t, you’ll be operating in the dark, and that’s far sillier than your question could ever have been. Although you don’t ask “meaning of life” questions of a database, you do pose questions such as, “How many customers in London do we have?” or “What’s the phone number of that guy who works for Acme Explosives?” Either of those questions, if you didn’t ask it, would require you to scroll through rows and rows of data to find the information. (That’s the silly approach, obviously.)
This chapter introduces you to the art of asking questions about the information in your database using queries. You discover how to use the Query Wizard to pose simple questions, and then you find out about creating your own simple-yet-customized queries by using the bizarrely and inaccurately named Advanced Filter/Sort tool. After that, you discover how to combine multiple tables (from the same database) in your query — which can yield interesting answers to the questions you have about your data. Suffice it to say that by the end of this chapter, you’ll be a veritable quizmaster, capable of finding any record or group of records you need.
Wait a minute. We were just talking about queries, and the heading above says something about filter and sort. What happened to querying? Well, sorting and filtering are queries unto themselves — using them, you can more easily find a record — say, for a particular person if your list is in alphabetical order by the LastName
field. Filtering, on the other hand, says “Give me all the records that have this in common” (this being the criterion you set your filter to look for). It’s kind of like playing Go Fish with your database.
It’s all related, and it’s all about asking questions. There are really two ways to find a particular record:
Of course, the exclamation point is optional (depending on your level of excitement about the data involved) — and so is the method you use. You can query or filter for any record or group of records you want. And you can sort the results of your filter. But it’s still all about asking questions.
I begin by explaining filters because they’re more straightforward, procedurally, than queries. By starting with filters, too, you can get your feet wet with them while preparing for the deeper waters of querying.
Filters quickly scan a single table for whatever data you seek. Filters examine all records in the table and then hide those that do not match the criteria you seek.
The filtering options are virtually unlimited, but simple:
City
field for Lancaster.EventType
field for Fundraising, and there you go.Wi-Fi
field and Outdoor in the LocationType
field.There’s a price to pay for ease and simplicity, however. Filters are neither smart nor flexible:
Chapter 12 covers filters in their limited-but-useful glory.
Queries go far beyond filters. But to get to that great “beyond,” queries require more complexity. After all, a bicycle may be easy to ride, but a bike won’t go as fast as a motorcycle. And so it goes with queries. Queries work with one or more tables, let you search one or more fields, and even offer the option to save your results for further analysis, but you can’t just hop on and ride a query with no lessons.
Advanced Filter/Sort is more powerful than a run-of-the-mill filter. It’s so powerful that it’s like a simple query:
You can ask only simple questions with the filter.
Real, honest-to-goodness queries do a lot more than that.
The filter always displays all the columns for every matching record.
With a query, you choose the columns that you want to appear in the results. If you don’t want a particular column, leave it out of the query. Filters aren’t bright enough to do that.
Even with these limitations, Advanced Filter/Sort makes a great training ground to practice your query-building skills (that’s why I’m starting with this feature, despite the word “Advanced” in its name).
Before you use the Filter window, you need to take a quick look at its components and what they do. In the section that follows this one, you find out how to access and use the Filter window.
The Filter window is split into two distinct sections, as shown in Figure 13-1:
Field list (the Volunteers box in Figure 13-1): The Field list displays all the fields in the current table or form (the table or form that’s open at the time). Not sure about forms? Check out Chapter 7!
At this point, don’t worry about the upper half of the window. The Field list comes more into play when you start working with full queries. The table you were working on is already shown in the upper half of the window, so you don’t have to do anything with this part of the window now.
Query grid (the lower half of the screen): When you use the Advanced Filter/Sort command, you are presented with a blank query grid for the details of your filter.
You’re building a filter, but Access calls the area at the bottom of the screen a query grid because you use the same grid for queries. (You also see it later in the chapter, in the section about building real queries.)
To build the filter, you simply fill in the spaces of the query grid at the bottom of the window you see in Figure 13-1. Access even helps you along the way with pull-down menus and rows that do specific tasks. The procedure for filling in these spaces appears in the next section, along with details about each part of the grid and how it all works.
The following sections show how to design and use filters.
Start your filter adventure by firing up Access’s basic query tool, the Advanced Filter/Sort.
Decide what question you need to ask and which fields the question involves.
Because it’s your data, only you know what information you need and which fields would help you get it. You may want (for example) a list of volunteers who live in a particular state, donors who’ve donated in excess of a certain amount to your organization, cities in a particular state, books by your favorite author, or people whose birthdays fall in the next month (if you’re painfully organized, in which case I commend you).
Whatever you want, decide on your question first and then get ready to find the fields in your table or form that contain the answer.
Don’t worry if your question includes more than one field or multiple options. Both filters and queries can handle multiple-field and multiple-option questions.
Open the table (or form) that you want to interrogate.
Assuming you have the correct database open, your table or form pops into view.
In the Sort & Filter section of the Ribbon’s Home tab (see Figure 13-2), choose Advanced ⇒ Advanced Filter/Sort.
The Filter window appears, ready to accept your command. What you see depends on the following considerations:
The Filter window is nothing but a simplified query window. The filter looks, acts, and behaves a lot like a real query. More information about full queries comes later in the chapter, so flip ahead to the next section if that’s what you need.
After you open the Filter window, you’re ready to select fields and criteria for your filter. The following section shows you how.
As you begin selecting the fields you want to use in your filter and set up the criteria against which your fields’ content will be compared, be sure to follow these steps carefully:
Click the first box in the Field row (in the query grid) and then click the down arrow that appears to the right of the box.
The drop-down menu lists all the fields in your table (or form).
Click the field (as identified in the preceding section) that you want to query.
Access helpfully puts the field name in the Field box on the query grid. So far, so good.
If you want to see the results of your filter in the same order that your data always appears in, skip to Step 4.
Ascending order is lowest to highest (for example, A, B, C …). Descending order is highest to lowest (for example, Z, Y, X …). If you don’t want to stop and click the drop arrow to choose a sort method, just type an “a” or a “d” and Access fills it in for you, and you can move on to the Criteria
field to specify what you’re looking for.
Set up your criteria for the field.
Follow these steps:
Type each criterion (such as =value, where “value” refers to a specific word or number that is represented within your data or < or > followed by a value).
Setting criteria is the most complex part of building a query — it’s the most important part of the entire process. The criteria are your actual questions, formatted in a way that Access understands. Table 13-1 gives you a quick introduction to the different ways you can express your criteria.
If you’re making comparisons with logical operators, flip to Chapter 14 for everything you need to know about Boolean logic, the language of Access criteria.
If your question includes more than one possible value for this field, click the Or box and type your next criterion in the box to the right of the word Or.
If you move on to a new box, the criterion you entered is automatically placed in quotes. Don’t worry. This is just Access acknowledging that you’ve given it a specific value to look for, to
Table 13-1 Basic Comparison Operators
Name |
Symbol |
What It Means |
Example |
Equals |
|
Displays all records that exactly match whatever you type. |
To find all items from customer 37, type 37 in the Criteria row. |
Less Than |
|
Lists all values that are less than your criterion. |
Typing <50000 in the |
Greater Than |
|
Lists all values in the field that are greater than the criterion. |
Typing >50000 in the |
Greater Than or Equal To |
|
Works just like Greater Than, except it also includes all entries that exactly match the criterion. |
>=50,000 finds all values from 50,000 to infinity. |
Less Than or Equal To |
|
If you add |
<=50000 includes not only those records with values less than 50,000, but also those with a value of 50,000. |
Not Equal To |
|
Finds all entries that don’t match the criterion. |
If you want a list of all records except those with a value of 50,000, enter <>50000. |
With all the fields and criteria in place, it’s time to take your filter for a test drive. Figure 13-3 shows an example that uses two criteria: the Volunteers table filtered for Active Status volunteers whose start date is before 2011, indicating they’re long-time volunteers whom the organization can count on.
After completing the process of choosing fields and setting criteria, you’re ready to run the filter. Click the Toggle Filter button in the Sort & Filter section of the Ribbon.
Access thinks about it for a moment, and then the record or records that meet your criteria appear. This is shown in Figure 13-4. Pretty cool, eh? Note the little Filter symbols on the Field Name headers for the two fields by which this particular table was filtered — a tiny arrow and a Filter icon — to remind you which fields your filter used.
Click the Toggle Filter button, found in the Sort & Filter section of the Ribbon’s Home tab.
The filtered records join their unfiltered brethren in a touching moment of digital homecoming.
Click the Filtered button.
This button appears at the bottom of the Access window (next to the Record buttons that you use to move through your records one at a time, and you can see it in Figure 13-4). When you click the Filtered button, your entire table comes back, and the button changes so its label says Unfiltered. Click again? The results of your query return. It’s a quick toggle, perhaps even toggle-ier than the Toggle Filter button!
The basic query tool, created to make your life easier, is the Select query — so named because it selects matching records from your database and displays the results according to your instructions.
The best process for creating a Select query depends on the following:
In life, solid relationships make for a happier person; in Access, solid relationships make for a happier query experience.
Key fields relate your Access tables to each other. Queries use key fields to match records in one table with their related records in another table. You can pull data for the item you seek from the various tables that hold this data in your database — provided they’re properly related before you launch the query.
Want to refresh your memory on creating relationships between the tables in your database? Check out Chapter 4, where all those secrets are revealed.
You can rely on the Query Wizard — and the Simple Query Wizard found within it — for a real dose of hands-free filtering. With the Simple Query Wizard, you enter table and field information. The wizard takes care of the behind-the-scenes work for you.
Access isn’t psychic (that’s scheduled for the next version); it needs some input from you!
To create a query with the Query Wizard’s Simple Query Wizard, follow these steps:
On a piece of paper, lay out the data you’d like in your query results.
A query returns a datasheet (column headings followed by rows of data), so make your layout in that format. All you really need are the column headings so you’ll know what data to pull from the database.
Determine the table location of each piece of data (column heading) from your paper.
Write down the table and field name that contain the data matching the column heading on the paper above the column heading.
In the Database window, click the Create tab on the Ribbon and then click the Query Wizard button from the Queries section.
The New Query Wizard dialog box appears, asking you what kind of Query Wizard you’d like to run. Choose Simple Query Wizard and click OK.
Choose the first table you want to include in the query (see Figure 13-5).
You’ll use the Tables/Queries drop-down menu, which shows all the tables (and any existing queries) in your database. Here are the specifics:
Select the fields from that table or query for your query.
Repeat these steps to select each field you want included in your query:
Click the name of the table or query to include in this query.
The Available Fields list changes and displays the fields available in the table.
If you add the wrong field, just double-click it in the Selected Fields list. It will go back home. If you just want to start all over, click the double-left chevron (that’s what you call the symbol that looks like a less-than sign) and all the selected fields go away.
After you select all the fields, click Next.
If the wizard can determine the relationships between the tables you selected, the window in Figure 13-7 appears.
If you don’t see the window, not to worry. Access just wants you to name the query instead. Skip to Step 8.
If you include fields from two tables that aren’t related, a warning dialog box appears. The dialog box reminds you that all the selected tables must be related before you can run your query — and suggests that you correct the problem before continuing. In fact, it won’t let you go any further until you appease it in one of two ways:
If the wizard asks you to choose between a Detail and a Summary query, click the radio button next to your choice and then click Next.
Summary tells the wizard that you aren’t interested in seeing every single record; you want to see a summary of the information instead.
A summary query can perform calculations (such as sums and averages) on numeric fields. If text fields are selected, Access can count the records or pull the first and last item from the set of fields alphabetically.
If you want to make any special adjustments to the summary, click Summary Options to display the Summary Options dialog box shown in Figure 13-8. Select your summary options from the check boxes for the available functions — Sum, Avg, Min, and Max — and then click OK.
If you’re curious about how the wizard decides whether to display the Detail or Summary step, the sidebar “To summarize or not to summarize” tells the story.
In the wizard page that appears, select a radio button for what you want to do next:
If you want to make your query snazzy: Select the Modify the Query Design option.
The wizard sends your newly created query to the salon for some sprucing up, such as the inclusion of sorting and totals.
The wizard runs the query and presents the results in a typical Access datasheet.
Type a title for your query in the text box and then click Finish.
The wizard builds your query and saves it with the title you entered; then Access displays the results, as shown in Figure 13-9.
Congratulations! You’ve given birth to a query.
AND
and OR
criteria, see Chapter 14.If you use Access regularly, you need to know how to build a query from scratch. This is where Design view comes into play. Design view may look a little complicated — check out Figure 13-10 — but it’s really not that bad, and you have seen it before, when building the filters we just looked at. You’ll be all right, I promise!
To build a multiple-table query by hand in Design view, follow these steps:
Click the Create tab on the Ribbon.
A series of buttons organized by object type appears on the Ribbon.
From the Queries section, click the Query Design button.
The Show Table dialog box appears, listing all tables and queries available for your new query.
Yes, you can query a query.
Add the tables you want in your query:
In the Show Table dialog box (see Figure 13-11), double-click the names of each table or query you want.
After you double-click a table, a small window for that table appears in the Query Design window.
After you add the last table you want, click Close.
The Show Table dialog box is dismissed.
In the Query Design window, lines between your tables (as shown in Figure 13-12) show relationships between the tables. The sidebar “Get the right tables” explains how these relationships are essential to the proper building and execution of your query.
In the table(s) that now appear(s) in the top half of the Query window, double-click each field you want in the list at the top of the Query Design window.
Consider the following while choosing fields:
If you accidentally choose the wrong field, you can easily correct your mistake:
Select the Delete Columns button from the Ribbon’s Query Tools Design tab.
The field is removed from the Query grid.
Now you’re ready to put the finishing touches to your query by adding functionality such as sorting. The following section shows you how.
To sort your query results in Design view (as shown here), follow these steps:
Repeat these steps for each field you want to use for sorting:
The sidebar “Just these, and in this order” shows how to arrange sort fields. For all you need to know about sorting, see Chapter 12.
In the Criteria row for each field you want to use as criterion, type the criterion appropriate to that field.
For example, to show only fundraising events, as shown in Figure 13-14, type Fundraising in the Criteria cell in the EventType column. Table 13-1, earlier in the chapter, shows some criteria examples.
If you don’t want that field to appear in the final results, deselect the check box in the Show row for that field.
The Show setting really stands out in your Query grid. There’s only one check box in there — the Show option.
After you tell the query how to sort and select data, you’re ready to see your query results by running the query.
After you create your query, you’re ready to save it. Follow these steps:
Review your work one more time. When you’re sure it looks good, click the Save button on the Quick Access Toolbar to save your query.
The Save As dialog box appears.
In the Save As dialog box, type a name for the query and then click OK.
You’re saving the design of the query and not the results returned by the query. So as records are added, edited, and deleted from your data tables, the query always returns the data as it is at the moment the query is run.
After you create your query and save it, you’re ready to run it. Follow these steps:
Take one last look to make sure it’s correct.
Inspect the fields you’ve chosen and your other settings in the query grid. The sidebar “Query troubleshooting” lists common query problems.
Click the Run button (the huge red exclamation point, shown second from left in the Ribbon’s Design tab shown in Figure 13-15).
Did you get the answer you hoped for? If not, take your query back into Design view for some more work. To do so, click the Design View button on the Quick Access Toolbar.