A Brief Guide To Table Relationships In Access

If you're starting to use Microsoft Access you may be able to create tables and queries and perhaps Forms and Reports. During your work with tables you may have also created links between tables. This article describes some of these linked relationships and how they can be used. For a fuller understanding of table relationships it is best to consider attending a Microsoft Access training course to learn lots more about this.

Table relationships in Access come in three varieties. These are one to many, one to one and many to many. The first type is the one to many relationship. For example one customer can place many orders. A company database might contain a Customers table and an Orders table. The Customers table lists all current customers, with table headings customer ID, customer name, address, town etc. The customer ID column will be a unique code to identify each customer.

The Orders table lists all current orders, with table headings order ID, customer ID, order date, number of items, cost etc. The database designer would have created a link between the customer ID field in the Customer table and the customer ID field in the Orders table. When the sales assistant takes a new order from an existing customer they would open the Orders table, add a new order record and this would include in the customer ID to identify the customer making the order.

Later the finance clerk would run a query using the two tables, to prepare the invoice. The invoice query would use the customer ID from the Customers table to extract the customer name and address and the order ID to extract the order number of items and cost from the Orders table. The linked tables would allow one customer to make many orders. This is a one to many relationship.

The second type is the one to one relationship. For example one employee has only one company car. Suppose our company staff need company cars to travel around the country. Our company database has a Staff table to list all out staff details with headings; staff ID, name, post, date joined etc. We also have a Cars table to list all the company cars with headings; car ID, Staff ID, make, colour etc. Our database designer has created a link between the staff ID field in the Staff table and the staff ID in the Cars table.

Then the duty manager assigns each car to a member of staff by adding one record in the Cars table per car. The staff ID in the Cars table identifies which member of staff has that car. And the database designer has configured the Cars table to allow only one car per member of staff. So the linked tables would allow one employee to be assigned to only one car. This is a one to one relationship.

The third type is the many to many relationship. For example many doctors can see many patients. Suppose you visit your doctor one day, get seen and go home again. You still feeling unwell so you visit your doctor again a few days later. These days many people attend a medical centre rather than a doctor's surgery, so on this second occasion you're seen by a different doctor. In fact there's a pool of doctors who see all the patients. OK you personally might still be visiting a doctor's surgery, but I'm sure you get the idea.

The medical centre database has three tables, not two, to achieve this. There's a Doctors table listing details for all the centre doctors with headings doctor ID, name, speciality, date joined etc. There's also a Patients table listing all the registered patients with headings such as patient ID, name, address, contact tel etc.

The database contains a third table which is used to link between the other two tables. The Link table has these two fields, doctor ID and patient ID. The designer links the doctor ID field in the Doctors table to the doctor ID field in the Link table and the patients ID field in the Patients table to the patients ID field in the Link table. The Link table is used by the receptionist to record every visit, so the table also has the fields Visit ID, Visit Date, Outcome etc.

To record each medical centre visit the receptionist adds a new record to the Link table. The doctor ID field is used to extract the doctor's details and the patient ID field to extract the patient details from the Patients table. These three linked tables allow many doctors to see many patients. This is a many to many relationship.

So there you have it - table relationships in Access. Relational databases such as Access allow users to relate data in one table to data in another table through these linked relationships.