Join Adam Wilbert for an in-depth discussion in this video Defining criteria, part of Access 2013: Queries in Depth.
One of the most common uses of queries is to filter data within your database down to a specific subset based upon a single common attribute. To do this, we will 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 the products in a specific department. The criteria field is very flexible and Access provides some additional help with writing them to ensure that our syntax is correct. Let's go ahead and see this in action by creating a new query in design view.
The question I want to start with is what customers live in the state of Arizona? To do that, I'm just going to double click on the customer's table, and then close the show table window. I'm going to add a few fields for my customer's table up above and drop them in the design grid down below. I'm going to add first name, last name, and I'm going to scroll down and add the state. I'll just double-click on each one to add them to the grid. Let's go ahead and move the grid up a little bit so it's higher on the screen. At this point, I can go ahead and run my query record set. I'll go ahead and press the run button. And this will show me everybody on my database. For instance if I look down here at the bottom, I'll see that I have a thousand records.
These are the thousand customers in my customers' table. I've got their first name, last name, and state. Now I just want to see the customers that are in the state of Arizona. I could provide a text filter here, but I want to hard code this into the query. Let's go back into design view. Now, what I can do is look at this row here that says criteria and underneath the state column, I can put in the states that I'm interested in. In this case, I want to filter my records to just the customers that are in Arizona. So I'll type in the two letter state abbreviation for Arizona, which is AZ. When I press Enter, Access wraps that in quotation marks, which are required for text fields.
Also note that I'm typing in the letters az as lower case letters. I could type them in as upper case, but Access is actually case insensitive in this regard. I'm just going to go ahead and leave it as is and press Run. Now we can see just the customers that are in the state of Arizona. If I look at the bottom down here, I can see that I just have seven customers that live in the state of Arizona. Let's go ahead and go back into Design view. Now, we can add multiple criteria here on different columns. Let's say I want to find all the customers whose first name is Ann that live in the state of Arizona. If I put in Ann here and I press enter, again it wraps it in quotation marks because it's a text string.
Now I have a criteria that says the first name is Ann and the state is Arizona. Let's go ahead and run this. You can see that I have one customer that matches both of these criteria, Ann Roach lives in the state of Arizona. Let's go ahead and switch back to design view. Now right below the word criteria is the word or and you can imagine that every line below that also says or. The or lines allow me to put in additional criteria that don't relate to one another. For instance if I put Ann down here and then remove it from this line here, I'll just select it and press delete. Now I have a statement that ask for the first name is Ann or the state is Arizona.
Let's go ahead and run that and see the difference. Now I can see customers that live in the state or Arizona. For instance, we have Reuben, Aretha, and Quintessa who live in the state of Arizona. But I also have this person, Ann Holt, who lives in Arkansas. I have, Ann Roach, who we got before who lives in Arizona, but I have, Ann Maynard, who lives in Oklahoma. So, now I'm getting customers that either have the name, Ann, or that live in the state of Arizona, or both. Let's go ahead and go back to design view. Well, what if we wanted to see multiple criteria for the same row. For instance, let me get rid of this Ann here on the or line.
And you go back up here and put it on the same line as Arizona, so I'm going to type in Ann, A-N-N. What if I wanted the customers who were named Ann, A-N-N and also spelled their names Anne with an e at the end? What I can do is go in here in this criteria row and just type in the word or, and then after that, I'll type in the second criteria. I can type in A-N-N-E and press enter. Notice that Access capitalized the word or, and then wrapped the second instance of and with the e in quotation marks. Now I have a statement that asks for all of the customers named Ann with no e, or Anne with an e, and that live in the state of Arizona.
Let's go ahead and press run and we'll see that we don't have anybody named Anne with an e in Arizona. Let's go ahead and return back to design view. Let's see what happens if I get rid of the state field here. I'll remove Arizona from the state criteria. Now if I read it, I get all of the Annes in my database, including one Anne Meyer, who lives in Texas that has an e at the end of her name. Okay, let's go back in the design view again and check out something else. Let's say that we wanted to find all our customers that live in the New England state. I'm going to go ahead and clear out my Ann criterias here from the first name and go into the state column here.
I could do this a couple of different ways. First, there are six states that are in the New England region of the United States. I could put in there abbreviations on each of the lines here. For, instance I could say ME for Maine. I can go down to the or line and say NH for New Hampshire. And come down to the next line. And say VT, for Vermont. And going down again, MA for Massachusetts. And notice, when I start typing in MA for Massachusetts, Access thinks that I'm trying to type in max here. And if I press enter, it actually fills in the word max. So, this is a case where I actually need to type in the quotation marks on my own.
So I'm going to Backspace off of this. And this time I'll type in the quotation marks, MA for Massachusetts, and finish off with that closing quote. That'll allow me to type in MA properly. Let's go down to the next line. RI is Rhode Island. And again, it thinks I'm going to type in the word, right. So instead, I need to do the quotation marks on my own. RI ending quote. Let's go down one more line and this time Connecticut, CT, and I'll press enter. So, here's all of my New England states, in this case I'm looking for all of my customers who in live in the state of Maine, or New Hampshire, or Vermont, or Massachusetts, or Rhode Island, or Connecticut and if I run, you'll see the records that's here.
I have 28 customers that live in these states. I can do this another way here if I go into design view, let's go ahead and clear all of these out, I can put them on a single line. In this case, this is going to be quite long, so what I can do is right click and go into a zoom view, that will give me a bigger box where I can type in the values. In this case, I'll just string them together with an or character. I'll type in or, and then in quotation marks, NH or in quotation marks VT, or in quotation marks MA, or RI, or CT.
This will give me the same results. If I say okay it puts everything on a single line. If I read it, we'll see the same 28 customers we got just a moment ago. There's one other way that we can do this. Let's right click on it and go back into that zoom window just so I have more room to write. We can also use a function here called, in. In which case I'm going to write in and then, an open parenthesis. And now I just list out a comma-separated list of all the values. ME, NH, VT, MA, RI, CT.
There's all of my New England states and they're wrapped inside of this in function. If I say okay, once again I run it, I get the same 28 customers. Let's go back into design view. In all of these cases, we have been using exact text matches. In this case, we're looking for all of the states that are exactly ME or NH and so on. We can also use what are called wild card characters. Let me clear out the state line here. It's going to highlight all of that and press delete on my keyboard. Let's say we want to find all the customers who's first name is starting with an A.
I'm going to go ahead and go over to the criteria underneath the first name. In this case, I'm going to type in an A, and then the special wild card character, the asterisk. The asterisk character says any characters and any number of characters. If I go ahead and press enter on this, Access will fill in a syntax for me. In this case, it uses an operator called like and it wraps the A* inside of quotation marks. Let's go ahead and run this. Here I have all of my customers whose first name starts with an A. Again, if I look at the bottom I can see that I have 73 of these. We can use the asterisk characters in multiple places as well.
In this case, we said A* which means an A followed by any characters after it. By switching to design view and see what happens if we move asterisk to the beginning, if I say for instance, like *A, this will mean any customers whose name ends in an A and if I run that, you'll see that result. I can also use two asterisks in the same row. Let's go back into Design View. And if I say *A*, this means an A either at the beginning, in the middle, or at the end. Once again, if I run it, we'll see that every one of these customers, and I have 681 of them, every one of them has an A somewhere in their name.
Let's go back into Design View again. So that's the like asterisk operator. There are additional wild card characters that we can use in addition to the asterisk. For instance, if I get rid of this, I can find all of my customers whose name starts with A and only has four letters in their name. In which case I'll type in A and instead of an asterisk, I'll use the question mark. The question mark stands for any character but only one. So if I type three question marks, that means it starts with an A and has three letters after it. Let's go ahead and run that, and you'll see the nine customers that match that criteria. Let's go back into design view, and we'll try out one more.
Let's go ahead and get rid of this. I can also specify a set of characters. If I wanted to find every customer who's name either started with an A or a B and included four characters, I can write in the A and B set inside of square brackets. So I type in a A, B and then three more questions marks. This says that I want to match any fist who starts with an A and has four characters or starts with a B and has four characters. If I press enter, it's going to give me an error message. This is a case where I actually need to type in the word like, and the quotation marks on my own.
I'm going to write in the work like" and then move to the end and put in a closing quote. Now I can press enter, Access accepts that change. Let's go ahead and run it, and we'll see all of our four letter first name customers who either start with an A, or start with a B. So, we've seen how we can use queries to filter our data down to just the specific records that we're interested in based off of some text based criteria. While many of these operators will also work for numerical values, Access provides some additional tools when defining mathematical operations, so let's talk about those in the next video.
- Defining criteria
- Understanding comparison operators
- Using joins
- Creating parameter queries
- Using Expression Builder to work with functions
- Working with dates and times
- Creating conditional statements
- Finding duplicate records
- Creating backups
- Making, deleting, and appending records
- Understanding SQL basics and writing SQL queries
- Useful query tricks