Incorporating AND and OR statements

- [Instructor] Adding multiple query criteria to filter your records can be accomplished in a couple of different ways. Let's create another query that it further explores our guest data. We'll go into the Create tab and choose Query Design. This time I'm going to pull data out of the Guest table, the Room Assignments table, and the Rooms table. Going to close the Show Table window. From there, I want to see the first and last name of our guest, the check-in dates, and the Room ID that they were assigned to. And finally, from the Rooms Table, I want to see the room name as well as the type of bed that that room includes. Now if we add multiple criteria on the same criteria line, Access is going to treat them as a logical and statement.

Underneath our room name, we're going to type in a criteria of Victoria, this is one of our rooms that we have here at the Landon hotel. If I run this query we'll see that we have lots of Victoria rooms, and there is a bed configuration that includes a king bed and if I scroll down we also see a bed configuration that includes two doubles. If I go back into Design View, I can filter out to just see the two doubles version of that room by coming over here into Bed and on the same criteria row as Victoria, type in two doubles. When I view these results I'll see the 75 check-ins that included a Victoria room with a two double bed configuration.

Go back into Design View. Now underneath the Criteria row you'll also notice a row called or and you can think about all these other rows below that as also saying or. When you put criteria on alternating rows it will treat it as an or statement and we'll get different types of results. Let's go in and remove the two doubles from the Bed and move it down to the line below. Now when I run this query we'll get totally different results. I can see I'm getting 659 records back and it includes all of the people that checked into a room with two double beds, regardless of the type of room that it is.

And if I scroll down we should find some people that checked into a Victoria room, regardless of what type of bed. So we have some king beds here. If I keep scrolling down we should find some people that checked into a Victoria room that had two doubles, and yup here's a whole group of them there. So we can see that this is actually an inclusive or. It's one, or the other, or both. Let's go back into Design View. We can also use the or lines to include two different criteria in the same column. So if I want to see all of the people who checked into a Victoria room and one of our Westminster rooms, I can remove this Bed over here and underneath Victoria on the or line I'll type Westminster Now if I go ahead and do these data sheets we'll see we have all of the guests that checked into either a Westminster room or a Victoria room.

Once again we'll go back into Design View and we'll change it once again. There's a shortcut way of writing this statement out all on a single line. If I go ahead and highlight this Westminster and remove it, I'll come up here to this criteria row and I can actually include the or statement right inline. I'll type in Victoria or Westminster on a single line and once again we'll view the data sheet. And we get exactly the same results as before. Once again we'll go back into Design View and we'll look at one more example. We can also use the logical operator called and.

This mostly makes sense for ranges of data. I'm going to go and take a look at that in our Check-in Date Data. Underneath the criteria for this I want to find all of the people that checked in in the month of May of 2018. I can use a keyword here called between and type in the first date of our range - 5/1/2018. And then I'll use the logical operator and here and type in the last day of the month - 5/31/2018. When I press Enter, Access wraps the dates with the # characters.

Let me just expand this out a little bit more so we can see the whole thing, and these # characters act just like quotation marks around text like we see over here. This is just telling Access that this is in fact a date, and not a formula five divided by one divided by 2018. Now if I run it, we'll take a look and we can see, after a brief calculation here at the bottom, that we have a total of 40 guests that all checked into either a Victoria or a Westminster room during the month of May in 2018. So we did this by making use of multiple criteria on the same line in the Access Query Design View, and mixing in a couple of and and or logical operators.

When dealing with criteria, keep in mind that if they're on the same horizontal row, Access treats them as a single and statement, and if they're on alternate rows, then Access treats them as an or statement.