Microsoft Access Table Relationships
Microsoft Access is a relational database: it allows you to link Tables together using primary and foreign keys. A primary key is a field that contains a unique value for each record in a table, for example a hospital patient number or employee number. The foreign key will exist in another table that needs to reference records in the first table.
The foreign key essentially holds that primary key value and therefore references the rest of the data held in that record. Primary and Foreign keys must be defined with the same data type and field size; you will probably find it helpful to give the fields the same name although this is not essential.
A Primary Key field will only hold a value once but a foreign key field can reference each value many times. For example a table holding employee details would be referenced many times in another table recording quarterly staff reviews. This is, however, only one type of relationship.
There are in fact 3 type of table relationships in Access:
One To One
This relationship occurs when you link two Primary Keys together so that every record in one table has an associated record in another table. The Primary Key values in each table would need to be identical for this relationship type to work. You might want to split employee data in two tables as some of it does not need to be seen by anyone but senior members of staff. You would therefore link the two sets of data via a One to One Relationship.
One To Many
This is the most common type of relationship and the one described first of all. If a training company created a database they would need to keep records of delegates in one table and course bookings in another. The bookings table would hold a Foreign Key for delegate records so that delegates could be booked on one or more courses. The relationship is a Primary/ Foreign Key Relationship with each unique value in the first table potentially referenced many times in the second table.
Many to Many
This is essentially two sets of One to Many Relationships using a third junction table. If we take our training company again we could have the delegates table, the booking table and the course table. The delegate and course table would both have a one to many relationship with the booking table allowing any delegate to be booked on many courses and each course to hold many delegates. The booking table becomes the junction table.