Advanced Database – Apply, modify an inner join

Joins are links between table that are used to modify the way queries select records.

55 Inner Join

To demonstrate, create a new query in design view.

Add the tables ‘Car and owner details’ and ‘Jobs’

In the Field drop down menus, select all the fields from both tables using the wildcard *, as shown.

56 Wildcard

Press F5 or click the Run Query icon.

Notice that it repeats some of the records multiple times.

To prevent this we need to create a join between the tables.

Press F4 to clear the query table.

57 Create Inner Join

To create an inner join, click and drag from the ‘Car Reg Number’ field in one table to the corresponding field in the other table.

This creates a connection between the two fields, that applies only to this query.

Press F5 to run the query.

58 Run Query

Notice that it only returns records where the related Car Reg Number fields are identical, cutting down on the repetition.

To modify the inner join, right click on the connecting line between the tables and click Edit.

This brings up a Join Properties window where you can change the fields and type of join.