Access: Link tables and queries - how it works
To link your tables and queries in Microsoft Access, you have to consider a few things. There are different criteria by which you can link your tables together, such as the inner and outer link. This practical tip shows how you can do this with Microsoft Access 2007.
Access: Link tables and queries to the inner link
If you have an Access file that already contains various tables, you can link them together.
- Use the "Relationships" tab to create relationships between tables. There, select the desired relationship that best represents the relationship between the tables. If you have already created a relationship, Access will automatically create an inner link between the tables when you add new tables to a query.
- If you create a new query and there are no relationships between the tables, the program does not automatically create a link.
- To create a link, now drag a field from one table into the field of another table.
- The SQL syntax that you must use to use the inner link looks like this, for example: FROM table1 INNER JOIN table2 ON table1.field1 comparison operator table2.field2.
- The comparison operator is, for example, a "" or "=".
Access: Link tables and queries to the outer link
Another link is the outer link. It shows you all the rows from one table and only the rows from the other that match the first table.
- Since outer links are created by modifying an inner link, you must first create an inner link between two tables.
- First you have to go to the menu to change the link. Therefore, double-click on the link you want to edit.
- You can now choose between option 2 and option 3. Select the desired option and confirm with "OK".
- The SQL syntax that you need for the outer join looks like this compared to the syntax for the inner join: FROM Table1 [LEFT | RIGHT] JOIN table2 ON table1.field1 comparison operator table2.field2.
- As with the inner link, you must then adapt the example to suit your tables.
- To delete the shortcut if you have made a mistake, simply select a shortcut and press the "DEL" key.