How to Create All Types of Relationships in One Place in Microsoft Access

You can join data sources together with relationships.

Creating relationships in your Microsoft Access database prevents duplicate information, limits confusion and speeds up processes. Access contains several different relationship types and each depends on the kind of information listed in your sources. Two common relationship types include one-to-one and one-to-many. Access automatically defines relationships when you create a lookup field or combine tables in a query. You can make changes to these relationships, create your own or review the entire scheme in one unified location.

Instructions

1. Click the "Database Tools" tab on the ribbon at the top of the page and select "Relationships." This opens the relationships window, which allows you to view, add and remove relationships between fields. Because forms and reports don't hold any records themselves, you won't see them listed here.

2. Click the "Show Table" button if you don't see a certain table or query to place it on the relationships grid. To create a relationship between two sources, drag the field from the first table to the related field of the second table. Then click on the "Edit Relationships" button on the ribbon to change the type of relationship.

3. Click the "Join Type" button to define the type of relationship between the two sources. Choose the first option to display a one-to-one join with the most restrictions. You will only be able to connect a single field in the first table to a single field in the second table.

4. Select the second option to define a one-to-many type of join. Your first table can use as many of the fields from the second table as you want, but the second table will only have one related record. Select the third join option to choose the reverse table assignment.

References

• Microsoft Office: Guide to Table Relationships