Building queries in Design view - Microsoft Access Tutorial

- The Query Designer View is a very powerful tool to have at your disposal, and its layout makes it easy to find some answers to some very complex questions. The easiest way to begin exploring the Query Design environment is to take a look at an existing query to see how it was put together. We'll use this Check In Details Query that we created with the Wizard in the last movie, in order to get us started. Let's go ahead and open it up in Design View, and just like our tables, I can right click on it and jump straight into Design View in the flyout menu here. In Design View you'll notice that we have a couple of different sections to our screen. The upper half of the screen is kind of like a mini relationships skew, it allows us to see the different tables that are involved in our query here, so in this case we've got the Guests table, and we have the RoomAssignments table here. And in the bottom half of the screen is devoted to defining the different columns that are going to be returned in our query results. So in this case I've got a column for a First Name, the Last Name field, the Phone Number field, the Check In Date, and the Room IDs. Below the field names we can see which table those are coming from, so we can see First Name, Last Name, and Phone are all coming from the Guests table, and Check In Date and Room ID are both coming from the RoomAssignments table. So let's go ahead and recreate this query from scratch, using the Design View. Let's go ahead and close it out. I'll go over here and press this X button in the upper part of the window. And then to create a new query in Design View we'll hit that Create tab and then we'll come over here to the Queries group and go into the Query Design button here. That will start a new blank query here in the background and we're presented with the Show Table window, which is exactly the same window that we saw when we were creating our relationships in the first place. Here I'm going to select a couple of tables, I'm going to double click on Guests to add that there. I'm also going to click on the RoomAssignments, and I can either select it and press Add, down here at the bottom, but I generally prefer just to double click on them. So once I do that, both of those tables are added here into the background, and I can close the Show Table window when I'm done. So there's the two tables we want to pull information from, and just like in the Relationship Screen, we can drag these around based off their headers here, or I can make them a little bit bigger if I want to see all the different fields, by dragging on the edges or corner. And I'll do the same thing to the RoomAssignments there. And just like in the Relationship Screen we can see how these tables are related. So there's a one-to-many relationship between the Guests and the RoomAssignments, which just means that each guest is only going to appear a single time in the Guests table. But each guest can appear multiple times in the RoomAssignments table if they visit the hotel over and over again. Now that we've got our two tables assigned, we can start pulling out the fields from our table to add those into our query. In order to add a field from the table down into the Query grid down below, all you need to do is double click. So I will find the first name in the Guests table and I'll double click on it and it goes into the first column. Another way we can do this is click and drag, so for the Last Name field I will click and drag and drop it into the second column there. Another option is in the Column View down here at the bottom I can click here. I'll get a dropdown list, in which case I could just select it from the list. So maybe the Phone Number we'll pull it out this way. Notice that it fills in both the Phone Number and the table that it came from here, the Guests table. So those are the three fields I wanted to get from my Guests table. I also want to get a couple of fields from the RoomAssignments table, including the Check In Date. And I'll just go through the double click procedure there. We'll do the day of the week to see what day it was when they checked in, and the Room ID to find out which room that they were assigned to. At this point our query is identical to the query that we created through the Wizard. And if we wanted to take a look at its results, I could come over here and switch it into Datasheet View. The other option is to press the Run button, and right now we're creating what's called a Select Query, and in this mode, there is absolutely no difference between switching in the Datasheet View and pressing the Run button here. Now later on we're going to talk about a couple of queries that will make changes to your data tables, including the Make Table, Append, Update, and Delete queries, basically the ones with the exclamation marks on them. So those queries will actually make changes to your data tables and at that point, when we're working with those four types, the Run button will commit the changes to the table and the View Datasheet button will show you what's about to happen. So when we switch into these other Data Types, these two buttons will definitely do something different, but at this point we're just working with a standard Select Query, so either one of these will do the same thing. All we're going to do is view the datasheet, so I'll switch my mode here and we can see the datasheet of our query. So just like before, we can see the first name and last names of each of our guests that came to the hotel. We can see which date they checked in, the day of the week it was, and the room that they were assigned to. And we can go through and if I look at the bottom we have 2249 different check in events and we can see the details about each one of those, if we want to. Let's switch back into Design View to take a little closer look at the Design Grid down below here. Notice that we have a row here that says Sort, and if I click in one of these fields, for instance, right below Last Name I'll click in this row that says Sort. You notice I get a dropdown list and I can sort these fields based off of the last name, either Ascending, or Descending. I'll go ahead and type in Descending here and we'll hard code a Sort into our Query results here. I also have a check box that says Show on it. We can choose to hide certain fields from our query. And you might think, Okay, well if I'm just going to hide it why would I add it in the first place? And that's because we can do things like this where I can sort based off of the last name but then I can not show the last name in the Query Return. So if I do that and then press the View Datasheet button up here, you notice that these are being sorted differently, so I'm no longer seeing Katheryn's name here, Katheryn Reed, who had appeared down with the Rs down below. I'm seeing Judith here at the top. Now unfortunately, I don't know what Judith's last name is. Because I can't see it, I hid that column, but that is why Judith is appearing here at the top now because it's sorting based off of her last name. If I go back into the Design View we can turn that check box back on, run the datasheet again and we can see Judith is Young, here is her last name, so since we're sorting descending, the last numbers in the alphabet appear at the top. So that's why Judith Young is here at the top now. Switch back into Design View here. Another reason why you might want to include a column, but not show it here with that check box, is later on we'll start doing things with filtering criteria and we will start doing some calculations, and you might not want to see the results of all of those calculations in your query results, so that's where that Show check box would come in handy. And down here in the Query grid we could also make changes to the layout of our query. If we wanted to rearrange the columns, all we need to do is select one of the columns, and in order to select it, you would just come up here into the top bar and when you get this black pointing arrow down, give it a click there, it will highlight that whole column, at which point I can click and drag to move these columns around. So for instance, if I wanted to move Last Name after the Phone Number, I could just rearrange it that way. I'll go ahead and click and drag it and put it right back where it started. I can also adjust the widths of our columns, just like I can with our other data tables. If you come to the bar right between these fields here, so between Last Name and Phone Number, for example, and then come up to the top a little bit more, you'll get this icon here, and we can click and drag it left or right, in order to make that column wider or narrower, as needed. Later on when we start putting in some calculations into these fields, it will get pretty long, so you might want to increase the space here so you can read the entire calculation at one time. Let's go ahead and click and just drag it back here. There are some additional commands that we can run up here in the Query Tools tab, which is the contextual tab that opens up when we go into Query Design. And over here we have a Query Setup section, where if we needed to, we can get that Show Table back, if we wanted to add additional tables into our query, we could do it that way. Another way to do it, just like in Relationships, is just to right click anywhere in this blank area, and you can choose Show Table from the pop up there. Also in the Query Setup section we have buttons to Insert Rows, or Delete Rows, or Insert Columns and Delete Columns. So for instance, if I wanted to click here at the Phone Number and then insert a column, that would insert a column before Phone and then everything else gets moved over. Then I could put in another field in here, for instance, maybe Data Delete, I can drag and drop that in there. Notice when I did that though, it actually filled in a new column on its own, so I actually didn't need the blank column here, if I was just going to drag and drop. But if I wanted to add in a field by using this menu here, that's when you would go ahead and add Insert Column using the button up here on the ribbon. At this point I don't want this column, so I'll select it and I can come up here and select Delete Columns here. Let's go ahead and also delete this day of the week one here. I'll select it and press Delete Columns, and we'll talk about some of these other options here, specifically the Return, and the Totals option. We'll talk about those later on in the course. One final thing before we go, just like in the Relationship Screen, the scrollbars here tend to be a little bit tricky for people. Notice again that you have a scrollbar here for the upper portion where your table design shows up. We also have vertical scrollbar over here. And then we also have another horizontal scrollbar for our table grid down here. And I've seen lots of people accidentally click on one of these, or click on both of them maybe, and it looks like your entire query has been wiped out, you don't see anything here in the grid, and you don't see any tables up here. So you really need to pay attention to where these scrollbars are, it's really easy to accidentally scroll your information right off the screen. So if we just drag that back to the beginning you'll see our tables. And if I just drag this one, which is way over here, all the way back to the beginning, you will see the fields that will appear in our query, as well. We're going to continue working in the Design View throughout this chapter, and there's lots of options to explore and some really cool ways that we can organize our data. But they will all start with this basic model. We'll start with the tables to pull the information from up top and then the individual fields that will be combined into the final datasheet down below.