Linking objects

Dear Rebecca

Hope you enjoyed your training with Best last week. Thank you for your question regarding linking tables.

When you create a relationship between two tables, you first need to decide what kind of relationship the two tables have.

There are 2 main types of relationships

- One to Many
This means that one record in the one table is related to many records in another. For example, one supplier may have many products, or in your case, one person might attend many events (however, they do not attend alone, as there are actually many people at the event, hence the next type of relationship - many to many).

- Many to Many
This means that many records in the one table is related to many records in another. For example, many customers buy many products, or in your case, many people attend many events.

We did not cover this in your course, as the complexity increases rapidly and the technical knowledge set it up correctly is fairly deep.

I can give you the basic steps for a one to many relationship, using suppliers and products as an example.


1. Create two tables - Supplier and Product
Ensure you have a primary key set in both tables.

2. Create an additional field in the Product table, called Supplier

3. Close and save both tables. Then go to the design view of the Products table.

4. Choose "Lookup Wizard" as the DATATYPE for the SUPPLIER FIELD of the PRODUCTS table.

5. Follow the wizard, and point it to the SUPPLIER NAME field from the SUPPLIER TABLE. Include the Primary Key, but do not show it in the view.

6. Complete the Wizard, and then save the table. View the PRODUCTS table in datasheet view.

7. You should now be able to select the Supplier name in the supplier field from a drop down box. Allocate the Suppliers to the products in this way. Once done, save and close.

8. The Last step is to create a QUERY - Show both tables. You should have a line between the two tables, as they have a relationship. If not simply drag the common field (SUPPLIER) from the one table to the other, using the mouse. A window will pop up asking what types, just click ok.

9. Show the fields relating to the supplier and product tables, as required, and then run the query.

10. Group and Filter as required.

11. Create report as required from this query.


In this way, if your supplier detail changes, then the update happens in one place and the result spreads throught the enitre system, rather than having to have multiple entries to change.

Hope this helps.

Let me know if you have any furhter questions.


Kind regards


Richard
Microsoft Office Specialist Trainer