The “Relationships” window (“Relationships” window)

Since we’ll be creating several tables, we need “relationships” between these “tables” to make optimum use of “Access”.
Relationships are what we do in the “Relationships” Window.
Below is an example of the” Relationships” Window from the “Northwind” database as an example that is provided when you purchase “Access”.

You have two options to get to the “Relationships” Window:

1. click on the “Relationships” icon.
2. click on “Tools” and select “Relationships” in the toolbar.

So when we click on the “Relationships” and there are no “Relationships” made, then it opens the “Show table” window.

Select the table and click “Add” . Repeat this step for as many tables as required to make relationships between them.

Click “Close” when you’re done.
The “tables” are added to the “Relationships” Window.

Now to create “relationships” between these tables, we click on the field from one “table” and drag it to the field in the other “table” that we wish to establish the relationship with.

This pops up the “Edit Relationships” dialog box.
(You’ll see the checkbox “Enforce Referential Integrity”. It will be elaborated in the next section)
Click the button “Create” to capture the relationship.

Please note that you can create “relationships” between the different fields only if they are of the same “Data Type”.
You can not create relationships between fields where, one is a number field and the other is a text field.

You can always delete or modify relationships.For this, we right click on the “Relationships” icon, and choose “Delete Relationship” to remove a relationship or “Edit Relationship” to change a relationship.
You can also change a relationship by double-clicking on the line between the two tables.

To add more “Tables” to our “relationships” window later, we can click the “Show Table” (Show Table) icon.

Enforcing Referential Integrity (“Enforce Referential Integrity”)

Now we have established various relationships and have set the properties for these relationships.

Referential integrity refers to the rules built by “Access” to ensure that the relationship between the data (fields) of the various tables is valid.

It ensures that each unique value for a field of a table in the relationship, must have a corresponding unique value in the other table. (1 to many).

This prevents us from having meaningles data in our database.

For example, a sales record without a customer.

So, inorder to set up Referential Integrity, the relationship between the tables adhere to these rules:

1. The related field in the “parent table” must have primary key.
2. The related fields in both lists have the same data type.
3. Both lists should belong to the same database.

As long as you meet these conditions, you can “enforce referential integrity”.

Click on the box “Enforce Referential Integrity” in order to enable this property.

Once this box is checked, we have two options that we can check again:

1. “Cascade Update Related Fields”: assures us that if a value is changed in the “Parent table” will also be reflected in the “Child table”

2. “Cascade Delete Related Records”: assures us that if you delete a record in the Parent table, all related records in the “Child table” will also be removed.

You will find the button “Join Type …” in the “Edit Relationship”.
If you click it, opens a window where you have three different relationships between tables with checkboxes.
The first is checked by default. It would be highly unusual for the relationships in this window to change, as it permanently changes the way the data between the two lists are related.

Entering data with lookup lists (“Lookup” Fields)

In Access we can create “Lookup” fields in a table that displays values from another table, query or list of typed values.
If the field looks up a value in another table, it will automatically create an additional relationship between the two objects (tables, queries) that we found in the “Relationship Window”.

The easiest way is to use the “Lookup Wizard”.
Select the field and choose “Lookup Wizard” as a “Data Type”.

The next window that opens gives us the choice of whether we want to create the lookup column from a “table” / “query” or type in a list of values.

In our case we choose a “Table” that we created earlier with the different countries.

click the “Next” button

In the third window of the “Lookup Wizard”, we choose the left field (country in our case) and click on the button “>” so that the country field appears in the right column.

Now click “Next”.

The next window is new in Access 2003. We can sort the values of the field that we selected to appear in ascending or descending order.
Choose your field and choose Ascending or Descending

Click “Next”.

In the next window, we give a name to our “Lookup” column and click “Finish”.

In “Design View”

(Design View),

under the “Lookup tab”, we see that the information selected for that field with the country field from the table of countries as the source.

In “Datasheet View”, we see the dropdown list compiled from our table of countries, if you put the cursor in the country field.

If you were to type in the list of values for the field in the “Lookup Wizard” you would see the following window:

where you can enter a list of your choice.

 

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats