Sharing Data Between Tables

Diving Line


If you need to print this page out, type your name in the box below.

Your Name : 


Information

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.


Information
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 ...

  • Customers
  • Products
  • Orders


Databases


Make sure you have the following fields in your orders table ...

OrderID Autonumber Primary key field
CustomerID Number
ProductID Number
OrderDate Date/Time
ProductCost Currency
VAT Currency
OrderTotal Currency
Notes Long Text


Databaes


Information
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.

Databases


In the Lookup Wizard, select that you want to get values from another table or query (this is the first option).  Click Next.

Databases


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 Databases.

Databases Databases


Sort the list into alphabetical order by surname and first name.  Click Databases.

Databases


Finally, click on the Databases 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.

Databases


Information
Sharing the Product Information

Now we need to create a list for the ProductID field, so the product name and price is displayed.

Repeat the steps above for the ProductID field.

Databases


PrintIT  Print out this Page

Next  Types of Relationships


Last modified: Monday, 7 December 2020, 3:08 PM