Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Often your queries will need to have more than one criterion. Here's how to create And and Or queries. If you need to pull only the records that satisfied two or more criteria, use what's called an And query. For example, we want to see which employees were hired before 2003 and are still with the company. Click on the Create tab and on Query Design, add the SalesReps table, close Show Table and pull the bottom down.
Double-click on LastName, FirstName, scroll down, StartDate and EndDate. In the criteria line under StartDate type <1/1/2003. When you press Tab or click off of it, Access will put pound signs before and after to indicate a date. In the criteria line under EndDate, type Is Null. This is database terminology for Is Blank.
If you wanted matches that were not empty, you would enter Is Not Null. By entering both is criterion on the same row, you're indicating that both must be true for the result to appear. Click on View, and you'll see that you have four sales reps that have been with you for a very long time. Close this query and save it as Longest Employed. Then click OK. Our next type of query is an Or query.
This allows you to pull records that meet either one criterion or another. This works the same way, but instead of putting all the criteria on the same line, we will put them on lines below each other. Let's make another query that will pull sales reps who either have extremely high salaries or extremely low ones. Create a query by clicking on the Create tab, then on Query Design add the SalesReps table and click Close. Double-click on LastName and FirstName and scroll down to double-click on Salary.
Sort by LastName > Ascending. On the First Salary criteria line, enter <=10. We don't need any dollar signs or decimals. Now on the line directly below, it says "or," and type in >=30. View it, and now you get a list of employees with unusual salaries. So you can follow up and find out why. Close this query and save it as High/Low Salaries.
If you're wondering whether to use And or Or to get your desired result, remember that Or gives you more results. In an And, the record has to meet more than one standard, so fewer will be chosen. In an Or query, the record can meet two different standards. So it has a better chance of qualifying. Or gives you more. Together And/Or queries make it possible to analyze your data using multiple criteria.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98519 Viewers
80 Video lessons · 141524 Viewers
59 Video lessons · 59886 Viewers
52 Video lessons · 73077 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.