One of the most common uses of queries is to filter data within a database down to a specific subset based upon a single common attribute. In this movie, Adam will demonstrate how to make use of the Criteria selector. Further refinements can be made by combining multiple criteria with the logical operators "and," "or," and "not."
- [Voiceover] One of the most common uses of Queries is to filter data within your database down to a specific subset based off of a single common attribute. To do this we'll make use of the Criteria Selector. There are many different types of criteria that we can define. We can specify an exact match, like a specific product or an employee's name, or we can define a group of values, such as all of the products in a specific department. The criteria field is very flexible and Access provides some additional help when writing them to ensure that our syntax is correct. Let's see this in action by creating a new Query in Design View.
And here I want to create a Query that asks which customers live in Florida. To do that I'm gonna pull information out of my Customers table. So I'll double-click on it, and then close the Show Table window. From the Customers table, I'll choose the CustomerID, the FirstName, the LastName, and then I'll scroll down and double-click on State to add that all to the grid below. At this point if I run my Query, I'll just see all the different customers in my table, and, again, I have a thousand different customers in my data table, and that's what I'm seeing here in the Query results. If I wanted to I'd come up here to the State column, and start providing some sort of text filters by using these text boxes here, but I want to hard code this into the Query design.
But I'm gonna switch back into Design View using this button here on the Home tab. The row that I want to focus on in the Design Grid is this Criteria row right here. I'm gonna come over here into the State's column and I'm just gonna type in an FL which is this two letter abbreviation for Florida. When I press Enter, you'll notice that Access adds in the double quotation marks, and since we're matching an exact text string this is the syntax that we really need to be typing in. Now I tend to be a little bit lazy about typing in the quotation marks, and here and there, that's going to get me into trouble when I'm trying to do one thing, and Access thinks that I'm doing something else.
So I'd encourage you to get in the habit of typing in these quotation marks on your own. Now let's go ahead and run our Query, and we'll see the results. Here we can see that I have a total of 78 customers that live in the State of Florida. What happens if I add in two criteria onto the same row? Back in Design View, I'm gonna come over here to the Criteria row underneath the FirstName field. In this case, I want to find all of my customers that have the FirstName of "nita" that live in the State of Florida. As long as I place these on the same row here, Access is gonna treat this as an AND statement.
It's gonna find all the customers that have the FirstName of "nita" and that live in the State of Florida. Let's go ahead and run this here, you'll see that I have two "nita"s in Florida. Nita Snow and Nita Farley. Back in Design View you'll notice that the line below says OR. If I place two criteria on alternating lines, one on the Criteria row and one on the OR row, Access treats those as an OR statement. I'll go ahead and select this and press CTRL-x to cut it to my clipboard, then I'll come down to the OR row underneath FirstName and press CTRL-v to paste it back in.
Now the Query is going to find all the customers that live in the State of Florida or all of the customers that have the FirstName of "nita". Let's go ahead and run this now, and we'll see all the different people that live in Florida, and if I scroll down the list you might find somebody, for instance this one right here, whose FirstName is "nita", but lives in New York. Let's go back into Design View again. And we saw that the line below Criteria says OR, but you can think of all the different lines below this that don't have any name as saying OR as well. So this is going to be OR, OR, OR all the way down. Now if we could try mixing up our criterias here, putting them on different rows.
Let's go ahead and get this back to the clipboard, and put it back on this row right here. What if I wanted to find all of the "nita"s that lived in Florida and New York? Come over here and type in New York on the line below. Now what happens when I run it? I'll see all the different customers that live in the State of New York regardless of what their FirstName is. And if we scroll down the list we'll find all of the "nita"s that live in Florida, and here's one right here. Once again I'll go back into Design View, and if I really wanted to run this Query and just find the "nita"s that live in Florida or New York. I would have to put "nita" on both lines, just like this. When I do it like this we'll see the results that I originally expected.
Just the three "nita"s that we have in our database in Florida or New York. Once again, I'll go back into Design View, and we'll get rid of some of these. Now another way that I can do this is to put the Florida and New York on a single line. To do this, I'm gonna use an operator called OR. I was gonna type in the word OR and then, in quotation marks, I'll type in NY. Now what I'm finding is all of the customers who have a FirstName of "nita" and that live in the State of Florida or New York. Once again, I'll run it and I'll see the exact same results we had a moment ago. Now the word OR here is something called a logical operator.
There are two other logical operators, the word NOT and the word AND. You can use these keywords when filling in your Query criteria on the same row, but sometimes you might not get the results you were expecting. Often times people mix up the word OR and AND. For instance, if you're thinking I want to find all the "nita"s that live in Florida and New York, you might just type it in like that, but if I go ahead and press run you might be surprised at the results. You notice we don't get any records at all. And this is because Access is being very literal. It's trying to find all of the "nita"s that live in Florida and New York at the same time.
And obviously that doesn't exist in our database which is why we're getting zero results. We'll see how we can use AND later on when we talk about numerical criteria, but I can use the word NOT. For instance, if I select this out, get rid of that, and we'll get rid of this extra quotation mark, I'll come at the beginning and I'm gonna type in the keyword NOT. In this case I want to find all of the "nita"s that are NOT in Florida. Let's go ahead and say run, and we'll find just the one that lives in New York. So let's put all of this to use and create a Query that finds all the customers that live in the New England states. I'm gonna come over here and once I delete all the text, find the State column and the Criteria row, and we'll start typing in the criteria for the New England states.
The first one is Maine, which is the abbreviation ME, and I'll come down to the next row and type in NH, which is the abbreviation for New Hampshire, and notice that Access isn't really case-sensitive here, so I could have left this H as a lowercase and that would have been just fine, but I'm gonna change it here just to be consistent. The next State is Vermont, so I'll type in a VT, and notice also that Access is putting in those quotation marks for me here. Let's go ahead and scroll down a little bit. The next State is Massachusetts, MA. And notice if I don't type in the quotation marks here, Access is thinking that I'm trying to type in the name of the function called MAX.
If I was just to press Enter, Access will actually just make that the function MAX which is not what I want. So this is an instance when I actually need to type in the quotation marks on my own. So I'll type in the double quote, MA, and closing quote to finish up that one. The next State is Rhode Island and once again if I don't type in the quotation marks it's gonna think I'm trying to type in the function name. So I'll backspace off of that and manually put in the quotation marks. RI for Rhode Island. And finally the last one is CT for Connecticut. Let's go ahead and run this. And we'll see that we have a total of 28 customers that live in a New England State.
So that's one way that we could create this Query. Let's go back up and apply what we just learned though to see how we could do this a slightly different way. Let's go ahead and get rid of all these here, on these different OR rows. Go ahead and scroll up a little bit. Get rid of New Hampshire. And come back up here on the row that says Criteria and blank that out as well. We saw that we could use the OR logical operator in order to put all of these on a single row, but you'll notice though that I'm gonna run out of space really quick. One thing that I could do is right-click in the cell and choose Zoom. That's gonna create a new window here that'll give me plenty of space to type.
In fact, I can even change the font size here so it's even easier to read. I'll change it to a 14 point font and we'll see that as I start typing it'll be a lot bigger. So I'll go ahead and type this out. It'll say "ME" or "NH" or "VT" or "MA" or "RI" or "CT", and all of those get put into quotation marks. Let's go ahead and say OK to that. And Access drops all of that text down here even though we can't see it all. I can make this column wider if I really wanted to, but it's all there. Gonna press Run and we'll see that we get the exact same number of results we saw earlier.
Finally there's one other way that we can do this criteria. Once again, I'm gonna right-click here and choose Zoom to get back to that Zoom window. One of the things that we can do instead of saying OR is to use a function called IN. So the function'll say IN and then inside of parentheses we'll have the abbreviations for Maine, New Hampshire, Vermont, Massachusetts, Rhode Island, and Connecticut, all separated by commas. Go ahead and say OK to that and we'll run it one more time. We'll see we get the exact same results that we were getting previously. Let's go ahead and save this Query. I'll press CTRL-s on the keyboard, and I'm gonna type in the name of Customers hyphen New England.
I'll say OK to that and it gets added in here to the Navigation pane. So that's one way we can use Queries to filter our data down to specific records that we're interested in based off of some text based criteria. The Criteria and OR rows will allow you to find exact text strings that are present in the database and returns only the relevant records. Remember to use the OR, AND, and NOT logical operators where it makes sense to be even more specific about the results that you're after.
- Creating a query with the wizard
- Defining query criteria
- Using comparison and wildcards in criteria
- Working with joins
- Creating parameter queries
- Using the built-in functions in Access
- Summarizing data
- Aggregating records with totals
- Working with dates
- Creating alternative queries: unmatched, crosstab, and more
- Writing queries with SQL