Chapter 13

I Want These AND Those OR Them

In This Chapter

Setting criteria for your queries

Working with the AND and OR operators

Including AND and OR in the same query

As you build larger and more complex databases with Access, your questions about the data they contain become larger and more complex. Simply changing the order of your records — sorting from A to Z and then from Z to A — and filtering it from one or more perspectives just may not be enough for your needs anymore. You want greater power to find and use data, and you need more from your database. You also need it quickly.

What to do? You can use queries that ask questions (present criteria to the database to see which records meet them) to help you find the records you need to access (pardon the expression) right now.

This chapter shows you how to set more specific criteria for your queries, controlling the answers to your database questions with ease and speed. You find out about two very powerful Access operators — AND and OR — what the operators do, how they do it, and (most important) when and why to use them.

If AND and OR are not the solutions to your query-building needs, Chapter 12 takes a more detailed stroll through the basics of querying, and may help point you in the right direction. You can create queries automatically, or you can build them from scratch. Chapter 12 gives you some more advanced ideas about queries and helps you craft specialized queries for your specific needs.

Working with AND and/or OR

AND and OR are the most powerful and popular of the Boolean terms.

In written language, you probably know when to use and versus or. If you’re not sure how this knowledge can be transferred to your use of Access, read on:

AND is used when all the items in a list are to be chosen. For example, “bring a salad, a side dish, and a dessert to the potluck dinner” would tell the person to bring all three items to the dinner.

OR creates a list wherein each item is a choice — “bring a salad, a side dish, or a dessert to the potluck dinner.” In this example, the person reading the instruction would know that only one item need be brought, but it’s okay to bring one or more (or all) of the items.

With the OR example, you’ll be spending less time in the kitchen, and with OR in your query, you’ll be making it possible for more of the records to meet your criteria. Using the employee database as an example, your query might say, “Give me all the people who work in Accounting AND Operations” — and none of the employee records would meet that criterion, because nobody works in two departments at the same time. However, using OR would work: “Give me all the people who work in Accounting OR Operations” would provide a list of all the employees in the two departments.

Boolean logic (named for George Boole, the guy who invented it) allows you to use words like AND, OR, NOT, LESS THAN, GREATER THAN, and EQUAL TO to search a database. In Access, these terms are called operators.

If you’re not sure you can remember when to use AND versus OR, think of it this way:

AND narrows your query. Fewer records match.

In normal usage, and gives you no options. You do everything in the list.

OR widens your query. More records match.

In normal usage, or gives you more options because you can pick and choose which items from the list you want to do.

As an example, in Access, if you’re searching a customer database and you say you want customers who live in a particular city and who live in an area with a particular zip code and who have purchased more than $50,000 worth of items in the past year, you’re probably going to end up with a short list of customers — you’ll have fewer options. On the other hand, if you want customers who are in a particular city or have a particular zip code or who have purchased more than $50,000 in goods this year, you’ll get many more choices — everyone from the city, with the zip code, and over that purchasing level — probably many more customers than the and query will give you.

Data from here to there

One of the most common Access queries involves listing items that are between two values.

Here’s an example. You may want to find all the orders placed between June 1, 2006, and January 1, 2009. For this list of records, you use AND criteria to establish the range. Here’s how:

Put the two conditions (on or after June 1, 2010, and before January 1, 2011) together on the same line.

Separate the conditions with an AND operator.

Figure 13-1 shows the query window for this range of the Order Date field’s dates.

Figure 13-1: Find data that falls within a range of dates by using AND.

