O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Making AND and OR Do What You Expect

Problem

Logic operators are not conceptually difficult to follow, but combining and nesting them does add complexity. If you don't construct complex SQL statements very carefully, they may return incorrect or incomplete results, sometimes without reporting any errors.

Solution

Logic operators provide the flexibility to construct criteria in any way that suits your requirements. The AND operator returns true when all conditions are met; the OR operator returns true as long as one condition is met. In terms of how this applies to SQL construction, OR is used to set criteria for which one condition must be met, while AND is used to set criteria for which all the conditions must be met. Some examples are presented in Table 1-1.

Table 1-1. Examples of using logic operators

SQL statement

Description

SELECT DISTINCT State, City,
Count(LastName) AS Customers
FROM tblCustomers
GROUP BY State, City
HAVING State="NY" AND City="Yonkers"

This gives a count of customers located in Yonkers, NY. Only customer records in which both the state is New York and the city is Yonkers are counted.

SELECT DISTINCT State, City,
Count(LastName) AS Customers
FROM tblCustomers
GROUP BY State, City
HAVING State="NY"AND City="Yonkers" OR
City="Albany"

This gives a count of customer records for which the state is New York and the city is either Yonkers or Albany.

This produces an unintended result. The OR statement does not properly apply to both Yonkers and Albany. Any Yonkers customers must be in New York, but the way this SQL statement is constructed, Albany customers do not have to be in New York. Consequently, as Figure 1-4 shows, customers in Albany, GA will also be returned.

SELECT DISTINCT State, City,
Count(LastName) AS Customers
FROM tblCustomers
GROUP BY State, City
HAVING State="NY" AND (City="Yonkers" OR
City="Albany")

This correctly returns customer records for customers located only in Yonkers, NY and Albany, NY. Enclosing the cities and the OR operator in parentheses ensures that both cities must also match the state of New York on a record-by-record basis.

The second query returns all Albany customers

Figure 1-4. The second query returns all Albany customers

Discussion

OR is applied amongst records; AND is applied across fields. What does this mean? Figure 1-5 shows the tblCustomers table that is used as the example in this recipe. The OR operation involves evaluating the value in a particular field in each record. A single record cannot contain both Albany and Yonkers in its City field; it can contain at most one of those values. So, searching for customers in Albany or Yonkers requires looking for these values in the City field of each record (or, in our example, at least those records in which the state is New York). Thought of another way, when using OR, you can apply the statement multiple times to the same field. For example:

	City="Albany" OR City="Syracuse" Or City="Yonkers"

The AND operator, however, is not used on the same field. A SQL condition like this:

	City="Albany" AND City="Yonkers"
Each customer is in a single city

Figure 1-5. Each customer is in a single city

would make no sense. No records can be returned because there cannot be any records in which the single City field holds two values. Instead, AND is applied to pull together the values of two or more fields, as in:

	State="New York" AND City="Yonkers"

The query grid in Access is flexible enough to handle any combination of OR and AND operators. Figure 1-6 shows how the grid is used to return customer records from New York where the customer type is Retail or Wholesale, as well as customer records from Florida where the customer type is Internet or Mail Order. Internet and Mail Order customers from New York will not be returned, nor will Retail or Wholesale customers from Florida.

Along a single Criteria row, all of the conditions set in the different fields must be met (i.e., this is an AND operation). The SQL statement Access generates bears this out:

	SELECT [FirstName] & " " & [LastName] AS Customer,
	City, State, CustomerType
	FROM tblCustomers
	WHERE
	(((State)="NY") AND
	((CustomerType)="Retail" Or (CustomerType)="Wholesale"))
	OR
	(((State)="FL") AND
	((CustomerType)="Mail Order" Or (CustomerType)="Internet"))
	ORDER BY tblCustomers.CustomerType;
Applying AND and OR in the query grid

Figure 1-6. Applying AND and OR in the query grid

As you can see, the SQL condition for NY is followed by AND to get Retail and Wholesale customers from that state.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required