databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access Join Table Queries

Multiple Table Queries

Most of the queries you create in Microsoft Access will more that likely need to include the data from more than one table and you will have to join the tables in the query. The capability to join tables is the power of the relational database.

As you know, in order to join database tables, they must have a field in common. The fields on which you join tables must be the same or compatible data types and they must contain the same kind of data, however they do not have to have the same field name (although they probably will). Occasionally, the two database tables that you want to bring the data from may not have a field in common and you will have to add another table to the query with the sole purpose of joining the tables.

Different types of query joins will return different sets of results. When creating new queries, it is prudent to test them on a set of records for which you know what the result should be. That’s a good way to be sure that you have the correct join and are getting accurate results. Just because a query runs and doesn't give you an error doesn't mean that the resulting data set is what you intended to return.

Failure to join tables in a database query will result in a cross or Cartesian product (A Cartesian product is defined as all possible combinations of rows in all tables. Be sure you have joins before trying to return data, because a Cartesian product on tables with many records and/or on many tables could take several hours to complete.), in which every record in one table is joined with every record in the second table - probably not very meaningful data.

There are inner joins and outer joins - each with variations on the theme.

Inner Join

A join of two tables that returns records for which there is a matching value in the field on which the tables are joined.

The most common type of join is the inner join, or equi-join. It joins records in two tables when the values in the fields on which they are joined are equal. For example, if you had the following Customers and Orders tables and did an equi-join on (or, as is sometimes said, over) the CustomerID fields, you would see the set of records that have the same CustomerID in both tables. With the following data, that would be a total of 7 records. Customers listed in the Customers table who had not placed an order would not be included in the result. There has to be the same value in the CustomerID field in both tables.

An Inner Join of the Customer and Order Data
An Inner Join of the Customer and Order Data

If, in the query result, you eliminated redundant columns - that is, displayed the CustomerID column only once in the result - this would be called a natural join.

An inner join returns the intersection of two tables. Following is a graphic of joining these tables. The Customers table contains data in areas 1 and 2. The Orders table contains data in areas 2 and 3. An inner join returns only the data in area 2.

Outer Joins

A join between two tables that returns all the records from one table and, from the second table, only those
records in which there is a matching values in the field on which the tables are joined.

An outer join returns all the records from one table and only the records from the second table where the value in the field on which the tables are joined matches a value in the first table. Outer joins are referred to as left outer joins and right outer joins. The left and right concept comes from the fact that, in a traditional database diagram, the table on the one side of a 1:N relationship was drawn on the left.

Using our Customers and Orders tables again, if you performed a left outer join, the result would include a listing of all Customers and, for those that had placed orders, the data on those orders. You would get a total of 11 records from this data, which is a very different result from the 7 records provided by the inner join.

An Outer Join of the Customers and Orders table
An Outer Join of the Customers and Orders table

In the diagram below, a left outer join on the Customers table will return the data in areas 1 and 2. By the way, this type of diagram is called a Venn diagram.

Not All Data Can Be Edited

Earlier, it was mentioned that the results of a query represent “live” data, meaning that a change to that data is actually a change to the data in the base table. However, you will find that you cannot edit all data that is returned by a query. You can edit values in all fields from a query based on a single table or on two tables with a one-to-one relationship. But you can’t edit all fields in a query based on tables with a one-to-many relationship nor from crosstab queries or
those with totals.

In general, you can edit:

  • all fields in a single table query
  • all fields in tables with a one-to-one relationship
  • all fields in the table on the many side of a one-to-many relationship
  • non-key fields in the table on the one side of a one-to-many relationship

You can’t edit:

  • fields in the primary key in the table on the one side of a one-to-many relationship
  • fields returned by a crosstab query
  • values in queries in which aggregate operations are performed calculated fields

There are ways to work around some of these editing limitations but the precise technique will depend on the RDBMS you’re using.