Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Working with Joins

It is important that tables and queries that are the data source of a query be correctly joined in the query design. In some ways a query join is similar to a relationship. The most important distinction is that a query join is temporary. In fact, a query join can temporarily over-ride a permanent relationship.

In Access, there are three join types, affectionately known as Type 1, Type 2, and Type 3. In SQL these joins are more commonly referred to as Equi-or Inner Join , Left Outer Join, and Right Outer Join, respectively. Equi-joins are the default in the Access query designer; if you want a left, or right, outer join you have to modify the join type. In addition to these three join types, there is a fourth, the Cartesian join, which usually happens by mistake and yields results that, most often are far different from what you may be expecting.

Let’s put the theory in more understandable terms with some examples. In the sample database (download from here if you haven’t already done so), are two tables, tblRegions, and tjxLocations. In the database design there is already a relationship between these tables but we are going to override the relationship and set up temporary joins for this demonstration. In this particular example, we know the city and country of every location but we do not always know a city’s region.

When you first create a query using these two tables, the query diagram will include a join based on their relationship. To experiment with join types then, the first step is to right-click on the join line and edit the Join Properties.

Edit Join

 

DialogueJoinProperties

Inner Joins

An Inner Join (Type 1) returns records only when there are matching records in both tables. The diagram looks like this:

EquiJoin

The query returns four records.

EquiJoinData

Right Joins

A right join will return all records from the primary table, whether or not there is a matching record in the related (or foreign) table. So, in this example, the query returns one more record than the inner join did. The region for City 2 is not in the table.

RightOuterJoin

RightOuterData

Left Joins

A left join returns all records from the foreign table and only matching records from the primary table. In this case, the query result has the same number of records a the inner join example.

LeftOuterJoin

LeftOuterData

But what about the cities and countries, you might ask? We simply have to include two additional tables in the query source, tblCities and tblCountries. For both of these tables an inner join will be adequate because our data includes city and country data for every employee.

Including More Data in the Query

With a query open in design view additional tables and queries can be added to it by right-clicking the the field list area and clicking on Show Table. The required tables and queries can also be simply dragged for the database window or navigation pane depending on your Access version.

Here is an example that will return the city, region and country name for each employee:

qselEmployeeLocations

 

Another approach is to create an intermediate query that gathers the city, country, and region names using a query like this (qselLocations_Extended)

qselLocations_Extended

qselLocations_ExtendedData

Then this query is included in the employee locations query:

qselEmployeeLocationsExtended

This is the result (which is exactly the same result you get when you include tjxLocations and the city, region, and country tables):

qselEELocationsExtendedData

Cartesian Joins

A cartesian join is a special case where no explicit join is defined between a pair of tables. There are some special cases where a cartesian join is called for. However, most often the presence of a cartesian join in a query is simply an error. Depending on how many records are in the source tables, a cartesian join can return an extremely large number of records. Multiply the number of records in the first table by the number of records in the second table. The result will by the record count of the query output.

For example, there are two records in the Regions table and five records in the Locations table.

CartesianJoin

CartesianJoinData

Comments are closed.