Advanced Database – Apply, modify an inner join
Joins are links between table that are used to modify the way queries select records.
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.
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.
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.
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.