Access Relationships – What They Are and How to Create Them

To get your Access tables working properly, you have to establish the right relationships. When done correctly, you will be able to draw related data from multiple tables so that you can create reports and conduct data analysis.

Microsoft Access gives users the necessary tools to create and maintain relational databases. To create the database, you have to create relationships between the different components. Perhaps the most obvious is the relationship between the tables. As one of the major advantages over Excel, you want the tables to be able to return related data, and the established relationship will play a role in your query results.

Understanding the Relationship

For relational databases, data on tables is related, and what ties them together is usually a specific field. As is common in software, the relationship is usually a parent and child. In this case, the parent table houses the basic information. There are child tables related to the parent table, and they contain more detailed information about the parent record.

The field is defined by the parent table, and it is called the primary key. That same field appears in each of the child tables, and it is called the foreign key. Many businesses use customer IDs or some other numeric method of defining their customers and vendors. A number is applied because a name can appear multiple times, but the numbers are assigned only to one customer or vendor uniquely.

There are three primary types of table relationships.

1. One-to-one – Only one row can match between two tables.
2. One-to-many – One row in the parent table can match many rows in the child table, but each row in the child table can only have one matching row in the parent table.
3. Many-to-many – Multiple rows in each table may have multiple matching rows in the other table. A third table is usually created to accomplish this. The third table includes look up data for the two tables.

Creating a Relationship Between Tables

The steps to create a relationship between tables is relatively straightforward. However, you may want to practice on a sandbox database to test out the different options.

1. Go to the Database Tools tab and click on Relationships. This will display the Show Tables If it does not display the screen, click on Show Tables. The screen should display without prompting if you have not established the relationship.

2. Highlight the tables you would like to link with the relationship, then click Add.

3. Click Close.

4. Click on the field that you want to use to create the relationship. Drag it over to the same field in the second table. The Edit Relationships screen should display.

5. Review the checked field. You have three choices:

• Enforce Referential Integrity – The child table must have a corresponding parent table. In the beginning this is the safest choice.
• Cascade Update Related Fields – Tables will be updated whenever the related record is updated on another table.
• Cascade Delete Related Records – Tables will be deleted whenever the related record is deleted on another table.

6. Click Create.

7. Verify the relationship as it appears between the two tables on the Show Tables

Deal with Access File Corruptions

Relationships make data in different tables related. And make the structure of the whole database more complex. In case a complex Access database is corrupt, you can consider using a professional Access file repair tool to recover both the tables and the relationships.

Author Introduction:

Victor Ren is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair xls problem and word recovery software products. For more information visit www.datanumen.com