Pin Me

Microsoft Access: Join Properties and Join Types

written by: •edited by: Tricia Goss•updated: 12/9/2009

When two tables are joined in a relationship or query the join will tell Microsoft Access how the data in the table is related and the type of join will dictate which records the query performs an action on. Here we discuss the nature of join properties and how join types impact on data output.

  • slide 1 of 4

    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’.

  • slide 2 of 4

    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.

  • slide 3 of 4

    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.