Microsoft Access queries really become a powerful tool with the addition of filtering criteria. With the criteria row in the query column grid, users can focus in on just the records of interest. Also, learn how to filter records across multiple columns of data.
- [Instructor] In addition to gathering and displaying records from data tables, we can add filtering constraints to the query design grid to only display the exact records that we're interested in seeing. Let's see how this works by exploring the data that we've gathered for the Landon Hotel's guests. We're gonna start a new query here in Design view, go to the Create tab and click on Query Design. For this query, we're only interested in data from the Guests table, so I'll go ahead and double click on that to add it into our grid and then I'll go ahead and close the Show Table window. From the Guests table I wanna add in the fields for the first name of the guest. I'll go ahead and double click on that.
The last name for the guest, the phone number, country and finally the state that they live in. At any point in time, we can go ahead and view our data sheet to see the results and this is exactly being pulled straight out of the Guests table, so we're not filtering it or anything. So, it's just the record straight out of my Guests table. Let's switch back into Design view here and start taking a look at this new row that we have down here called Criteria. This is where we're gonna put some filtering constraints and I'll come over here underneath the column for state. And if I only wanted to see the guests that live in the State of Washington, what I could do is type in the two letter state abbreviation for Washington which is WA and press enter.
Access helps us out here, it actually wraps it in double quotation marks, which, I could have typed in on my own but Access helped us out there. This is because we're literally looking for the text WA in the State column and Access is only gonna display the records where that exists. Let's go ahead and switch our Data Sheet view here. Now we can see that we have a total of five records for the guests that live in the State of Washington. Keep in mind that you can take a look down here in this box and this will tell you exactly how many records you have in the results, so it's an easy ways to look in order to get that detail. Let's go back into Design view, and we'll change this from WA to CA to take a look at our guests that live in the State of California.
Once again, I'll switch my view into Data Sheet view and I can see that I have a total of 14 guests that live in California. What happens if we specify a criteria that doesn't exist in the database? Let's go ahead and switch back into Design view, and I'll come down to the state and I'm just gonna click here and press backspace a few times to delete that out of the state record. I'm gonna come over here to the Country field and I'm gonna type in the country of Europe. Now, Europe actually isn't a country, so this actually doesn't make much sense. But when we run the data sheet you might be actually surprised at the results. We're not getting any errors here. We're just getting a blank result set.
If you think about what Access is telling us here, this actually makes perfect sense. We're asking our database to show us all the guests that are in the country of Europe. Well, here they are. They don't have it, either they don't exist, so Access shows us zero matches. There's nothing wrong with the question at all. It's just that this isn't the correct answer to the question that we ended up asking. Let's go and switch back into Design view. I'm gonna come down to the country section and delete that. This time, I'm gonna change it here I'll type in the double quotes on my own this time. And I'll type in United States of America.
I'll finish that with a closing quote and press enter. Notice that this actually runs off the screen here. And so, if I wanna get a little bit more space in each of these cells. I can come up here to the very top. And right between these two columns, I'll get this double-headed arrow. Then click and drag to make that open just a little bit more. Now we can read the full criteria that we're filtering this column to. This time, I'll run the data sheet one more time and we'll see that I have a total of 102 guests that live in the United States of America. At this point, we can go ahead and save our query here, I'll press control S on the keyboard or you can press the disk icon here in the Quick Access Toolbar.
I'm gonna call this Guests_UnitedStates. I'll press enter and that query gets saved here inside of our navigation pane. I'm gonna make one small change to this. I'll switch back into design view. Come down to the country section and instead of United States this time, I'm gonna type in China. Once again, I'll view it to take a look at the results. I have a total of 19 guests from China. Now if I wanna save this query again, it would actually just overwrite this one that's called Guests_UnitedStates, which could be a little bit confusing because if you run the query, it's gonna return China results.
So, instead what we can do is come up to File, go down to Save As, click on Save Object As and finally click the Save As button. This will allow us to create a copy of that with a new name. And instead of copy of Guest_UnitedStates, I'm gonna call this Guests_China. I'll press OK and now I have two different queries here saved in my navigation pane. Let's go ahead and close this out. Now, anytime I wanna see which guests live in the United States, I can run this query and easily get all those information. Or I can click on Guests_China and easily see these folks.
So, the criteria row of the Query Design view allows you to quickly and easily filter your data. Unlike adding filters directly to your data tables, you can save your queries in any number of configurations and have lots of different filters applied to the same data.
- Determine the essential uses for the Trust Center.
- Explore the functions of the database Navigation pane.
- Recognize the fundamentals of entering data when using Access.
- Identify the necessary steps when importing a table when using Access.
- Break down the fundamentals of filtering and sorting table data in Access.
- Identify the method utilized when building queries in Design view.
- Determine the role of forms in Access.
- Examine all of the elements involved in maintaining a database in Access.
- Explore how to properly protect an Access database with a password.