You can relate tables by using a Database Field while in Form mode to see data from a field in another table. The following example shows you how to go about this.
Example
You have two tables, Orders and Products, as shown below. It is your aim to design a form that will show, for each order, the product name, the quantity, and the cost per item.
Orders
Order_ID |
Product_ID |
Quantity |
ORD1 |
A003 |
5 |
ORD2 |
A001 |
7 |
ORD3 |
A003 |
4 |
ORD4 |
A002 |
3 |
ORD5 |
A002 |
2 |
ORD6 |
A004 |
6 |
Products
Product_ID |
Description |
Cost |
A001 |
File |
2.99 |
A002 |
Lamp |
25.50 |
A003 |
Pen |
1.20 |
A004 |
Fan |
18.99 |
Open the Orders table.
Click on the View Form button on the toolbar to open up an auto-form based on the Orders table.
Click on the Design button.
You now need to insert two lookup fields from Products, namely, Description and Cost. You will need to insert each field separately, using the "Insert Field Wizard". To see how to do this, go to Inserting a lookup field.
Note: In this example the following selections should be made in "Insert Field Wizard". Since the field that connects the two tables is Product_ID, you should select it as both the Data Field and the Lookup Field. Select the Products table as the Lookup Source. Since we want to show the product name rather than just the Prodcut_ID, we will choose the Description field from the Products table as the Display Field. The same selections are made when inserting the second lookup field, only this time you should select Cost as the Display Field.
Once you have inserted the lookup fields you should return to browse mode to scroll through the records. The two lookup fields will display the name and the cost respectively for each order.
You can hide any field you don’t want to see in your form (see Hiding fields from the form).