As with many of the objects in Access, the easiest way to get started with queries is to have a wizard walk through the steps in the creation process. Learn how to pull fields from multiple data tables in order to reveal basic details in a more convenient format.
- [Instructor] As with many of the objects in Access, the easiest way to get started with queries is to have a wizard walk us through the steps in the creation process. Before we can get started, let's have a goal in mind. Let's first take a look at the existing data tables. And one way that we can get the big picture on what's going on in the database is by going back to the relationship screen. If you remember, we can get to it by going to the Database Tools tab and finding the button here. Now our Guest table is set up with a relationship to the RoomAssignments table which can be found here. This is where we're storing information about the each guests visit to the Landon Hotel.
In the RoomAssignments table we can see their check in date, the room id for the room that they were assigned to, the foreign key, the reference here to the guest that was assigned to the room, the day of the week that was assigned on, and the rate code. Whether it was a weekend rate or a weekday rate for that particular room. This table also has a unique feature in it. It has two different fields here that together serve as the primary key. This is kind of an advanced technique, but the rationale behind it follows the same rules that we've seen before. The primary key serves as a unique identifier for the data and with both the check in date and room number serving to jointly define a unique pairing, this creates a situation where access won't allow us to double book a room by assigning it to two different people on the same day.
If you want to see the data that's inside of this table, go ahead and just open it up in the navigation pane here by double clicking on it. And here you can see we have our check in dates, the room numbers, a reference to the guest that was checked in, the day of the week that this day is, and the rate code is here. Now if I wanted to quickly see the name and phone number of our guests along with the dates of their visit I'd have to flip back and forth between this table and with the guest id find it over here in the guest table. This is the perfect opportunity to leverage a query to help us out to see all that data at once. Let's go ahead and close both of these tabs. I'll come over to the far right and press the x button over here twice.
Then, we'll go over to the create tab and in the queries group, I'll click on the Query Wizard button. That'll start up a new query window where we can choose from a Simple Query, a Crosstab Query, or two specialized queries. One that finds duplicate data and one that finds unmatched data. We're gonna stick with the simple query for now. Go ahead and select it and press OK. On this screen we get to choose which fields we want to see in our query results. We have a drop down menu here that lists all the different tables that appear in our database. First let's start with the Guest table. Go ahead and select that. Then we have a selection that shows me all the different fields that appear in that table.
What I need to do is move them from the Available Fields side here on the left to the Selected Fields side over here on the right. We have a couple buttons here in the middle where we can move fields over. If I click this button it will just move a single one over. If I click on this one, it will move everything over. And then I can move individual fields back or all of them back. So from the Guest table I want to see the first and last name for my guest. So I'll go ahead and select this one. Go ahead and move that over. And we'll choose LastName and move that over as well. I also wanted to see the phone number. So let's make sure we grab that and move it over. So those are all the details from the Guest table that I want to see, but I also want to get details out of that RoomAssignments table.
So let's go ahead and select that one in the drop down menu. From here, I wanted to see the CheckInDate, the DayOfWeek, and the RoomID that they were assigned to. With all of my fields chosen, go ahead and press the Next button. On this screen we get to determine whether we want to see Details, which is all of the fields in every record or a Summary, which will summarize our data. I want to go ahead and see all the details. So make sure that this option is selected and press Next. Finally, we get to name our query and this is what's going to appear over here in the navigation pane when it gets saved. Instead of Guest Query which is a little bit generic, I'm gonna call it, CheckIn Details.
On the bottom we have the option to open the query to view the information or we can jump straight into design view to modify our query further. Let's go ahead and take a look at what that information looks like first. I'll check this option and press Finish. That will pull up the record set results for our query and we can see that Access has gone through both tables and matched the name and phone number from the Guest table with each of the records in the RoomAssignments table. So we can see all of the details at once. Here we have the first and last name, the phone number, as well as the check in date, day of week, and room id that were coming out of the RoomAssignments table. And just from this quick little look at the table we can see that Katherine Reid has checked into the hotel quite a number of times.
Here's all of her records and we can see her complete check in history from May 2018 through some future booking in January of 2020. So the query wizard can help you get quickly up to speed constructing basic queries. For even more control and options though, we need to take a look at the Query Design environment and we're gonna do that in the next movie.
- 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.