If you've used table relationships in Access and created queries to show data from more than one table, then the join property can be a very useful way to control what data is displayed in the query. This article recaps how to create a one to many relationship and then summarises how to use the join property in an Access query, and describes the two basic kinds of join properties, an inner join and outer join. The join property is only used in Access queries which are based on related tables.

Creating a one to many relationship

Suppose your Access database contains two tables, one a Staff table which lists members of staff and the other a Sales table which lists sales records. The Staff table has a field StaffID as its primary key and a NAME field with each person's name. The Sales table has a field SalesID as its primary key and an ITEM field to describe the sale made. The Sales table also includes a field StaffID to show which member of staff made the sale. Suppose there are 5 names in the staff table, and there are 20 sales in the sales table, and suppose also that only 3 staff actually made sales, because 2 staff are new recruits.

You then create a one to many relationship between the tables using the relationships view in Access. To do this you drag the StaffID field in the Staff table to the StaffID field in the Sales table. You'll see the 1 and infinity symbols next to the fields in the relationships view as the start and end of the link. You can describe this relationship in plain English as "one member of staff can make many sales". You can then use this relationship in a query to extract data from both tables.

Creating a query with two related or joined tables

You can create a new query in design view and add both tables Staff and Sales. Once added, the link or join between the tables automatically shows in the query design view. You then drag a field from the Staff table, for example Name, into the first field in the lower query design area. Then drag a field from the Sales table, such as Item, into the second field in the query design. If you run the query, you'll see a list showing all staff with matching data in the Sales table. The query shows one line for each sales item. If someone didn't make a sale then they don't appear in the list. This is because the default join property for a join in a query is an inner join. This means that the query will only show data when there are matching values in the common field in both tables, which in this case is StaffID.

Changing Join properties in the query

Back in query design view you can view and change the join properties. To do this, carefully double click the link between the tables in the query design view. The JOIN PROPERTIES panel appears which shows three possible choices.

The default is Option 1, "Only include rows where the joined fields from both fields are equal". This an inner join and means that the query will show a list of all staff and their corresponding sales records, with one line per sales record. So this option only shows a list of records where the join field contains the same data. The join property is set to this option in all one to many relationships.

If you choose Option 2 instead "Include ALL records from Staff and only those records from Sales where the joined fields are equal" the query will now show all records from the left hand table, staff, and where they made a sale, each of their sales records. So the query shows all staff irrespective of whether they made a sales or not, and a list of every sale. So this option shows all records from the left hand table, and a list of records where the join fields contains the same data. This is an outer join.

If you choose Option 3 instead "Include ALL records from Sales and only those records from Staff where the joined fields are equal" the query will now show all sales, and where the sale has a staff name in its detail, each staff detail. In our example, because every sale is made by a member of staff, this option will show the same result as Option 1, which is a list of all sales and the corresponding member of staff. However if some sales had been made without a member of staff being logged, for example online or by post, then this option would list every sale. This is also an outer join.

So by changing the join property in a query showing a one to many relationship, you can display data in three different ways. You can show all common records, or all records from the left hand table plus all common records, or all records from the right hand table plus the common records. Hopefully this short article has given you a brief insight into joins and join properties in Access queries. Interested in learning more about Access? A really effective way is to attend a training course. This could represent a fast track way to really boost your Access skills.