Setting up Table Relationships
In Microsoft Access Ribbon, there is an entry in the 'Database Tools' Tab called Relationships. It should be used to maintain the integrity of the relations in your database.
Previous discussions have discussed the master tables and subordinate tables. The more common terminology is either 'master/detail' or 'parent/child'. For this discussion, we will use parent/child.
You can create the rules that Access will use to maintain the integrity of the relationships between your parent/child tables by using the relationships screen.
In the diagram (above) there is a parent/child relationship between Author and Books. The field called AuthorID links these tables.
Enforce Referential Integrity
When creating the link you will want to mark the 'Enforce Referential Integrity' check box and the 'Cascade Delete Related Records'.
By checking the ‘Enforce Referential Integrity’ setting, the insertion of 'orphaned' records is prevented. An orphaned record is defined as having a child record without a matching parent record.
Cascade Delete Related Records
In a very similar fashion, the ‘Cascade Delete Related Records’ option prevents orphaned records when you delete a master record. When this box is checked, Microsoft Access will automatically delete the associated child records.
All relational database management systems have this automatic capability option.
Cascade Update Related Fields
This check box is for those Access users who use actual data to link two tables. You should only need to check this on the rare occasion when the ID field is an internally generated integer. An example is an Employee_ID that is equivalent to an auto number field.
When setting up a relationship there is a button for 'Join Type'. The Join Properties popup has three choices.
The first choice is 'Only include rows where the join fields in both tables are equal'. This is the choice that you will most likely pick 99% of the time. One way to think of this is that you would not want to put a foreign key into a child table if you didn't need it for all records.
In those few cases where you just won't have a value for the foreign key in the child table you will need to use the 2nd or 3rd option in the Join Properties popup. This option is usually referred to as an 'outer join'. This tells Access to get all the records from the parent table and to get any records that happen to have a matching key in the child table.
Judicial Attorney Services Inc.
You continue to do a job well done with the design, updating and maintenance of our company's program. Work is performed in a timely and professional manner.
A Trucking and Warehousing Company
Their ability to understand our industry from the ground up - just like one of my coworkers would - is their key differentiator.
- Customer Service Manager
A Services Company
I needed to believe that Access Programmers could help my company. Their team understood my hesitation and took the time to really understand my business needs.
- Regional Sales & Operations
A Non-Profit Advocacy Firm
Access Programmers has completed several projects for us - all of which we've been extremely satisfied with. The staff is very knowledgeable, accessible, professional and easy to work with.
- Information Technology Manager