Working with Tables
Typically, when you create a relationship between two tables, you set that relationship on one common field. A common field is a field in each of the two tables that has the same data in it.
Say you keep your customer data in a “Customers” table and your order data in an “Orders” table. You assign each of your customers a unique ID number, which you enter in a field called “Customer ID” in the “Customers” table. When a customer places an order, you create a record for that order in the “Orders” table. To link that order to the customer who placed it, you make that customer’s unique ID number a part of the order record. You do this by entering the customer’s ID number in a field called “Customer Number” in the “Orders” table. The “Customer ID” field in the “Customers” table and the “Customer Number” field in the “Orders” table, then, contain the same data, and they are said to be the common field between these two tables.
The common field in the table on the “one” side of the relationship – in our example, the “Customer ID” field in the “Customers” table – has to be indexed and is usually, though not necessarily, the primary key field for that table. The values in that field must be unique. (Think about it: If you assigned the same ID number to two customers, could you use that number to link an order to a specific customer?)
Generally, the two fields have to be of the same data type: text-text, number-number, date-date, whatever. The main exception is that an AutoNumber field can only be linked to a Number field with Long Integer format.
Note that the common fields don’t have to have the same name – so long as you follow the rules above, you can set a relationship on two fields no matter what their names are.