Join Adam Wilbert for an in-depth discussion in this video Exploring the Query By Example (QBE) interface, part of Access 2007: Queries in Depth.
- View Offline
All of the queries in Access are stored in a common programming language called SQL. SQL stands for Structured Query Language, and it could be a pretty powerful way to create queries, but a little bit daunting at first. So, Access actually provides a second way that we can create our queries called the QBE grid or the Query By Example. I am going to go ahead and open up one of the queries that we just created in the previous movie. I am going to open up the qry_ CustomerEmail query, and if I double-click on it you'll see that the query runs again, and it's pulling the FirstName, the LastName, and the Email address out of our DirectCustomers table. Now, there are a couple of ways that we can switch to the QBE environment.
At the very top on the Home tab we've have got this icon here that's a triangle and a pencil and a ruler and this will switch us to our Design View. I can also select on the drop-down menu below that and select from a couple of additional views that we have. So, we've got the Datasheet View, which we are looking at right now. I also have PivotTable, PivotChart, SQL View if you want to take a look at the text behind the scenes, and we have our Design View as well. There is another option that we have when we want to switch between Datasheet View and Design View, and we can find that in the lower right-hand corner. We've got a couple of little icons down here and these are really tiny. We've got the Datasheet View, the PivotTable View, the PivotChart View, SQL View, and finally in the lower right corner we've got a small icon for Design View.
I use these all the time, because they are really convenient, they are always there, and sometimes it beats moving up to this menu, opening the drop-down menu, and then selecting the view that you want. But right now we are going to go ahead and just select Design View, so you can use whatever method you like. We'll take our query that we opened up and we'll move it into our Design View. You'll notice that when we switch into Design View, Access opens up a new tab called Design and we've got some additional options here that are specific to the query design environment. The Results section here has two options. We change our View again, and we can also run it. Now, for most of the queries that we are going to be creating in this course, running is actually identical to viewing the Datasheet View.
We'll talk about why that's different when we run action queries later on in the course. The second section is Query Type, and you'll recognize some of these from the wizard. We've got the Append, Update, Crosstab. These are all the different queries that we can create. Right now, we are doing a simple select query, so that's the option that we've got selected. The third section of the menu is called Query Setup, and this is where we can choose some of the options that will help us build our query. The fourth section of the menu, called Show/Hide, has some of the options that we can use to manipulate the grid below, and we'll take a look at that in moment. So, the Query Design environment has two windows. We've got the upper pane, it's called the Table pane, and this will have all of the tables that we are using to create our query.
Right now, we've got our DirectCustomers table. The lower half of the window is the Design Grid and down here we can take a look at all of the tables that are being used to create a query. So, we can see that we are pulling the FirstName from the DirectCustomers table. We've got the LastName from the DirectCustomers table and we've got the Email from the DirectCustomers table. We can move the tables around for organization. We can expand them open a little bit if we want to view all the fields within each table. To add additional tables to our query, we've got a couple of options. We've got the Show Table button up here. We can also right-click in any open space here and choose Show Table. That will bring open a new window called Show Table and we could choose any additional tables from our database that we want to include in our query.
For instance I can include a tbl_Products. We'll go ahead and close this out, and I am going to expand this open so I can see a little bit more. To add a field from a table, down into our query design environment, all we need to do is double-click on it. So for instance, if I want to include a ProductName into our query, I would just double-click on it and then drop it down. There are couples of other ways we can do it. We can click and drag. So, for OilCode, I could click and drag that down and drop it. I can also select a range of fields by using some of the standard Windows shortcuts. So I can click on the first one. I can Shift+Click and click on the third one to select a range.
I could also use the Ctrl key on my keyboard. If I press that down, I can select individual records to add to the group. At that point, I can go ahead and click and drag and select all of those and drop those down into our design grid. So, that added a lot of fields here to our query that I actually don't really need, and we can get rid of them in a couple of different ways. I don't want to actually include a ProductName into our query, so I can click on this gray bar at the very top of the field name, and the whole field will select. Once it's selected I can right-click on it and say Cut or I can say Delete Columns from the Query Setup menu. So, let me go ahead and delete this column. I can continue deleting columns and that will delete out of my query.
Another way to delete some of your fields is just click on the gray bar above the field name, and again, you can Shift+Click to select a range. Once it's done you can right-click and say Cut and we'll get rid of all those as well. Finally, to remove a table from our query designer, we can right-click on the table header and say Remove Table, and this goes back to where we started. So, let's go ahead and take a moment and we are going to recreate this query from scratch using some of the methods we just took a look at. So, I am going to go ahead and close this and Access is going to ask if we want to save changes, and I don't necessarily need to, so I am going to go ahead and say No.
This time we are going to go to the Create tab and we are going to choose Query Design. Access opens up a new Query Design environment and it automatically opens up the Show Table window where we can select the tables that we want to use. So, we are going to recreate the customer email query from scratch. So I am going to pull in the DirectCustomers table just by double-clicking on it. I am going to go ahead and say Close, because I am done with this window and I am going to expand this out a little bit. Now, if you remember, the customers query includes the FirstName, the LastName, and the Email address. I find it easiest just to go through and double-click on everything, so I am just going to double click on FirstName, double click on LastName, and double click on Email, and you'll see that they all get dropped down into our query environment.
If I go ahead and say Run, I'll switch to Datasheet View, and we'll see that we get the results from the query that we built using the wizard in the last movie. We've got the FirstName, the LastName and the Email address pulled from the tbl_DirectCustomers table. So, we just created our query from scratch using the Design environment and this query is identical to the one that we created using the Wizard. In the next chapter we are going to take a look at how we can use the Design environment to move beyond the options that are available within the Wizard.
- Naming conventions and best practices
- Working with joins and primary keys
- Using comparison operators
- Printing query results
- Creating parameter queries
- Creating calculated fields
- Using the Expression Builder
- Making conditional statements
- Appending queries
- Updating queries