Filtering with mathematical comparisons

- [Narrator] When it comes to filtering your data, there's one additional class of operators, that is specifically used with numerical values. They're called comparison operators and are useful for all those situations when you want records returned that are above, or below, a specified numerical value. Let's put those to use, and take a look at what our guests are spending per night with a new query. Once again we'll come up to the Query tab, and start with a query design window. This query is going to involve the guests table, the room assignments table, and, finally, the room rates table, this is where we're going to get some rate information.

We're going to close the show table window, and we're going to choose the guest table, we're going to have the first name, so double-click there, last name, as well as check-in date, from the room assignments table, the room ID that they were checked in to, and, finally, we can get the room rates table and we'll find the rate value. As a sanity check, let's go head and just view our data sheet to see what our starting values are here. And we can see all 2249 check-in details for all of our guests. Let's start filtering this data down back in design view. Now you're probably already familiar with what the comparison operators are, even if you don't know them by that name.

I'm going to come down into the criteria row here, underneath rate, and I'm just going to right click and go into our zoom box, so we can take a look at a larger view of it, and I'll go head and change the font up to 18, so we can see what we're doing. So, our comparison operators are going to be the less than symbol, the greater than symbol, we can say equals to, we can also say less than and equal to, just by typing them both together, we can also say greater than or equal to, again by typing those two symbols together, and finally, we can say not equal to by literally typing less than, followed immediately by a greater than symbol here.

So this is how we would specify not equal to. So those are our comparison operators, let's start putting them to use. I'm going to press cancel here, and come back down into our criteria row underneath rate. So if I wanted to see just the visits that were at or above $200, underneath rate, I would type greater than or equal to 200. When I view the data sheet, we'll see our filtered list here, includes 508 records where the customer spent more than $200 on their room. We'll go back into design view and change it again. We can also specify ranges by combining a comparison operator with the logical and operator.

So if I wanted to see everybody that spent between $80 and $100, I can say greater than and equal to 80, and less than or equal to 100. I'll view the data sheet again, and this time we can see a total of, down here at the bottom after our calculation, 313 guests that fit that criteria. Once again, we'll go back into design view, and we'll take a look at using a comparison operator with a date. If I wanted to see all the guests that had a room rate of between $80 and $100, that also checked in after December 1st, 2018, I can use our comparison operators in a check-in date as we'll.

I'll come over here, and I'll type in greater than or equal to, and then inside of the pound character, to denote a date here, 12 slash one slash 2018. We'll finish that with a closing pound, just to wrap our date on that, and we'll go head and view the data sheet one more time. And now you can see that we have a total of 116 reservations for guests that are going to spend between $80 and $100 on a room, after December 1st, 2018. So when filtering based off of numerical values, you're going to think about specifying values with the comparison operators, that's the less than, greater than, and equal to characters, that make entire ranges of data quick and easy to find.