Create Tables Relationship

After creating and identifying the necessary tables in an accounting system, the next step is to create a relationship. Relationship describes the relations among tables in MS Access database. Once this relationship is finished, then you can design forms, reports, and queries.

A relationship works by matching data in primary key fields, usually fields with the same name in both tables. In most cases, the relationship matches the primary key from one table, which provides a unique identifier for each row, with an entry in the foreign key in the other table.

There are three types of relationship that Microsoft Access can create:

• A one-to-many relationship: only one of the related fields is a primary key or has a unique index. A row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A.
• A one-to-one relationship: both of the related fields are primary keys or have unique indexes. A row in table A can have no more than one matching row in table B, and vice versa.
• A many-to-many relationship: a row in table A can have many matching rows in table B, and vice versa. You create this relationship by creating a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B.

How to create table relationship in MS Access

Notice here that in creating a relationship between tables, the related field name do not have to be the same name but it must have the same data type unless the primary key field is an AutoNumber field. You can match an AutoNumber field with a Number field only if the FieldSize property of both of the matching fields is the same. For example, you can match an AutoNumber field and a Number field if the FieldSize property of both fields is Long Integer. Even when both matching fields are Number fields, they must have the same FieldSize property setting.
Click Database Tools tab, and then click Relationship icon in the Show/Hide Group.

1. Click right mouse on the Relationship box, and choose Show Table...
2. Double click tblOpenTransactionJournalParent and tblOpenTransactionJournalChild. close the Show Table dialog box. If the title is not clearly displayed, drag each table box border to make it clearly displayed.
3. Drag the field JournalId of the table tblOpenTransactionJournalParent onto the field JournalId of tblOpenTransactionJournalChild.
4. The Edit Relationships dialog box is displayed. Make sure that the field names displayed in the two columns are correct. Put all check mark on all of the three check box displayed.
5. Click Create button
6. Do the same steps 1 to 5 for other tables, until the full relationship as depicted below:

Tables Relationship (click mouse to open original size)

As you can see, tblOpenTransactionJournalParent, tblOpenTransactionJournalChild,  tblPermTransactionJournalParent, and tblPermTransactionJournalChild are the table that we have created before in the page Create Tables in MS Access - Transaction Table. The other tables we have created before in the page Create Tables in MS Access - Master Table