课程: Access 2016 Essential Training

Creating queries with the Simple Query Wizard

课程: Access 2016 Essential Training

开始一个月试用 购买团队方案

Creating queries with the Simple Query Wizard

“

- As with many of the objects in Access, the easiest way to get started with queries is to have the Wizard walk us through some of the steps. Before we get started, though, let's have a goal in mind. Let's first talk a look at some existing data tables and one of the easier ways to get the big picture on what's going on in the database, is to go back to that Relationships screen, which, if you remember, we can get to from the Database Tools tab on the Ribbon, and then hit Relationships right here. We can see that our Guests table here is set up with a relationship to the RoomAssignments table which is down here. So we have a GuestID number that's being generated for each of our guests, and in the RoomAssignments table we have the check-in date and room that was assigned. And then we have a link to the GuestID that was assigned to that room for that particular day. We also have some details about what day of the week it was, and the rate code that they paid, whether it was a weekend rate or a weekday rate for that particular room. Now, if I want to quickly see the name and phone number of our guests, along with the dates of their visit, I would have to flip back and forth between these two tables. So here, it sounds like we need to create a query to help us out. Let's go ahead and close this Relationships window here, and we can start a new query by going up to the Create tab on the Ribbon, and in the Queries group we're going to hit the Query Wizard button here. That will start up the new Query Wizard, and we have a couple of options here. I'm going to start with the Simple Query Wizard here, and press the OK button. Next we get to choose which tables we want to be involved in our query. So, we have a drop-down list here with all the different tables from our database. I'm going to start with the Guests table, and I'm going to grab the FirstName and move it from the Available Fields side over here to Selected Fields, with this arrow right here at the top. Now incidentally, if I wanted to move all of the fields over I could use this button. You see if I click it they all go over. I can move them all back with this button, or I can move individual fields back with the single button right here. After we move everything back, and once again I'll select FirstName and press the arrow to move that one over. And we'll take LastName as well. And then I'll go down here, I'll click on Phone and add that one, too. So, right now I've got three fields from the Guests table. Let's go ahead and go back up here to the drop-down list, and we'll choose the related table, which was the RoomAssignments table. From RoomAssignments I want to grab the CheckInDate, so we'll add that over to our Selected Fields side. And I also want to see what room they are in, so, there's the RoomID, we can add that one as well. Let's go ahead and press the next button here, and we have two options, we can either see a Detail or a Summary query. We can see a Detail showing every field of every record. Or we can get some summary statistics, by choosing this option here. I'm going to stick with this Detail section here so we can see each of guests and which room they were assigned to. So I'll leave it there and press Next. Finally, we get to give a name to our query here and Guests Query isn't very specific about what this is returning, so I'm going to highlight this here and we're going to name this query CheckInDetails. Finally, we have the option to open the query, to view the information. Or, we can modify the query in Design view. Right now we're just going to check out the information, so we'll leave it at the default option here and press Finish. That will complete the query and it'll open it up, notice that this query does look exactly like a data table. So we've got field names across the top, we have records going down the sides here. We have the title here on the tab. The only thing that's different between this and a data table is the icon here. You'll notice here in the Navigation Pane that queries get this icon with two tables overlapping each other. Whereas a table just has the single table icon here. So in this query we can see lots of information about who checked in and when to our hotel. So I can see that Katherine Reid is all of these records here at the top. And I can click on the first one and then just shift-click on these, so I can see the highlighted section is all pertaining to Katherine Reid here. And we can see her Phone number, we can see the CheckInDates, and see what rooms she was assigned to on each of her visits. So Access has gone through both tables and found matches to the name and phone number with each of the records in the RoomAssignments table. So we can see all of those details at one time. 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'll do that next.

内容