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

Working with Criteria Using the IN Operator

Problem

Using multiple OR operators in the query grid makes for an unmanageable experience. If too many values and ORs are placed in a grid column, the column may expand to be bigger than the viewable area.

Solution

A way to save space in the query grid is to use the IN operator. IN is used in conjunction with a list of values from which any value can be returned. This essentially means that the IN operator works in the same fashion as the OR operator. It is not required that all conditions be met; meeting one of the conditions suffices.

Here is a SQL statement that returns records for students that took at least one of the listed courses:

	SELECT Students.Student, Student_Grades.Course,
	Student_Grades.Instructor
	FROM Students INNER JOIN Student_Grades ON
	Students.StudentID = Student_Grades.StudentID
	WHERE
	(((Student_Grades.Course)="Beginner Access"))
	OR
	(((Student_Grades.Course)="Beginner Excel"))
	OR
	(((Student_Grades.Course)="Advanced Access"))
	OR
	(((Student_Grades.Course)="Advanced Excel"));

Using IN provides a more streamlined SQL statement. Notice how the WHERE section has shrunk:

	SELECT Students.Student, Student_Grades.Course,
	Student_Grades.Instructor
	FROM Students INNER JOIN Student_Grades ON
	Students.StudentID = Student_Grades.StudentID
	WHERE Student_Grades.Course In
	("Beginner Access","Beginner Excel",
	"Advanced Access","Advanced Excel");

Discussion

The IN operator provides a syntax convenience. It makes it easier to eyeball a set of criteria values to which OR logic is applied. Figure 1-7 shows an example of using IN to return records where the instructor is either Brown or Maxwell.

Using the IN operator to specify the instructor

Figure 1-7. Using the IN operator to specify the instructor

That's simple enough to follow: when the instructor is either Brown or Maxwell, the record is returned. Figure 1-8 shows an example of using IN in two fields.

The example shown in Figure 1-8 returns records in which either Brown or Maxwell taught Beginner Access, Advanced Access, or Intro to VBA. In other words, all combinations of these instructors and courses are returned.

Using the IN operator for both the Instructor and Course fields

Figure 1-8. Using the IN operator for both the Instructor and Course fields

Adding criteria to other fields will further cut down the number of returned records. The next example adds new criteria to the row. The Instructor and Course fields still have IN operators, but now only records that have a MidTerm Grade and a Final Grade of 85 or better are returned. Here is the SQL statement for this query:

	SELECT Student_Grades.Instructor, Student_Grades.Course,
	Students.Student, Student_Grades.[MidTerm Grade],
	Student_Grades.[Final Grade]
	FROM Students INNER JOIN Student_Grades ON
	Students.StudentID = Student_Grades.StudentID
	WHERE (((Student_Grades.Instructor) In
	("Brown","Maxwell")) AND ((Student_Grades.Course) In
	("Beginner Access","Advanced Access","Intro to VBA")) AND
	((Student_Grades.[MidTerm Grade])>=85) AND
	((Student_Grades.[Final Grade])>=85))
	ORDER BY Student_Grades.Course, Students.Student;

The IN operator is handy when using subqueries. A subquery returns a set of records to which the rest of a query can apply further criteria. The following SQL statement returns information for those students who got a 90 or better in either Advanced Access or Advanced Excel and took either Beginner Access or Beginner Excel last year:

	SELECT Student_Grades.Instructor, Student_Grades.Course,
	Students.Student, Student_Grades.[MidTerm Grade],
	Student_Grades.[Final Grade]
	FROM Students INNER JOIN Student_Grades ON
	Students.StudentID = Student_Grades.StudentID
	WHERE (((Student_Grades.Course) In
	("Advanced Access","Advanced Excel")) AND
	((Student_Grades.[Final Grade])>=90) AND
	((Students.StudentID) In
	(Select Stud_ID From LastYear Where
	(Course="Beginner Access") Or (Course="Beginner Excel"))))
	ORDER BY Student_Grades.Course, Students.Student;

The IN operator is applied to the LastYear table through a subquery. Here is the portion of the SQL that does this:

	((Students.StudentID) In
	(Select Stud_ID From LastYear Where
	(Course="Beginner Access") Or (Course="Beginner Excel"))))

The Select statement within the larger SQL statement is where the subquery starts. The subquery returns StudentIDs that have matches in the LastYear table (on the Stud_ID field) for those students who took Beginner Access or Beginner Excel.

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