Unit 6 exercise help. relationships


Exercise 1: Distributer

In parts 2 and 3 the structure of the tables needs to be edited:

1 Go to table design, by selecting it and clicking on the button in the Database window.

2 Add the field at the end of the table.

3 Close the table.

If you have problems revise unit 4.

 

In part 4 you must create two relationships:

One relationship of one-to-many between Clients and Sold cars. As Sold cars contains the client code this table will be the related table, and the Clients table will then be the primary table.

1 Click on the button on the toolbar.

As the Relationships window is empty we will first need to add the tables.

2 Select the Clients table.

3 Click on the Add button.

4 Click on Sold cars.

5 Click on the Add button.

6 Click on the Close button.

And now define the relationship:

7 Enlarge Sold Cars representation in order to see Client field.

8 Click on Client code field in Clients table.

9 Drag it onto Client field in Sold Cars table.

10 Check Enforce Referential Integrity box.

11 Do not check Cascade Update Related Fields and Cascade Delete Related Records boxes (it should not allow a client to be deleted if he has sold cars)

12 Click on the Create button.

On clicking the button it is possible that an error message might appear, in this case you will need to press the Cancel button, correct the cause of the error and try again to create the relationship. In the message that appears you will be told what the cause of the error is.

If the message is No unique index found.... it is because you do not have the Client code field defined as the primary key in Clients table.

If the message is Relationship must be on the same number of fields... it is because the Client code field in the clients table is not the same type (or field size) as the Client field in the Sold cars table, check data type and size.

If the message is Microsoft Office can't create this relationship and enforce referential integrity... it is because in the Sold cars table you have a record (or various) with a value in the Client field that does not exist in the Clients table.

It is often due to the predetermined value of the Client field in the Sold cars table. The numeric fields by default have a predetermined value of zero, and so when a new record is created it is assigned a value of zero as long as we don't type anything. The zero is not the same as a null value, and so Access understands that the sold car is assigned to the client whose code is zero, whilst in the Clients table there is no client with a zero code, and therefore the error. For this reason it is convenient to remove the predetermined value in those fields that intervene in relationships.

If no error message appears, create the relationship and it will appear in the Relationships window.

The next relationship to define is between Sold cars and Services. As Services contains the license of the car, the Sold cars table is now primary and the related table is Services (a car can have several services and a service corresponds to only one car).

As the Sold cars table is already in the Relationships window only the services table needs to be added. Assuming that the Relationships window is still open, if not then open it.

1 Click on .

2 Double-click on Services table.

3 Click on the Close button.

And now define the relationship:

4 Enlarge Services representation in order to see Car field.

5 Click on License field in Sold Cars table.

6 Drag it onto Car field in Services table.

7 Check Enforce Referential Integrity box.

8 Check Cascade Update Related Fields and Cascade Delete Related Records boxes (we have decided that cars that have services can be deleted, and in this case these services wil be automatically deleted).

9 Click on the Create button.

The relationship is created and this appears in the Relationships window.

10 Close the Relationships window by clicking on the button.

A dialogue box will appear.

11 Click on the Yes button to save the window.

In parts 5 and 6 the corresponding tables need to be opened and the data introduced.

If you have problems remember what we explained to you in this page on Referential Integrity when we created the relationship between Clients and Sold Cars.

 

Exercise 2: Clinic

 

In part 2 the structure of the Entries table needs to be edited:

Do the same as in exercise 1.

 

In part 3 you must define two relationships, one between Patients and Entries, and another between Doctors and Entries:

1 Click on the button on the toolbar.

2 As Patients table is selected, click on the Add button.

3 Double-click Entries table.

4 Double-click Doctors table.

5 Click on ther Add button.

6 Click on the Close button.

To define relationships:

7 Enlarge tables representation in order to see all the fields in each table.

8 Click on Patient code field in Patients table.

9 Drag it onto Patient field in Entries table.

10 Check Enforce Referential Integrity box.

11 Check Cascade Update Related Fields and Cascade Delete Related Records boxes.

12 Click on the Create button.

Now we will implement the second relationship:

13 Click on Doctor code in Doctors table.

14 Drag it onto Doctor field in Entries table.

15 Check Enforce Referential Integrity box.

16 Check Cascade Update Related Fields and Cascade Delete Related Records boxes.

17 Click on the Create button.

18 Close the relationship window by clicking on the button..

 

In parts 4, 5 and 6 the corresponding tables need to be opened and the data introduced.

 



 
Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.