Build queries in Design view

- [Instructor] The Query Designer is one of the most powerful tools available to you inside of Access, and its layout makes it easy to find the answers to some very complex questions that you might have about your data. One 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're gonna use the check-in details query that we created in the last movie with the Wizard to see exactly what the Wizard did for us in the background, and then what I wanna do is delete that query and then re-construct it from scratch using the Query Design environment to help us out. To get started, let's go ahead and right click on our query here, the check-in details query, and open it straight up into Design View.

Another way you can do it is to double click on it to open it up in Data Sheet View and then come up here to the View button and switch its view into Design View by clicking on the top part of it. The Design environment is split into two different sections. The top portion of the screen up here is kind of like a mini Relationships window. It shows you a view of the tables that are involved in our query. Here we have the representation of the Guest table, and here we have the Room Assignments table. Just like in the Relationships window, we can go ahead and click and drag them around on the screen. We can also make them a little bit bigger by dragging on their borders if we need to in order to see all the different fields inside these tables.

We have a line representing the join between the two tables so I can see that the Guest table is linked to the Room Assignments table based off of this Guest ID value. On the bottom portion of the screen, we have this column grid, and this is where we're going to display all of the different columns that'll appear in the queries record set when we run the query. This tells us that the columns are going to be the first name, last name, and phone number that'll come out of the Guest table, and then we have the check-in date, the day of the week, and the room ID that are all coming out of the Room Assignments table. The layout of this interface can be modified a little bit if you need to.

The dividing line between these two sections is actually movable, so if you move your mouse cursor right into this area here, you'll get a double-headed arrow, and you can click and drag to move this up and down if you need to give yourself some more room to either the top or the bottom. Also notice that we have scroll bars in each section, so here's a horizontal scroll bar for the upper section and a vertical scroll bar, and then we also have a horizontal scroll bar down here for the bottom section. Now, just like with the Relationships screen, these can accidentally get clicked in quite easily, and when you do so, everything just zooms right off the screen. So it's really easy to think that you've accidentally deleted things when in reality everything is just off the screen and you can't see it.

So when you're working with this, just make sure that your scroll bars are always here to the left-hand side and this one over here is all the way to the top. So this is the basic of what a query looks like in Design View. Let's go ahead and delete everything here and we'll re-create this from scratch. We'll go ahead and close this tab. I'm not gonna save any changes that I made to the Design environment. And then we'll come over here to the query in the Navigation pane and I'll right click on it and choose Delete. Access is gonna confirm this delete, and I'll say yes to that. And then we'll come up to the Create tab and we'll go in here to the Queries group, and this time we'll click on the Query Design button.

That'll load up an empty query here and give us this Show Table window where we can start choosing the tables we want to be involved in this query. Just like with Relationships, I'll go ahead and double click here on Guests and I'll double click on Room Assignments to add those two tables into this Design area in the back. When you're done choosing the tables, you can go ahead and close the Show Table window. If you ever need to get it back, it's right here on the Design contextual tab. There's a button here to get back to that Show Table window. If you need to remove a table here that you might've added accidentally, just right click on the header and choose Remove Table. Once again we can go ahead and rearrange these any way we'd like.

I'm just gonna expand this so I can see all the different fields. And now in order to add the fields from the tables at the top into our grid down here at the bottom, all I need to do is either click and drag each one. I'll click and drag the First Name down here to this first position. Or we could simply double click. Go ahead, double click on Last Name, and that adds it here in the second column. Our query is also going to see the phone number, so I wanna add that one in, and then from the Room Assignments table, we wanted the check-in date, we wanted the rate code, and we also wanted the room ID.

Just go ahead and double click on each of those. So at this point our query is exactly the same as what the Wizard gave to us. At any point in time we want to, we can either run the query or switch into Data Sheet View to see the results, and there are the results, exactly the same as what we saw earlier. Go ahead and click back into Design View, and we can take a look at a couple of other changes here that we're able to make. In the column grid down here in the bottom, we have a row here called Sort, and this is where we can hard code a sort into our query results. Let's go ahead and sort these results in a reverse chronological order. I'll come over here to the Check-in Date column and in the Sort row, I'll go ahead and give that a click, and you'll notice we get a new drop-down menu.

Here we can choose if we wanna either have the data appear ascending or descending. We'll go ahead and choose Descending to give us a reverse sort. We also below that have a row called Show, and that's a series of check boxes that we can turn on or off. If we turn them off, then this particular column will be invisible in the query results. Now, you might wonder why you would wanna include a column in the query but then hide it from the results. This actually can be quite a handy tool to have in order to hold intermediate calculations or other data that you wanna sort by but not include in the records that display.

I'm gonna go ahead and just leave all these columns on. Finally, if you want to rearrange the ordering of your columns, at the very top of the column here, we have this gray bar, and when you hover your mouse over it, you'll get this downward pointing black arrow. If you give it a click, that'll highlight the entire column, and then you can click and drag to move your columns into a different order. I'll go ahead and click and drag the Check-in Date back to after Phone. Go ahead and click anywhere to de-select it and that'll return to an unselected state. At this point let's go ahead and save our query. You could either press the Control + S shortcut key or press the disk icon here in the Quick Access toolbar.

Let's give the same name that we had before, the Check-in Details. Press OK, and that appears over here in the Queries section of the Navigation pane. And when we're done, let's go ahead and switch into Data Sheet View to see the new order here, and you can see that it's sorted reverse chronologically now instead of the original sorting order that it had, which I believe was based off of the ID number of the guest. So we're gonna continue working in the Query Design View throughout this chapter. There's lots of different options to explore and some really cool ways that we can organize our data, but they'll all start with this basic model.

We'll start with the tables to pull information from up top and the individual fields will be combined into the final data sheet down below.