Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When creating queries that include multiple tables, it's important to understand how those tables are related in order to get meaningful results. Typically, Access will be able to assist you in building these relationships if they're not previously defined. But there are occasions when you'll need to create or modify how your tables are grouped together. Let's take a look at a couple of examples. In the Chapter 2-6 group, let's go ahead and open that and we'll open up our Sales department query. You can see that we're getting Employee ID, FirstName, and the Department is Sales, and we have a total of 84 employees in the Sales department.
If we look at this in Design view, we'll see that we've got the Employee ID, FirstName and Department, and the Criteria Sales. So 84 employees in our Sales department. Keep that number in mind. Let's go ahead and close this query out. We'll open up this query MaySales. Here we've got the employee ID, FirstName, and the May sales figures. But you can see we only have 31 records here. What happened to all the other employees? Let's go into Design view and take a look at this. This query is constructed with the Employees table, the Orders table, and the Products table.
We have the Employee ID and the FirstName coming from the Employees table, the Price coming from the Products table, and the OrderDate coming from the Orders table. Under Criteria, we have >=#5/1/2010# And <=#5/31/ 2010#, basically all of May. Finally, in the Price column, we have the total of Sum. So it's adding up all of the prices over the course of May. So why when we run this aren't getting all of our employees in the sales department? Well, we're only getting the employees that actually had sales in the month of May.
If I double-click on the join line between tbl_Employees and tbl_Orders, we'll see that the default view is to only include rows where the joined fields from both tables are equal. So that's why our results are only showing sales numbers for the employees that actually had sales. We're not seeing anything for the employees that didn't have sales. Now, we can construct a single query that fixes this issue. But if we were to try and change this here to include all records from the tbl_Employees and only those records from tbl_Orders where the joined fields are equal, Access won't allow us to do that.
If I try and run this query, Access is going to give us an error saying the SQL statement could not be executed because it contains ambiguous outer joins. In order to fix this, we need to create another query and use this MaySales query as an input. Okay, let me show you what I mean. Let's go ahead and close this MaySales query. Now we don't need to save changes to those. We'll create a new query in Design view. Go to Create > Create Design. First, we'll add our Employees table, and then we'll switch the tab to Queries and we will choose qry_MaySales.
Go ahead and close the Show Table window and you'll see that Access has generated a join between EmployeeID and EmployeeID here. Let's add a couple of fields to our query. Let's add our EmployeeID, FirstName, Department, and our May sales figure from that query. Now, if I run that query right now, we'll see the same number of records, 31, that we saw in the previous query. Again, we're only displaying the employees that had figures for May.
Go back into Design view and let's go ahead and change this relationship between the employees and the query that we had built by double-clicking on the line. What we want is the option to include all records from the tbl_Employees and only those records from MaySales where the joined fields are equal. Now depending on which order you put your tables in, these two options might be reversed. So yours might be 3 or 2. But what we're looking for is we're including all records from the Employees table.
Access updates the join line to indicate the choices that we made with the arrow pointing to the MaySales field. If we run this query now, we're going to get all of our employees regardless of whether they had sales or not. Now we're getting 200 and this is not the 84 people in our Sales department. So we have one more thing that we need to change here. You can see that we're also getting employees in the Advertising or Human Resources departments and it makes sense that they wouldn't any sales for the month of May. So let's go ahead and filter them out in Design view. Under Department, I'll change my Criteria to Sales.
Now, when I run this query, I'll get all 84 people that are in the Sales department, and for the people that had sales in the month of May we'll get their totals. If they didn't have any sales in May, it'll be a blank field here in MaySales column. So understanding how your data tables relate to one another is a critical component to creating well-structured queries that give meaningful results.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.