Access: Making a Relationship Between Two Tables

Microsoft Access is considered a Relational Database system. A Relational Database allows you to establish relationships within your data to tie them together to form a single data set. In our prior Query Introduction post we went over how to…

  • Build a query based onĀ one table
  • Filter via query criteria

In this post we will go over how to make a query that includes two separate tables to make one data set.


 

Files

Below are the tables I am using in my database. I’ve exported them into Excel so that you can import them into your database to follow along is you wish.

File:RelationalDB Tables

Below is what my database looks like once I add in all of the tables. None of my tables have a primary key, it is simply the data from the spreadsheet. Instead, I will be using EMPLID and Country/Country Code as my unique identifiers.

RelationalDB1


 

Building Relationships

Objective 1: Build a query to show employee names and hours
Objective 2: Build a query to show employee names, hours, and country/region descriptions

  1. Make a new query via the query design button on the “Create” tab.
  2. Bring in table Employee_Data and add the three fields to the query.
    Your query should look like the below.

RelationalDB2

Now that we have our Employee_Data, lets now bring in the Employee_Hours data.

  1. Add the table Employee_Hours to our query
  2. Add the fields from Employee_Hours to our query.

Note that you typically would not need another EMPLID field in your query but we are adding it here for the sake of instruction and learning.

  1. Run your query

Notice how your query is a jumbled mess and looks nothing like the data in either table. Joe for example is the only person that has worked 50 hours. What’s happening is that Access has no idea which employee hours goes with which employee. You haven’t told the query that EMPLID 12345, 50 hours, actually belongs to Joe which is causing Access to allocate each set of hours to each employee name.

RelationalDB3

  1. Go back into the design view of your query.
  2. Click on the EMPLID field in your Employee_Data table and drag-and-drop it on top of the EMPLID in your Employee_Hours table.

You should see a line connecting the two now. You have told access that these two fields are related and have joined them together. Run your query again and you should now see your employee Names and Hours correctly.

RelationalDB4RelationalDB5

Next we will go over joining multiple tables together and then the different types of joins/relationships you can make between your data.


If you liked this content and found it helpful, please like the page and leave a comment below. You can also ask questions on the Forum to get personalized help on your issues and questions.



Categories: Access

Tags: , , ,

Leave a Reply

Show Buttons
Hide Buttons
%d bloggers like this: