Chapter 12

I Was Just Asking . . . for Answers

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 get the lowdown on combining multiple tables (from the same database) — 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.

Don’t worry if your first few queries produce odd or unexpected results. As with anything new, queries (along with their inherent procedures and concepts) take a little getting used to. Because they’re so powerful, they can be a little complicated, but it’s worth taking the time to figure things out (with the help of this book!). Take your time, be patient with yourself, and remember that old saying: “The only stupid question is the one you didn’t ask.”

Simple (Yet Potent) Filter and Sort Tools

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 Last Name 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:

Queries use a set of criteria — conditions that eliminate many, if not all but one, of your records — that you present to the database and that says, “Look here, here, and here, and find THIS for me!”

Filters use one criterion, saying (in effect), “Sift through all these records and find the one(s) with THIS!”

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.

Filter things first

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.

How filters work

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:

Want every customer in London? Filter the City field for London.

Want to see all the Dining expenses from 2010? Filter for Expense Date (between 1/1/2010 and 12/31/2010) and the expense Category for Dining, and there you go.

Want all the products that cost more than $15.00, but that are not Condiments? Filter for Unit Price greater than $15.00, and for the Category (not Condiments).

There’s a price to pay for ease and simplicity. Filters aren’t smart or flexible:

You cannot filter multiple tables without first writing a query that contains — brings together — the tables.

A filter cannot be the basis for the records seen on a report or form.

Chapter 11 covers filters in their limited-but-useful glory.

What about queries?

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.

For all the differences between filters and queries, the most advanced filter is, in reality, a simple query — which makes some bizarre sense: Your first step into the world of queries is also your last step out of the domain of filters. Welcome to Advanced Filter/Sort, the super-filter of Access, masquerading as a mild-mannered query.

Advanced Filter/Sort

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 use the same steps to build an Advanced Filter/Sort as you do to create a query.

The results look quite a bit alike, too.

Advanced Filter/Sort looks, acts, and behaves like a query, but it’s still a filter at heart and is constrained by a filter’s limits, including these:

Advanced Filter/Sort works with only one table or form in your database at a time, so you can’t use it on a bunch of linked tables.

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 those 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).

Although this section talks about applying filters only to tables, you can also filter a query. There’s a good reason to filter queries: Some queries take a long time to run, so it’s faster to filter the query results than to rewrite and rerun the query. Suppose (for example) that you run a complicated sales-report query and notice it includes data from every state instead of the individual state you wanted. Rather than modify the query and run it again, you can apply a filter to your query’s results. Poof! You get the results you’re after in a fraction of the time.

Fact-finding with fun, fast filtering

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 12-1:

Field list (the Customers box in Figure 12-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 12-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.

Figure 12-1: The Advanced Filter/Sort window allows you to choose the field(s) on which to filter your data in the current table.

Here’s the “advanced” part

The Advanced Filter/Sort tool also works on forms. If you feel particularly adventuresome (or if you mainly work with your data through some ready-made forms), try the filter with your form. Filtering a form works like filtering a table, so you can follow the same steps.

The following sections show how to design and use filters.

Starting the process

Start your filter adventure by firing up Access’s basic query tool, the Advanced Filter/Sort.

1. 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.

2. Open the table (or form) that you want to interrogate.

Assuming you have the right database open, your table or form pops into view.

3. Click the Ribbon’s Home tab at the top of the Access workspace.

4. In the Sort & Filter section of the Ribbon’s Home tab (see Figure 12-2), choose Advanced⇒Advanced Filter/Sort.

The Filter window appears, ready to accept your command. What you see depends on the following considerations:

• If you previously used a filter of any kind with this table, Access puts that most recently used filter information into the new window.

• If no filter was done previously, the Filter/Sort window looks pretty blank — for now.

Figure 12-2: Click the Advanced button to choose Advanced Filter/Sort.

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.

Selecting fields and criteria

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:

1. Click the first box in the Field row 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).

2. 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.

3. If you want to sort your filter results by this particular field, follow these steps:

a. Click the Sort box.

b. Click the down arrow that appears.

c. Select Ascending or Descending from the drop-down menu.

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.

4. Set up your criteria for the field.

Follow these steps:

a. Click the Criteria box under your field.

b. 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 12-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 13 for everything you need to know about Boolean logic, the language of Access criteria.

c. 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

• Make an exact match.

• Use the value with a greater-than or less-than symbol for comparison.

If your question involves more than one field, repeat the preceding Steps 1–4 for each field. Just use the next block in the grid for the additional field or fields you want to use in your filter.

With all the fields and criteria in place, it’s time to take your filter for a test drive. Figure 12-3 shows an example that uses two criteria: the Products table filtered for products that cost more than $20.00 that fall into the Beverages Category.

Figure 12-3: Two fields are queried in this example — Unit Price and Category.

Running the filter

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 met your criteria appear. This is shown in Figure 12-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.

There are two ways to see all the data again:

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 12-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!

Figure 12-4: Voilà! Your filtered data appears; no abracadabra needed.

Select Queries

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 sidebar Secrets of the Select query summarizes the differences that make Select queries more powerful than lesser filters. If upon reading these secrets, you suspect that the Select query sounds like the right tool for the job you have in mind, you may be able to use a query instead of a filter.

The best process for creating a Select query depends on the following:

If you’re new to writing queries, the Query Wizard is a fast, easy way to get started. It walks you through the process of selecting tables and fields for your query — and can even add some summary calculations (such as counting records) to your query.

If you’ve already written some queries and are comfortable with the Query Design window, you’ll probably want to bypass the Query Wizard and build your queries from scratch. Later in this chapter, “Getting Your Feet Wet with Ad Hoc Queries” guides you through the process.

---

---

Solid relationships are the key to getting it all (from your tables)

In life, solid relationships make for a happier person; in Access, solid relationships make for a happier query experience.

To query your database effectively, you need to know the following about its table structure:

Which tables do you need to use?

How are the tables you need to use related to each other?

Which fields contain the data you want to know about?

Which fields do you need in the solution?

Access maintains relationships between the tables in your database. Usually you (or your Information Systems department) create these relationships when you first design the database. When you build the tables and organize them with special key fields, you actually prepare the tables to work with a query.

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.

If you don’t relate your tables via the Relationships window, you’ll have to do so for each multiple-table query you build in Access. As a general rule, put in the time to properly design and relate your tables. With proper table design and relationships, you’ll get the results you want in a shorter amount of time.

Running the Query Wizard

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:

1. 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.

2. 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.

3. 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.

4. Choose the first table you want to include in the query (see Figure 12-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.

a. Click the down arrow next to the Tables/Queries drop-down menu (as shown in Figure 12-6).

b. Click the name of the table or query to include in this query.

Figure 12-5: The Simple Query Wizard starts and asks which table(s) you want to query.

5. Select the fields from that table for your query.

Repeat these steps to select each field you want included in your query:

a. 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.

b. In the Available Fields list, double-click each field from this table or query that you want to include in the query you’re creating.

Figure 12-6: The Tables/Queries drop-down list.

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.

6. After you select all the fields, click Next.

If the wizard can determine the relationships between the tables you selected, the window in Figure 12-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:

• Remove all the fields selected for your query from the unrelated tables.

• Fix the relationships so that all tables you’ve selected in your query are related.

Figure 12-7: The Query Wizard may give you the chance to summarize your data.

7. 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.

• Detail creates a datasheet that lists all records that match the query. As the name implies, you get all the details from those records.

• 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 12-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.

Figure 12-8: Access offers different ways of summarizing the data.

8. 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.

• If you want to skip the fancy stuff: Select the Open the Query to View Information option to see the Datasheet view.

The wizard runs the query and presents the results in a typical Access datasheet.

---

---

9. 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 12-9.

Congratulations! You’ve given birth to a query.

Figure 12-9: The results of a query built with the Query Wizard.

When you finish the steps in this section, the Query Wizard saves your query automatically with the name you typed.

---

---

Use the following list to modify or use a query created with the Query Wizard:

To write complex AND and OR criteria, see Chapter 13.

To add calculations like sums and averages, see Chapter 14.

To add custom formulas (like a sales tax calculation), see Chapter 15.

To attach the query to a report, see Chapter 17.

Getting Your Feet Wet with Ad Hoc Queries

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 daunting — check out Figure 12-10, if you don’t believe me — but it’s really not that bad. I promise!

The top half of the view is where you place the tables or previously created queries you want to include in this new query.

The bottom half is called the Design Grid; it contains the Field, Table, Sort, Show, Criteria, and Or rows used to generate the results.

You’ll notice that a new tab appears on the Ribbon as soon as you enter a Design task. The Design tab appears under a Query Tools heading, as shown at the top of the window in Figure 12-10.

To build a multiple-table query by hand in Design view, follow these steps:

1. Click the Create tab from the Ribbon.

A series of buttons organized by object type appears on the Ribbon.

2. 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.

3. Add the tables you want in your query:

a. In the Show Table dialog box (see Figure 12-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. (To see the three tables chosen for the query in this example, each in such a window, refer to Figure 12-10.)

b. 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 12-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.

4. 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:

• Choose your fields in the order you want them to appear in the query results.

• You can include fields from any or all of the tables at the top of the query window (the tables you selected in the preceding step).

Figure 12-13 shows fields selected from multiple tables in Query Design view.

Figure 12-10: A query that will allow you to see orders from customers in a particular city.

Figure 12-11: The Employees table is added to the ad hoc query.

Figure 12-12: Access knows how the Customers, Orders, and Employees tables are related.

Figure 12-13: This query will show which employee was responsible for the sales to customers in London, and show all the orders for those customers.

If you accidentally choose the wrong field, you can easily correct your mistake:

a. Click the field name’s entry in the Query grid.

b. 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.

Adding the finishing touches

To sort your query results in Design view (as shown here), follow these steps:

1. Repeat these steps for each field you want to use for sorting:

a. In the Query grid, click the Sort box under the field name.

b. Click the down arrow that appears at the edge of the Sort box.

c. Click either Ascending or Descending (as shown in Figure 12-14).

---

---

Figure 12-14: See the resulting orders in Ascending date order.

The sidebar Just these, and in this order shows how to arrange sort fields. For all you need to know about sorting, see Chapter 11.

2. In the Criteria row for each field you want to use as criterion, type the criterion appropriate to that field.

For example, to show orders from customers in London, as shown in Figure 12-15, type London in the Criteria cell in the City column. Table 12-1, earlier in the chapter, shows some criteria examples.

Figure 12-15: With the criteria set for the City field, only orders from customers in London are in the query results

3. 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.

Saving the query

After you create your query, you’re ready to save it. Follow these steps:

1. 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.

2. 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.

---

---

Running your query

After you create your query and save it, you’re ready to run it. Follow these steps:

1. 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.

2. Click the Run button (the huge red exclamation point, shown second from left in the Ribbon’s Design tab — refer to Figure 12-14).

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.

---

---