Click here for great value web hosting.








Search for



Microsoft Access is a relational database, this means that items in one table can be 'related' to items in another so that the data can be linked together.  If we had one table containing the names of the Departments in the company we could create a relationship between the department name and the department for each employee in the Employees table to help us create forms containing information about each department.  Before you start creating relationships you should work out what tables you will need and make sure that the items which are going to be related contain the same information in each table.

Before you can create a join you must make sure that at least the field on the 'one' side of a 'one-to-many' relationship is defined as a primary key.  To create the join, click on the relationships icon on the Standard Toolbar or click on View -> Relationships.

When the Relationships window appears you will see a small window where you can choose the tables you are going to relate.

We are going to use both of these tables in our relationship so we will click on each one and then click the Add button to add them to the Relationships window.  When you have added all the tables you need (you could also add queries if you needed to relate them), click the Close button and you can define the relationships.

To create a relationship between the Department fields in each table, click and hold your left mouse button on the Department field in one table and then drag across to the Department field in the other table.  As soon as you release your mouse button the Edit Relationships window will appear showing the two fields that have been related.

If you want to use the relationship to ensure that data is automatically updated (e.g. all staff in a certain department are deleted if that department is deleted) then you should use referential integrity, but take care with this as you may destroy your data if you do not use it carefully.  To make sure that the join occurs in the right direction (the Department field in the Departments table is on the 'one' side and the Department field in the Employees table is on the 'many' side) you should click the Join Type button and select the appropriate option from the Join Properties window.

When you click OK on the Join Properties window and then click Create on the Edit Relationships window your join will be created.  As we have used Referential Integrity our join is shown as a one-to-many relationship with an infinity symbol (an 8 on it's side) marking the 'many' end of the join and a number 1 marking the 'one' end of the join.

When you have created your relationship you can click the Save icon to save the relationship and then close the Relationships window.  You will now be able to link the data from one table to the data in the other.