When writing queries in Access, it is important to know where to place the criteria for the queries, as placing the criteria in the wrong place can drastically change the outcome. Although there are many ways to write criteria in Access queries, our focus will be on the ‘AND’ and ‘OR’ expressions, and where to place these expressions in queries to get the proper outcomes.
AND – When using AND, it is important to understand that the outcome of the query will give fewer answers. This is because AND requires that all criteria are true, in order to be displayed.
OR – When using OR, more answers will populate, as OR only requires that any one criteria is true, in order to be displayed.
Queries can be created from scratch by the user or by using the Query Wizard. Although we are going to work with a query from scratch, any query can be altered in the Design View of the query. Two lines in the query design mode decide which expression is being used in a query. The ‘AND’ and ‘OR’ expressions can be written directly into the criteria, when necessary, but simple queries do not require it.
Using Diagram 1.1 above, we note there are four areas we can alter in each column for our query. We will focus on the Criteria and OR rows. If we type criteria in the Criteria sections, AND is represented. If we type criteria in the OR section, then OR is represented. See examples of this below in Diagram 1.2 (AND), Diagram 1.3 (OR), and Diagram 1.4 (combination of ‘AND’ and ‘OR’).
In Diagram 1.2, the query will pull any record that has a first name of Ken and a last name beginning with B.
Diagram 1.3 will pull any record that has a first name of Ken or a company name starting with Hal. This gives us records with just Ken, companies just beginning with HAL, and a combination of the two criteria.
In Diagram 1.4, we use a combination of criteria, which will give us any records with the first name of Ken or Susan, with a last name that begins with B.
Queries take a little practice, and some trial and error, but using the right criteria can ensure that outcomes are very accurate.
