Learn How to Create Relationships and Master Them in Access 2007
- select the contributor at the end of the page -
This is the part that most of us get confused with because Access relationships (as any other relationships) can get quite complicated.
And let's face it, we all get a little confused when it comes to Access. But don't worry, help is on the way!
Today I'm giving you step-by-step instructions on creating basic relationships in Access 2007. Keep in mind that there is much more you can do, and that what's presented here is just the beginning. Once you have the basics, don't be afraid to start exploring.
Creating Relationships in Access 2007
For this example, I am using an already existing database with tables already created, primary keys assigned, and data types selected.
Nothing however, was setup when it comes to relationships so you should be able to apply these steps in your situation. Alright, let's get started.
1. First open up your database. In order to create or modify any relationships in your database, you need to switch to the relationships view.
So click on the Database Tools tab on the new Access 2007 ribbon and click on the Relationships button.
2. Since there aren't any existing relationships, Access will open the Relationships View together with the Show Table dialog box.
3. In this example I am using employee tables and we'll work on relating them together. But first, we need to select the tables and add them to the relationships view window.
Go ahead and select each table that relates to the other tables and click Add. I am selecting tables that relate to employees.
4. Once you are done selecting and adding all the tables, go ahead click on the Close button.
5. Now, I am creating a one-to-one relationship between my tblEmployees and the tblHRData tables.
But before I do that, I need to figure out where I want to start with my data entry. This is quite important so take your time.
In my example, I want to start with the tblEmployees table as I want the employee's first and last name, etc. to be entered first.
Starting with that table, I will click and hold on the primary key of that table, which in this example is EmployeeID. Then I will drag and drop it on the EmployeeID in tblHRData.
6. After this, the Edit Relationships windows will appear. You may want to check the box next to Enforce referential Integrity to verify the contents of the table during data entry.
In my example, I am going to leave it out. Go ahead and click on the Create button.
Congratulations! Your first relationship in Access has been created!
7. Before I continue, I want to rearrange my tables a little bit by what I am going to be connecting them to.
So the tblDepartments table will be next to the tblEmployeeInfo table which will be next to the tblEmployees table. The rest will stay the same.
8. You are now ready to create your first one-to-many relationship. On my end, I am going to create it between tblEmployees and tblEmployeesInfo.
To create a one-to-many relationship you do not need to specify the data entry order. It will be selected automatically by Access.
So, you can click and hold the ID from tblEmployeeInfo or click and drag the EmployeeInfo_ID from tblEmployees table. Either way works.
In my example here, I will click and hold EmployeeInfo_ID, then drag and drop it right on top of that ID field.
9. Once I let go of the mouse I get the Edit Relationships window. The main difference in this window from the window we got in the one-to-one relationship, is that you do not have a choice in what is going to be your primary field or table.
Like I said before, this is automatic. Access is selecting it by picking the primary key of a table. So even though we decided to grab the EmployeeInfo_ID first and drop it over ID, Access made sure that the primary key (which in this instance is the ID field) is the primary table.
10. Alright, so once you verify that your relationship is good, go ahead and check the box next to Enforce Referential Integrity and click the Create button.
Ta-Da!
11. Our next relationship for today is going to be between tblDepartments table and tblEmployeeInfo table.
I'm going to click and hold on Departments from tblEmployees and drag and drop it … but where? ID or Department?
This is a great example of a flawed naming in an Access database. The foreign key, which is Departments can be either the ID or the Department field in tblDepartments table. We can't really tell, can we?
The only person who knows this is the person who created the database. I happen to know that it is supposed represent the ID field, but what if someone else was creating the relationships? Kind of confusing, isn't it?
To avoid confusions like this, make sure you follow Leszynski Naming Convention when creating your tables and database.
To fix the problem for this example, I would need to change the Departments field name to Departments_ID or Department_ID. Something that would let everyone know that the foreign key here, represents the ID.
Ok, so let's go ahead and create the relationship by dragging and dropping Departments on top of the ID field.
12. And once again, make sure to check the box for referential integrity and hit Create.
13. And the final relationship we are going to create today is going to be between tblHRData and tblBenefits.
Notice that I have my Benefits field in tblHRData table setup to accept multiple values. You can create a relationship like this in Access, but you are creating it between the BenefitID field and the actual value of the Benefits field.
I am going to click and hold on the BenefitID field and drag and drop it on the Benefits:Value field.
And when ready, hit the Create button in the Edit Relationship window.
And that's building relationships in its most simplest form.
Master Access relationships, new features and capabilities with Microsoft Access 2007 Training!
Our comprehensive instructor-led videos focus on integrating Access 2007 with Excel 2007, Word 2007, and SharePoint Services to maximize the efficiency of your organization.
Get in-depth instruction on all of the new features in Access 2007 which make it easier to work with your data.
Find out more about our new Access 2007 Training!
Ready to test your skills in Access? See how they stack up with this assessment from Smarterer. Start this Access test now
