Viewers: in countries Watching now:
In this course, author Adam Wilbert illustrates how to create and leverage real-world queries and turn raw data into usable information. The course covers setting up queries, performing calculations, using the built-in Access functions to further refine query results, and identifying top performers or areas for improvement based on a range of criteria.
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'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 employee's name or we can define a group of values such as all of the products within 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. So let's see this in action.
We'll go up to the Create tab and we'll create a new query in Design view. Let's go ahead and add our Employees table by double-clicking on it. And close Show Table. Now the query that we want to start with first is which employees live in Arizona. We'll add a couple of fields to our query here. I'm going to open this up we'll add FirstName and LastName and State to our query. Now if I run it right now, we're going to get everybody, no matter what state they live in. We can see that we have a total of 200 records.
Let's go ahead and run back into Design view. For our criteria, we're going to specify that we're only interested in employees that live in the state of Arizona and we can put in the two letter abbreviation for Arizona, AZ. Press Enter to accept that value and Access actually wraps it in quotation marks, which is the proper syntax. Let's go ahead and run this query again and we'll see that we're down to three records with just the employees that live in the state of Arizona. Let's go ahead and switch back into Design view. I can specify multiple criteria by using the same line.
So for instance if I'm interested in all the employees that have a first name of Jennifer that live in the state of Arizona I can put it in like this. FirstName is Jennifer and State is Arizona. If I run this I'll see that I only have one employee that matches both criteria simultaneously. Back in the Design view and let's get rid of Jennifer here and I'll get rid of Arizona. I'm just going to highlight and press Backspace. Let's expand our reach a little bit and now say we're interested in all of the states that begin with the letter A.
We can use the Like operator to use this query. I'll write the word "like" followed by a asterisk. What this is telling Access is that we're interested in all of the states that have an A as the first letter and then any letters after that. Let's go ahead and press Enter to accept that. Access adjusts our statement a little bit, capitalizes the word Like, wraps it in quotation marks. If we run that, we'll see the states of Alaska, Alabama and Arizona are all represented. Let's go back into Design view and I'll highlight this and get rid of it.
The asterisk character is a wild card and it means any character and any number of characters. So for instance, if I was interested in all of the employees that have a W anywhere in their last name I can write that statement this, like *w*. Now I'm asking for any characters, W, and then any characters after that. I'll press Enter and we'll run that query and you'll see that our last names either have a W at the beginning, a W at the middle, or a W at the end.
Let's go ahead and switch back into Design view and I'll get rid of this. Now right below the Criteria line is the word or. Any criteria that you put on multiple lines will be treated as an Or statement. So if I was interested in multiple states, I can say the state of Arizona and on the line below that, I can say New Mexico, 'nm'. Let's go ahead and say Run and you'll see that we have both states there.
Let's go ahead and switch back into Design view and I'll get rid of these. We can also write an or statement on a single criteria line. So I could also have written that same query as 'az' Or 'nm'. And when I press Enter, Access understands that that's Arizona or New Mexico. Again, the same results. Back into Design view, we'll go ahead and get rid of this criteria. Another logical operator in addition to Or is the word Not.
This does exactly what you would expect. It excludes records. So I can say Not 'az' and we would get all of these employees that do not live in the state of Arizona. Now there's a third operator called And. We've already seen how we can include multiple columns here. So for instance, I have the first name of Jennifer in the state of Arizona on one line. Access treated that as an And statement. We're looking for both at the same time. If I were to put in here 'az' And 'ny' thinking that I'm going to get all of the records for the employees that live in Arizona and New York, if I say Run I don't get anything and the reason for that is because Access uses a very literal understanding of the word And.
It is actually looking for all the employees that live in Arizona and New York at the same time. So that's why the proper operator for this statement was Or. We will see And coming up here when we look at mathematical operators. So we have seen how we can use queries to filter out data down to just the specific records or range of records that we are 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 take a look at those in the next movie.
There are currently no FAQs about Access 2010: Queries in Depth.
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.