Locked lesson.

Upgrade

About this lesson

Learn how to use joins to create a query using information from more than one table.

Quick reference

Understanding Joins

Joins tell Access which data to retrieve from linked tables. There are three types of joins: INNER, LEFT, and RIGHT. 

When to use

We specify a join type when running queries using linked tables. 

Instructions

  • Open ‘qryTest’ from the navigation pane. 
  • Select ‘tblStatus’ and press Delete. 
  • Delete the criteria from ‘Priority’ and 'CustomerName'. 
  • From the Add Tables pane, select ‘tblTicketCompany’ and click Add Selected Tables.
  • Drag the ‘Company’ field from ‘tblTicketCompany’ to the grid. 
  • From the Query Design tab, in the Results group, click Run. 
  • Switch to SQL View. 
  • Note that the default is INNER JOIN. 
  • From the Query Design tab, in the Results group, click Run. 

This means that the value must exist in both tables for the record to be returned. 

  • Switch back to Design View. 
  • Double-click on the line that links the tables to open the Join Properties dialog box. 
  • Select Option 2 (LEFT JOIN)
  • Run the Query.

This means that the value must exist in the left table for the record to be returned. 

  • Switch back to Design View. 
  • Double-click on the line that links the tables to open the Join Properties dialog box. 
  • Select Option 3 (RIGHT JOIN)
  • Run the Query. 

This means that the value must exist in the right table for the record to be returned.

Login to download
  • 00:04 You'll come across joins whenever you want to run a query that uses fields
  • 00:09 from different tables.
  • 00:10 And joins really tell Access how you want to join the two tables together and
  • 00:16 what information you want to extract.
  • 00:18 Now that's quite hard to visualize without seeing an example.
  • 00:22 So let's go back to the query that we created in the previous lesson.
  • 00:28 Now I'm going to remove some of these criteria.
  • 00:30 So let's get rid of active, let's also get rid of high, and also descending.
  • 00:36 And I think I'm also going to get rid of the status column and also priority.
  • 00:41 I'm going to delete the status table.
  • 00:44 Now what I'm going to do is I'm going to add a different table all together.
  • 00:48 So let's go to the tables group and our add tables pane and
  • 00:53 I'm going to add table ticket company.
  • 00:56 Now notice as soon as I add that,
  • 00:58 it recognizes the relationship that I have set up between these two tables.
  • 01:04 Now what I'm going to do here is I'm
  • 01:06 going to add the company field down into my query.
  • 01:10 So effectively, if I run this query, I should get a list of the ticket numbers,
  • 01:14 the customer names, and the companies where they exist.
  • 01:18 Remember, we didn't add companies for all of the customers and the tickets.
  • 01:22 So if I run this query as it is, that's the list I get.
  • 01:26 Now that is by no means the full list.
  • 01:29 It's not showing me the full list of ticket numbers,
  • 01:32 all of the customer names and all of the companies.
  • 01:35 Now why is that?
  • 01:37 Well, this is related to how the two tables are joined,
  • 01:41 the joint type that they're using.
  • 01:43 If we right click and take a look at the SQL code,
  • 01:47 notice that we have an inner join in here.
  • 01:51 So what is an inner join?
  • 01:54 Well, this is best illustrated if we go back into design view and
  • 01:58 double click on the relationship between these two tables.
  • 02:02 Now right at the bottom here we have three different options.
  • 02:06 And this first option relates to an inner join.
  • 02:10 And you can see that if it links the tables using an inner join,
  • 02:14 it's only going to include rows where the join fields from both tables are equal.
  • 02:19 So because I'm using an inner join, Access will only return
  • 02:24 records whether is a customer name and a company present.
  • 02:29 And as I said, we didn't add all companies in for our customers.
  • 02:34 So that's why when I run this query, I'm getting a much shorter list.
  • 02:39 The inner join is between these two fields, the customer name and company, so
  • 02:43 it's only going to return the records where a customer name and
  • 02:47 a company both exist.
  • 02:48 So that is why I'm getting a much shorter list than I might expect.
  • 02:54 Let's go back and change our join type.
  • 02:56 So we're going to go back into design view, let's double click,
  • 03:01 and this time I'm going to select option number two.
  • 03:04 Let's click on OK and rerun the query.
  • 03:09 Take a look at this.
  • 03:10 My list is now much longer.
  • 03:12 And you can see that not all of these customers have a company name.
  • 03:17 Now if we right click and take a look at the SQL view,
  • 03:20 this time it's doing a left join.
  • 03:23 Now what a left join means is that it only requires the information on the left, in
  • 03:29 this case the custom name to be present, in order for it to return the record.
  • 03:34 So it doesn't matter if the company name is blank.
  • 03:37 Let's go back to design view, double click, and select option three.
  • 03:45 Click on OK.
  • 03:47 Let's run our query and I get that shortened list again.
  • 03:52 So if we take a look at this, we're going to go into SQL view,
  • 03:56 it's now doing a right join.
  • 03:59 So what that means is that the information has to exist on the right in order for
  • 04:05 it to return what's on the left.
  • 04:07 And because we've only added companies in for
  • 04:10 four customers, that is why we're getting this shortened version of the list.
  • 04:15 So that is how joins work.
  • 04:18 They're not particularly confusing when you break them down in that way,
  • 04:22 just remember, option one is an inner join,
  • 04:25 option two is a left join, and option three is a right join.

Lesson notes are only available for subscribers.

Gift this course