"Creating Access Relationships creates a family of data information harmonising the use of queries and reports..."
Access Relationships enable data in one table to be joined to data in another table which generally serves as the basis for your reports (typically via a query).
Without relationships in place, you are restricted to how data is managed from the reporting point of view and each table sits as a 'stand-alone' file.
As part of the database normalisation process , setting Access relationships keeps to the rules and general good practice of RDBMS Relational Database Management System.
Joining tables together can be set in one of three ways:
- Table level via the 'Relationship' Window
- Query level via the 'Query' Window
- Both Table and Query levels
In order to join two tables together, there are rules that must be observed which are:
1. Field Data Types - the fields which are to be joined across two tables must be the same data type. Therefore a 'Text' data type cannot join to a'Number' data type field.
The exception to the above is a 'Number' data type can join to a'AutoNumber' data type providing both are set to same size (i.e. Long Integer).
2. Field Sizes - applies to only the 'Text', 'Number' and 'AutoNumber' data types where the size or length of a field can be modified. For the numeric data types, the size must be the same and therefore a 'Number' data type field size in one table which is set to 'Double' cannot join to a field whose size is set for example to 'Integer'.
The 'Text' data type is a little more relaxed as the joining field must be at least the same length (or greater) of characters (bytes)
Note: It is possible to join different field sizes with 'Number' data types provided the field can be cast and fit into the larger size. This is not good practice and in any event shouldn't the data type be the same across tables for the same data value?
3. Field Names? - this is not essential as field names are not checked when joining tables together. But for user-friendliness, having the same field name makes life just a little bit easier!
As a reminder, you may want to review data types and sizes before setting any primary or foreign key indexes.
Setting the correct keys (primary and foreign) must be considered and applied before joining tables together.
The type of key applied will lead to the type of Access relationship you can have and in essence there are four:
- One-to-one
- One-to-many
- Many-to-one
- Many-to-many
The 'one' side is associated to a unique value for a field and is referred as the primary key. The 'many' side is an indexed field which allow for duplicate values in a field and is known as the foreign or secondary key.
To get a better understanding of these keys, please review primary and foreign keys.
One-to-One This type of Access relationship is seldom used but it allows you to join two tables together whereby each row (record) in table A has a related row (only one record) in table B.
Types of uses for this setup could be a way to overcome some management, performance or sensitive data issues. For example, if you had two tables dealing with employee information where one table Personnelstores general profile information including surname, ID and contact information and the other table Payroll containing salary and other sensitive data, then there would only be one record per individual in each table(joined by a common unique value - 'Employee ID').
Other times, this type of Access relationship may be used is when overcoming limitations and performance problems. For example, for a table to hold over 100 fields (maximum is 255) will require a lot more memory than a table with far less. If you had a client table which held a lot more profile information than the normal, consider splitting into two tables; table A to hold the main essential fields and table B holding the less frequently used data (especially when using 'Memo' and 'OLE Object' data types). The common field would be unique in both (i.e. Customer ID).
One-to-Many / Many-to-One This is the most common type of Access relationship where a single record in one table is joined to one or more records in another table with the same matching data value from the unique value of the first table.
There are many examples to highlight but imagine if you have a table calledCustomers which contains the client's details including name, address and contact and another table of transactions called Orders where a customer can place more than one order over a period of time. The common field between the two is the Customer ID and is unique from Customers but duplicated in the Orders table for each instance an order was placed for the same customer.
This type of Access relationship is sometimes referred as parent-child ormaster-detail. Terminology sometimes used when talking about a customerhaving never placed an order is referred as a widow record (a parent with no children). The other way around where an order does not have a knowncustomer is referred as an orphan record.
Many-to-Many This type of Access relationship cannot be created directly in Access and therefore will require three tables. It is where in table A, a record can refer to one or more records in table B and table B can be linked to one or more records in table A (a two-way 'one-to-many/many-to-one' link) but requires a third interim table C!
This relationship happens by default if you have created multiple tables using the database normalisation techniques and have joined tables together using the 'one-to-many' relationships.
For example, for each order stored in the Orders table which holds the header information including date, customer ID and shipping information has a second table which stores the item details that make up the order calledOrder Details where the Order ID field is common to both tables causing a'one-to-many' relationship. For each item in the Order Details table, a product is held identifying the item and its product information which in turn is held in a separate table called Products containing name, stock levels and price. The relationship between Order Details and Products is a 'many-to-one' where the common field is Product ID.
So we have three tables; Orders linked to Order Details linked to Productsand therefore we have an indirect relationship between Orders and Products (the Order Details table sitting between the two).
In the 'real world', this would be a typical relationship because you have one order with many products (items) and the product is sold across many orders. The middle table Order Details handles the two way relationship.
In addition to Access relationships, you also have three different join types to choose from:
- Equi-Join (Inner Join)
- Left-Join (Outer Join)
- Right-Join (Outer Join)
Equi-Join By default, when you join two tables together, it sets it as anEqui-Join also referred to as the Inner Join.
It tells Access to show only records from the two tables where they both have the matching values. Any record in either table that does not have a corresponding record in the other table is simply suppressed from view.
For example, if we continue to use our Customers and Orders table relationship scenario of a 'one-to-many', it would only return records where the matching Customer ID field existed across the two tables. Therefore, it will display all customers that have placed at least one order and only display orders that have a known customer account.
Any orders that do not have a matching Customer ID will be suppressed and in the 'real world' this would be unusual due to the fact you first would choose and assign a customer before placing the order!
However, it is possible to have a customer as a new prospect that has yet to place their first order and therefore would also be suppressed with this type of join.
Note: Access relationships cause and effect - Before adding any criteria, you will find records have been filtered because of this type of join. If you had 99 out 100 records in table A each having 3 records from table B with the remaining 1 record not having any records from table B, then the result recordset would be 297 (99 X 3) suppressing the 1 record.
Left/Right-Joins which are also known as Outer Joins change the relationship link. Access will force records to be displayed whether there is a matching value or not.
In Access, you can only set one direction of outer join (left or right) at time and it tells the system from which direction to force the records to be all shown.
For example, using our Customers and Orders table scenario, creating aRight-Join (pointing from Customers to Orders) will show all customers whether they have orders related or not.
Where there are records from Customers which do not have any related orders are said to be Widow Records (parent with no children).
Using the Left-Join example for the above tables will force all orders to be displayed and only related customers.
This type of example may not be different from an Equi-Join if the relational database has been designed with care as in the 'real world' you wouldn't really have an order issue without first choosing a customer!
This type of join will be driven towards data that may have been imported from an external system where Access cannot deal with those orphanrecords (records without a parent).
There it is, Access relationships understood!
Don't get too wrapped with up different joins, keep it simple and as you develop the database you will learn to modify the rules.
The next step is to create the joins which is covered under how to apply the joins and create a relational database.