How to Add a One-to-Many Database Relationship in Microsoft Access

Video Transcript

"Hi my name is Dave Andrews, today I'm going to show you how to create a one to many relationship in Microsoft Access. A one to many relationship in a database, typically refers to one item that should be tied to multiple items. This can be very useful when you're dealing with sales reports, and information where you may have one resource that has to show up on multiple items. So let's go to the database here, I'm going to launch Microsoft Access. I'm going to go to file, and say new, and we'll create a blank database. I'm going to call it my database. Let's begin by creating a table. To create a one to many relationship each of your tables must have what is referred to as a ID field. To create an ID field let's just name our field ID, and give it a data type of number. Now it's important that you ID field be what is considered a primary key field. This key field is what is used to identify each row in the table. So right click somewhere on this first row and select primary key. Now add a new field, I'll call it my data, and we can leave that as text. Let's save this as table one. To create your next table, let's create another table again give it an ID field number, right click in here and make it a primary key. Since you're doing one to many all of our tables must have IDs and primary keys. So let's call this my data two. And just leave that data type as text. So let's save this as table two. And now we're going to create the one to many relationship between these two tables. Table two is going to refer many items in table two are going to refer to one item in table one. So it's the one to many relationship. To do this let's go to tools and select relationships, this brings up your list of tables. We want to add both tables and then click close. Here they are in our relationship manager. To create the one to many in table one hold down you mouse on the ID field, and drag onto the ID field in table two. And then select create. You've now created the one to many relationship in Microsoft Access. My name's Dave Andrews, and I've just show you how to create the one to many relationship in Microsoft Access."