MS Access Joins Explained: Database Design with Microsoft Access
Join Types
In any database table relationship there is an accompanying ‘Join’ property which manages the flow of data from one source to the other. In both the query and relationship screens the default join is set to ‘Join 1’, commonly known as an ‘Inner Join’. There are two other join types, ‘Join 2’ and ‘Join 3’, commonly known as ‘Outer Joins’.
Setting ‘Inner’ and ‘Outer’ Joins
The join properties, accessed from the relationships dialog allows table joins of the following types;
Option 1 - ‘Only include rows where the joined fields from both tables are equal’
This is called an inner join. A query that runs using this option shows only the records from the joined tables or queries that have the same values in the joined fields. When values are the same, the query combines the matching records and displays them as one record in the query’s results. An example of an inner join between a patient and an episode of care table would only show those records where the patient ID appeared in both tables, i.e. it would not show patients who had not had any previous episodes of care.
Option 2 - ‘Include ALL records from ‘Patient’ and only those records from ‘Episodes’ where the joined fields are equal’
When you change the default join (above) to either this or the next option, you are establishing an outer join. Here each matching record from the two tables is combined into one record in the query’s results. However when a record from the table that is contributing all of it’s records cannot be matched with a record from the other table the record still appears in the queries results, but with empty cells where there was no matching record from the other table.
Option 3 - ‘Include ALL records from ‘Episodes’ and only those records from ‘Patient’ where the joined fields are equal’
As with option two, this outer join will highlight in which direction the table contributing only matching records is set. This is denoted by an arrow which points to the table contributing the matching records, i.e. ‘One’ patient can have – ‘Many’ episodes of care.
It is most common that join properties, certainly when running and producing queries will be set to join type 2 or 3. The key consideration is whether you want to include all records from one table or the other as this has a heavy bearing on whether the query will run a valid data extract or simply output thousands of meaningless or useless records.
It is definitely worth testing your join properties within queries and understanding how an inner and outer join works prior to launching full scale query’s that interrogate hundreds of thousands of records.
In the following article we will look at Searching Tables to show you the fastest and safest ways to find and filter data within your database tables.
Related Reading:
>» Microsoft Access: Referential Integrity
>» Microsoft Access: Setting up relationships between tables
>» Microsoft Access: Defining Relationship Types
>» Microsoft Access: Designing a Relational Database (Part 1)
>» Microsoft Access: Designing a Relational Database (Part 2)
This post is part of the series: Designing a Database with Microsoft Access
Need some help navigating Microsoft Access? This tutorial shows you how to design a database that works smoothly for all your projects.
- Microsoft Access: Designing a Relational Database (Part 1)
- Microsoft Access: Understanding Data Types
- Microsoft Access: Understanding Field Properties
- Microsoft Access: Understanding Lookup Fields
- Microsoft Access: Join Properties and Join Types
- Microsoft Access: Defining Relationship Types
- Microsoft Access: Setting Up Relationships Between Tables
- Microsoft Access: Designing a Relational Database (Part 2)
