Sharing Data Between Tables
One of the big benefits of a database is that you can share data between different tables. In a relational database, you can create a relationship between different tables that allows data to be shared.
For example, you could have a database with three tables ...
- Customers - store all the contact details for each customer
- Products - store all the details for each product for sale
- Orders - record which customer has purchased which product and when
We could create a relationship between the orders table and the customers table. When a new order is created, the customers' details can automatically be added to it
We could also create a relationship between the orders table and the products table. When a new order is created, the product details are automatically added to it.
Creating Related Tables of Data
Before you can create your relationships, you need to make sure you have the three different tables created and saved in your database.
In this tutorial, the three tables are ...
Make sure you have the following fields in your orders table ...
|OrderID||Autonumber||Primary key field|
Sharing Customer Data
Every time we enter a new order into the database we need to know which customer the order belongs to.
Rather than typing in the customer name each time they place an order, we can get the database to share this information between the customer table and the orders table.
Change the data type for the CustomerID field to Lookup Wizard.
In the Lookup Wizard, select that you want to get values from another table or query (this is the first option). Click .
We are going to lookup the name of the customers. Select the customers table and then add the CustomerID field, first name and surname fields into the list. Click .
Sort the list into alphabetical order by surname and first name. Click .
Finally, click on the button to complete your list.
When you enter data into your orders table, you can select the name of the customer for each order rather than typing in the customer name.