Don’t worry about the pound signs (#) — Access puts those in automatically for you.

Here’s what’s going on in the query window:

1. Access begins processing the query by looking through the records in the table and asking the first question in the criteria:

Was this order placed after June 1, 2010?

• If the order was placed before this date, Access ignores the record and goes on to the next record.

• If the order was placed on or after the date, Access goes to Step 2.

2. If the order was placed on or after June 1, 2010, Access asks the second question:

Was the order placed entered before January 1, 2011?

• If yes, Access includes the record in the results.

• If no, the record is rejected, and Access moves on to the next record.

Access repeats Step 1 and Step 2 for all records in the table.

3. When Access hits the last record in the database, the query’s results appear.

Note that using the less-than sign (<) for the first date would allow the query to include only records for those orders placed after June 1, 2010 — the omission of the equal sign (=) eliminates those records with a June 1, 2010 (6/1/2010) date in the Order Date field.

This type of “between” instruction works for any type of data. You can list numeric values that fall between two other numbers, names that fall within a range of letters, or dates that fall within a given area of the calendar.

You could also search for dates by using the BETWEEN operator (see Figure 13-2 for an example of BETWEEN in action). The criteria BETWEEN #6/1/2010# AND #6/30/2010# selects records if the dates land on or between June 1, 2010, and June 30, 2010, a span of 30 days.

Figure 13-2: The BETWEEN operator is the ultimate range finder.

Using multiple levels of AND

Overall, Access lets you do whatever you want in a query — and for that reason, flexibility really adds to the application’s power.

Access doesn’t limit you to just one criterion in each line of a query — you can include as many criteria as you want, even if by adding more and more criteria, you end up whittling your results down to one record, or even no records. When you add multiple criteria, Access treats the criteria as though you typed an AND between each one and the next.

All that power can backfire on you. Each AND criterion that you add must sit with the others on the same row. When you run the query, Access checks each record to make sure it matches all the expressions in the given Criteria row of the query before putting that record into the result table. Figure 13-3 shows a query that uses two criteria: Because they all sit together on a single row, Access treats the criteria as though they were part of a big AND statement. This query returns only orders placed after January 1, 2011, AND that were shipped from Canada (Ship Country).

Figure 13-3: Multiple criteria whittle down the resulting data to just those records you want to see.

When you have a very large database and want to restrict your results to a minimum of records, combining a few criteria is the most useful way to go.

Really want to whittle that list of records down? Because Access displays the query results in Datasheet view, all the tools available in Datasheet view work with the query — including filters! Just use any of the filter commands (use the Filter tool group’s buttons to filter by Selection, for example) to limit your query results. (If you need a quick refresher on filters, flip back to Chapter 11.)

Establishing criteria with OR

When you want to find a group of records that match one of several possibilities (such as orders shipped from either Canada or France), you need the OR operator when you choose your criteria. It’s the master of multiple options.

Access makes using OR easy. Because the OR option is built right into the Access query design window, you can just

Choose the field on which to query the data.

Indicate which values to look for.

To make a group of criteria work together as a big OR statement, list each criterion on its own line at the bottom of the query, as shown in Figure 13-4. Here you see that order records for those orders shipped out of Canada OR France will meet the query’s criteria.

Figure 13-4: OR allows more (yet very specific) records to meet your criteria.

Each line can include criterion for whichever fields you want, even if another line in the query already has a criterion in that field. (This is easier than it sounds.)

Of course, you can list the criteria in different columns, as shown in Figure 13-5. Here you see a query that searches for Canadian orders with an Order Date after 1/1/20111 OR French orders with any date. Such a search might be done to isolate orders placed while a particular shipping company serving Canada and France is known to be experiencing delays.

Figure 13-5: You can use the OR operator to set criteria for data from different fields.

Each OR criterion is on a separate line. If the criteria are on the same line, you’re performing an AND operation — only records that match both rules appear.

Combining AND with OR and OR with AND

When it comes to combining the use of AND and OR operators, Access can bend like a contortionist (how do they do that?). When the AND and OR operators by themselves aren’t enough, you can combine them within a single field — or in multiple fields in one or more tables.

These logically complex queries get really complex, really fast. You can end up confused as to why certain records came back in your results — or worse, why certain records didn’t make the cut. If a query grows to the point that you’re losing track of which AND the last OR affected, you’re in over your head, and it’s time to start over again.

Instead of adding layer upon layer of conditions into a single query, you can break down your question into a series of smaller queries that build on each other:

1. Begin with a simple query with one or two criteria.

2. Build another query that starts with the first query’s results.

3. If you need more refining, create a third query that chews on the second query’s answers.

Each successive query whittles down your results until the final set of records appears.

This procedure lets you double-check every step of your logic, so it minimizes the chance of any errors accidentally slipping into your results. You can also use it to build on an existing query, adding more criteria and including more (or different) fields in the query — just open the previously created query and begin working on it as though it were a new query in progress.

Each OR line (each line within the query) is evaluated separately so that all the records that are returned by a single line will appear in the final results. If you want to combine several different criteria, make sure that each OR line represents one aspect of what you’re searching.

For example, in the Orders database, querying for orders placed before June 1, 2010, and after December 31, 2009, requires the OR condition, and using an OR condition means that the criteria go on separate lines.

Imagine, however, that you want to find only the orders placed within that range of dates but that were also shipped from a particular country. For such a query to work, you need to repeat the date-range information on each OR line.

To set up this query, you need criteria on separate lines:

One line asks for orders placed within a range of dates (Order Date).

The other Criteria line gives orders shipped from specified countries in the Ship Country field.

Figure 13-6 shows this combination of AND and OR operators in action — a range of dates for Canadian orders based on their Required date, and then all orders (based on Order Date) within a range of dates, regardless of their country.

Figure 13-6: Any criteria placed on separate lines are seen as OR statements.

When reviewing your query criteria, keep these points in mind:

Separately, make sure each line represents a group that you want included in the final answer.

Check to be sure that the individual lines work together to give you the answer you’re seeking:

• AND criteria all go on the same line and are evaluated together.

• OR criteria go on separate lines. Each line is evaluated separately.

• Criteria that you want to use in each OR statement must be repeated on each separate line in the query grid.