Working with Criteria in Queries

Since we worked with queries in both Access: The Basics and Access: Structuring & Relating Data, we will start by editing some existing queries. When we are revising these queries, we want to be sure that we don't save over the query we are starting with, so before we start working with criteria, let's make it easier to save queries under a different name.

Adding the Save As Command to the Quick Access Toolbar

To make saving queries under a new name easier, we will add the Save As command to the Quick Access toolbar.

NOTE: If you worked through the previous Access workshop materials, the Save As command may already be on the Quick Access toolbar. If so, skip to the next section.

Step1. To view available commands, on the Quick Access toolbar,

Click, Click More Commands...

Step2. To find the Save As command, in the pane on the left, if necessary,

scroll through the list until Save As is visible

Step3. To add the Save As command,

Double-Click the Save As icon

Step4. To close the dialog box,

Click

The Save As icon is now available on the Quick Access toolbar.

Saving an Existing Query Under a New Name

Now that we have the Save As button on the Quick Access toolbar, let's use it to save the query under a new name.

Step1. To open an existing query,

Double-Click qrySalariesByRank>70,000Hired2000OrLater

We see 34 records. The current criteria limits the returned records to faculty who are making more than $70,000 and were hired on or after January 1, 2000.

We will switch into Design View before we save it under a new name, because there is currently a bug in Access that sometimes causes problems if you attempt to save it with a different name from Datasheet View. When this happens, the name of the query will change on the tab but the new query won't appear in the object list. When you try to switch to Design View, you see an error message similar to this:

2. To switch to Design View,

Click

Step3. To save the query, on the Quick Access toolbar,

Click

We see the Save As dialog box.

Step4. To name the query, in the Save As dialog box, type:

qrySalary>70,000or<40,000Hired2000orLater, Click

The query is now saved under the new name. Next we will make changes to the query.

Using And Criteria and Or Criteria

We want to alter the query to also see faculty who are making under $40,000 who were hired on or after January 1.

The criteria currently looks like:

In Access: The Basics, we learned that when we put the criteria in the same row, we are creating an AND query, meaning that both conditions must be true for records to be returned.

In Access: Structuring & Relating Data, we learned that we can use the "or" row to include additional criteria. If we put the <40000 in the Salary field "or" row, we would be creating an OR query that displays people making less than $40,000 regardless of hire date. While we could add the hire date to that second row, there is another way we can create the OR criteria we want in a single row.

We want to combine the criteria for the salary, so we find faculty making more than $70,000 or less than $40,000. In addition to using the grid to create an AND query and an OR query, we can also use the logical operators AND and OR. Logical operators are operators whose results are either true or false.

Step1. To alter the criteria,

Click after ">70000", type: or <40000 Enter key

Since both the criteria for salary are on the same row, the criteria for the date in the HireDate field will apply to both faculty making less than $40,000 or more than $70,000.

Step2. To view the results,

Click

You see a list of faculty earning less than $40,000 or more than $70,000 who were hired on 1/1/2001 or later.

Step3. To save the changes, press:

Control key + S

Step4. To close the query,

Right-Click the query tab, Click Close

NOTE: You can also close a query with the keyboard shortcut Control key + W.

Using NOT

Sometimes it's easier to describe what we want to exclude from our results rather than what we want to include. The NOT logical operator allows us to do this.

Let's revise an existing query to see how NOT works.

Step1. To open an existing query,

Double-Click qryCoursesAndSectionsSummer18

We see 49 records.

Step2. To switch to Design View,

Click

The query currently selects CourseCode, SectionNumber and CourseTitle and SemesterYear. The criteria for SemesterYear limits the records returned to SU18.

Before we make changes to our query, let's save it under a different name so we don't accidentally overwrite the query.

Step3. To save the query, on the Quick Access toolbar,

Click

The Save As dialog box appears.

Step4. To name the query, type:

qryCoursesAndSectionsNotSU18 Enter key

Step5. To position the cursor, in the SemesterYear Column,

Click before "SU18"

NOTE: If you have trouble doing this, you can also Click in the SemesterYear criteria row and then press the Home key.

Step6. To alter the criteria, type:

Not Enter key

When this query was only showing courses in the SU18 semester, we didn't need to see SemesterYear column. Now that we will be seeing courses in multiple semesters, we want to include SemesterYear in the results.

Step7. To show the field, in the SemesterYear field,

Click the Show checkbox

The criteria in the grid now is:

8. To view the query,

Click

We now see the 178 courses that are not occurring in the SU18 semester. They're sorted by semester and section number since that is the underlying order in the source data.

Step9. To save the changes, press:

Control key + S

Step10. Close the query.

Using Wildcards to Define Criteria

Access allows us to use wildcard characters when we want criteria to be more flexible. The wildcard character in criteria means "anything can go here." The wildcard character is the asterisk (*).

Let's open another query that was created previously and edit it to use wildcards.

Step1. To open the query,

Double-Click qryDeptCoursesFA18NotOnWednesday

To save us time, this query has been partially created. Currently it allows us to view the courses for each department. We want to alter this query to only show classes in the Fall 2018 semester that don't meet on Wednesdays.

Right now the query results show 227 records with information about the Department Name, Semester Year, Section Number, Course Code, Course Title, Meeting Day, and Meeting Time of the courses.

In the previous workshops and with the queries we have created up to this point, when we wanted to look for an exact match on one field, we simply typed what we were looking for in the Criteria part of the column.

Step2. To go to Design View,

Click

To start with, we would like to see only classes that are in the FA18 semester and meet on a Monday.

Step3. To enter the criteria, in the SemesterYear Criteria field, type:

FA18 Enter key

This is similar to the criteria we have entered previously. Let's use the wildcard to add criteria.

Step4. To enter the new criteria, in the MeetingDay Criteria field, type:

m* Enter key

NOTE: The criteria is not case sensitive.

You should see:

Notice that the criteria looks different than what you entered. Access added the keyword Like and the quotes around the m* to convert the criteria to official Access syntax.

Step5. To view the query,

Click

We see the 50 courses in the Fall 2018 semester that have a meeting on a Monday. Note that some meet Mondays and Wednesdays, some meet Mondays, Wednesdays, and Fridays, and others meet on Monday only, but they all have a Monday meeting.

What if we wanted to see the classes that didn't meet on a Monday? Earlier we learned about the NOT operator. Let's combine the NOT operator with the existing criteria.

Step6. To return to Design View,

Click

Step7. To position the cursor,

Click to the left of Like

Step8. To change the criteria, type:

Not Spacebar Enter key

You now see Not Like "m*" in the Criteria field under MeetingDay.

Step9. To view the query,

Click

There are 39 sections of classes that don't meet on a Monday.

Step10. To return to Design View,

Click

What if we want to see the classes that don't meet on Wednesdays? Values of Meeting Day for classes that have a meeting on Wednesday include the following:

Notice that the "W" occurs in different positions. To exclude all courses that have a meeting on Wednesdays, we need to use the wildcard twice: once at the beginning to indicate there can be text before the "W", and a second time to indicate there can be text after the "W."

Step11. To select the current criteria for Meeting Day,

Press & Drag across Like "m*"

Step12. To change the criteria, type:

*w* Enter key

Step13. To view the query,

Click

We see the 39 courses in the Fall 2018 semester that do not meet on a Wednesday.

Step14. To save the changes, press:

Control key + S

Next, we will learn how to adjust the display of the results.