Locked lesson.
About this lesson
Understand how relationships work in Access and why they are fundemental to building a functional database.
Exercise files
Download this lesson’s exercise file.
6.01 relationships-explained - Exercise.docx108.3 KB
Quick reference
Relationships Explained
Relationships are a way of linking common fields between tables together to ensure additional information is accessible.
When to use
Create relationships between tables whenever you want to interrogate data across multiple tables.
Instructions
Access is a relational database management system. We create tables to hold our data and then link those tables together so that information across the tables is accessible.
To link tables together there needs to be a common field, a field that exists in both tables. This concept is comparable to Excel's VLOOKUP function. When we link tables using a common field, the data in the linked table becomes accessible.
Sometimes tables that we need to link do not share a common field. In this scenario, we would need to create a link table that contains a field from one table and a field from the other. We can then create a relationship between the link table and the other tables to achieve the same result.
Hints & tips
- It's important that common fields have the same field name (column header).
- 00:04 In this section of the course we're going to introduce the concept of creating
- 00:09 relationships.
- 00:10 Now currently, we only have one table, but that is going to change very shortly and
- 00:15 we are going to start adding other tables into our databases.
- 00:18 Now when we add tables,
- 00:20 they are effectively individual entities not linked in any way, shape, or form.
- 00:25 But what we can do is, we can create relationships between tables using
- 00:30 common fields, which opens up more information for us to interrogate.
- 00:35 So let me show you what I mean.
- 00:37 Now, I've just got a quick example on the PowerPoint slide here of a couple of
- 00:41 different tables.
- 00:42 Notice that in the first table we have three columns, ID,
- 00:46 ticket number, and customer name.
- 00:49 And in the second table, I have two columns, ID and priority.
- 00:54 So currently, if i'm doing searches on this first table, I can only really
- 00:59 search for ticket number, or customer name, and I could use ID to do that.
- 01:04 Well, what about if I wanted to access the priority information for each customer,
- 01:09 where the priority information is stored in a different table?
- 01:13 And bcause these two tables aren't currently linked,
- 01:16 I can't access this priority information by interrogating this table.
- 01:20 So what we could do is create a relationship between these two tables and
- 01:25 link them together.
- 01:26 And how do we create a relationship?
- 01:28 Well, for this we need to use what we call a common field.
- 01:32 Now, if you're used to using something like VLOOKUP in Excel,
- 01:36 this is a very similar concept.
- 01:38 Because what we're doing here is we are finding the field that is common
- 01:43 between the two tables.
- 01:45 For example the ID field that's we have a field that exists in both tables,
- 01:50 we can link them together using that field.
- 01:53 But what if we have two tables, or
- 01:55 maybe multiple tables where there is no common field?
- 01:58 Well, this is where we would use a link table.
- 02:03 So if we take a look at this second example, on the left,
- 02:06 I have the ticket table, on the right I have a priority table.
- 02:11 And I need to link these two tables together.
- 02:14 But because I don't have a common field, I essentially need to add a new table or
- 02:20 create a new table that contains one field from the first table.
- 02:24 And one field from the second table.
- 02:27 And I can then use that table to effectively link the ticket table and
- 02:32 the priority table together.
- 02:34 And we can do this across multiple different tables not just between two or
- 02:38 three tables.
- 02:39 So that is the concept of creating relationships and
- 02:43 it's what we're going to explore in the next few lessons.
Lesson notes are only available for subscribers.