Guide to Microsoft Access JOIN Statements

Though basic queries work well in a Microsoft Access database, sometimes you need something a little more powerful, like a join query. Joins allow you to gather data from multiple tables at one time based upon existing relationships. Relationships can include one-to-one, one-to-many and many-to-many. Depending on your needs, there are several different types of joins to allow you to join tables from one side of the relationship or the other.

Getting Started

The first thing you need to do is establish relationships between your tables. Select the “Database Tools” tab from within Access. Select “Relationships” from the Show/Hide group. Select the tables and/or queries you want to add to the relationships window. Queries can be used within join queries as well as tables. Drag the field you want from your first table or query to the related field in the second table or query.

In the “Edit Relationships” window, select “Join Type.” Select your join type and press “OK.” Press “Create” to create a relationship between the two fields. After all desired relationships are created, you can create your join queries.

Open Query Designer

Select the “Create” tab. Choose “Query Design.” Press “Close” when the Show Table window appears. Press “SQL View” in the Results group. This window allows you to input an SQL query. This is more difficult than using the query wizard, but it’s the best option for creating joins.

Types of Joins

The most common type of join is the inner join. Inner joins gather results from two or more tables based upon a matching or common field between the tables. The matching field must use the same data type. The “INNER JOIN” keyword is used in the FROM statement of your SQL query. The matching field does not have to appear in your query.

Example:

SELECT Name, Date, Address, Age

FROM Table1 INNER JOIN Table2

ON Table1.Age=Table2.Age;

Outer joins are used to gather records from multiple tables while still keeping all the results from one table, regardless of matching records. Outer joins come in two forms: left outer joins and right outer joins. Use the left outer join to select all results from the left table, or left side of the relationships, while only retrieving matching records from the right table. Use right outer joins for the opposite results. Use the same format for outer joins as inner joins.

When using joins, the join keyword, such as INNER JOIN, LEFT OUTER JOIN and RIGHT OUTER JOIN, is always capitalized and placed in the FROM statement.

Nesting

For an even more detailed and complex query, nest your joins. There are a few rules to follow when nesting joins.

Always place nested joins within parenthesis. This keeps them separate from the main join and allows you to troubleshoot easier if necessary.

Inner joins can be nested inside other inner joins.

Outer joins can be nested inside inner joins.

Inner joins can never be nested inside an outer join.

If you nest a join, the nested join appears after the ON statement in the original join. For instance, in the inner join example above, the nested join would be placed in parenthesis after the ON statement line.