How to Work with Joins in MS Access

This article will explain the significance of Joins in MS Access, and their types.

In MS Access we deal with the set of tables that have some logical relationships with each other. Although a table can have as many relationships, a relationship can have only two tables. So in the context of a query, relationships are represented by joins. In context of any Relational database system, a SQL join is used to combine data from one or tables. As opposed to a query criteria, joins ensure that each pair of rows from different tables that meet the join conditions get combined in the recordset to essentially form one single row.Working With Joins In MS Access

Joins help users with combining records from two tables or more in a database. Joins in queries are similar in concept to relationships in tables. So whenever a user adds tables to a query, MS Access ensures that a join should form based on the defined relationships between those tables. It’s an automatic process but users can also manually create joins between source queries. In MS Access Joins are of mainly two types – The Inner Join and The Outer Join. There are however, other Joins too like Left Outer and Right Outer Joins.

Users can create them from Queries Design View option.

Inner Join

The Join Properties BoxInner join, sometimes called equi-join, is the default join in MS Access and is commonly used. The Inner Join is basically used to fetch or display those rows which have a common field value among two tables. Thus it only displays the matched field values.

It can be conceptually understood from a vein diagram containing two intersecting circles where the inner join is the section of intersection, which is common area of both the tables.

For example, consider a table of total students in a class and a table of absentees in the school, the inner joint will thus display only the absentees of that particular class (intersection).

You can make use of Inner Join in a query by selecting INNER JOIN as keywords for the FROM clause of Select Statement.

Outer Join

Outer Join is similar to Inner Join except that it also includes or displays all the records from one table along with the field values which are equal or common in the tables. It can be used to fetch records from a number of tables while keeping all the records of one table. On a Vein Diagram it can be represented as two intersecting circles where outer join is any one of the circles and conceptually it will contain all the common records between those two tables.

In the example of students of class and absentees of school, it will display the records of either all students in class or total absentees of the school.

Outer Join is further divided into Left Outer Join and Right Outer Join, both are supported by Access DB engine.

For Access users who keep sensitive business data stored in the database, it is very critical to keep an Access repair tool preinstalled to deal with incidents of corrupted Access files.

Author Introduction:

Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including fix sql and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.