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 13 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 13 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 events that occurred between June 1, 2012 and January 1, 2015. 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, 2012 and before January 1, 2015) together on the same line.
• Separate the conditions with an AND operator.

Figure 14-1 shows the query window for this range of dates.

Don’t worry about the pound signs (#) — Access puts those in automatically for you. When entering your ranges in the future, don’t type them yourself, let Access do its job. You don’t want it to feel unnecessary, right?

Anyway, 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:

   Did this event take place before June 1, 2015?

   • If the event occurred 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 event took place on or after June 1, 2012, Access asks the second question:

   Did this event take place before January 1, 2015?

   • 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 greater-than sign (>) without the equal sign (=) would include events occurring after June 1, 2012 — but not events occurring on June 1, 2012. Similarly, the second date in this query could be specified as either <1/1/2015 or ≤12/31/2014 to get the same results: records occurring before the first day of 2015.

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 14-2 for an example of BETWEEN in action). The criteria BETWEEN #6/1/2012# AND #1/1/2015# selects records if the dates land on or between June 1, 2012 and January 1, 2015.

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 type 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 14-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 events that happened between June 1, 2012 and January 1, 2015, AND that have an EventType of Education.

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

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 14-4. Here you see that events between June 1, 2012 and January 1, 2015 (based on the EventDate field’s date range) and that are also of the Education or Collection (for the EventType field) will be the only records to meet the query’s criteria.

Each line can include a 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 14-5. Here you see a query that searches for Education or Collection events that occurred in June of 2014 or June of 2015, achieved by including the EventDate field twice, each time with a different date range. Such a search might be done to isolate events that occur in a month with volatile weather, which can affect attendance or result in postponements.

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 come 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 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 our Pantry database, querying for events that happened before June 1, 2012 and after June 1, 2014 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 events that happened within that range of dates but that were also of a particular type or run by a particular volunteer. 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 events that happened within a range of dates (EventDate).
• The other Criteria line shows the EventType we want to see — Fundraising or Collection this time.

Figure 14-6 shows this combination of AND and OR operators in action — a range of dates within one year, and events relative to another year, along with specific EventType values.

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.