Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,974 courses, including more Business and personalized recommendations.Start Your Free Trial Now
- View Offline
- Naming conventions and best practices
- Working with joins
- Using comparison operators
- Defining criteria for select queries
- Creating parameter queries
- Creating calculated fields
- Working with dates and times
- Using the Expression Builder
- Creating conditional statements
- Making, deleting and appending records
- Building reports
Skill Level Intermediate
All of the queries in Access are stored in a common programming language called SQL. SQL or Structured Query Language is a powerful way to write queries but it can be a bit difficult to read or troubleshoot. Access provides a graphical interface to creating our queries that eliminates much of the tedium involved with reading straight SQL markup. This interface is called the Query Design View. Let's take a look at the various areas of the design interface and how we can begin to build queries using simple mouse clicks. In my navigation pane, I am going to expand the Chapter 1 group and I am going to open up the CustomerEmailandRegion query.
This opens it up in Record Set view and we can change to the Design view using this button up here on the menu, click on the little triangle and pencil icon. The Query Design view split into two areas. The upper half of the window shows all of the tables that go into building our query; the bottom half of the window shows all of the fields that are in our query. So, we can see that we have two tables that make up the data in our query. We have the tbl_ DirectCustomers and the tlkp_States table. I am going to go ahead and move this down a little bit by clicking on this bar in between and dragging down.
I am also going to rearrange these tables a little bit so I can see all of the fields within each. You can just grab on the corner and drag them open. You can grab on the header and move them over. And now I can see all of the fields that are within each table. I can also see that we have a relationship established between our DirectCustomers table and the States table based off of the State field. We can see that it's a one to many relationship based from state to the abbreviation in the States table. And this was previously established in our Relationships window. From these two tables, we are pulling a series of fields.
We can see that FirstName from the DirectCustomers table, LastName from the DirectCustomers table, Email address from the Customers table and the customer ID from the Customers table. From the States table, we are pulling RegionName and DivisionName. And looking at this, I can also see that we have an ascending sort order based off of the customer ID. If this wasn't here, the query results will be returned based alphabetically off of the FirstName. Let's go ahead and create this query from scratch. We will go up to the Create menu and in the Queries group click on Query Design.
The Show Table window opens up and here we can choose which tables we want to use in our query. Go ahead and double-click on DirectCustomers and States. You can now close the Show Table window. At any point, if you want to add additional tables, there is a couple of ways we can do that. We have a Show Table right up here or and appear or we can right-click in this field and say Show Tables. That will bring up the Show Table window again and we can select additional tables. Let's go ahead and close that out and we will start adding our fields to our query. Let's expand our table so we can see everything again.
From our Customers table, let's choose FirstName and LastName and you can add those just by double-clicking on their names. You can also drag and drop from the Customers table into the field. So, for instance I will grab Email. Drag and drop it into the next open field. Let's go ahead and add DivisionName and RegionName, and I am simply double-clicking. And then finally, we will add our CustID. Again double-click. Under the sorting row for CustID, if you click in this box, you will get a drop down menu of options.
I am going to Ascending. So, we will go from lowest customer ID to highest customer ID. Let's go ahead and run the query and see where we are at. You can either use the View button or Run. And with the Select query, running the query is the exact same as viewing the results. So, either of these buttons will work. They will do the exact same thing for a Select query. I will click Run. And we can see that we have our fields, FirstName, LastName, Email and CustId that are being pulled from our DirectCustomers table. We also have DivisionName and RegionName that are coming from our States table.
Let's go back into the Design view and we will look at a few ways that we can manipulate our grid further. Click on this button to go back into Design view and let's go ahead and take our CustID and move it to the beginning of the query. In order to select the field, there is a tiny gray bar at the very top and when you have the downward pointing arrow, go ahead and click and it will select that field. From there, you can go ahead and click and drag to move the field either left or right if we had further to the right. We can go ahead and move it to the left and drop it at the very beginning. Now, the CustID field is an auto generated number that really doesn't have any real-world data.
it's simply there for Access to keep the record separate. So, we don't really need to see it. We can go ahead and turn off the Show button so that Access will use this field for sorting but it won't use it to actually show up in the results. I can add additional fields to my query just by dragging and dropping them into the place that I want them. For instance, if I want phone number to be inserted between LastName and Email, I can drag it and drop it into the grid, and you will see the Phone number and then Email moved over. We can also use the buttons at the top menu to insert or delete columns. Let's go ahead and delete the Phone number and you will see that Email moved back over to the empty spot.
Let's review our query results by clicking Run and we will see our final query. We have got FirstName, LastName, Email, Division and Region, and everything is being sorted based off of the customer ID number, not alphabetically. So, while the wizard will be able to quickly create a very simple query within your database, it really only scratches the surface of what is possible in the Query Design View. For more complex tasks or to troubleshoot and modify your queries, the design interface will be your go to tool. As we'll see throughout this title, the design interface allows us to visualize how are queries are constructed, which minimizes entry errors and gives us greater control over our query record sets.