Microsoft Access Many-to-Many Relationships

welcome to another free microsoft access tutorial brought to you by access learning zone comm in this tutorial i'm going to teach you about many to many relationships I have a very simple database set up here with two tables in it I've got a driver table with a list of drivers driver ID first name last name and a vehicle table set the vehicle ID and a description now if I wanted to set up a system where a driver could have many vehicles I would simply have a one-to-many relationship and that's pretty easy to set up in that case all I have to do is add a driver ID down here in the vehicle table and that sets up a simple one-to-many relationship right design view I'll add a driver ID right here as a foreign key so that's the number of type long integer and I've got several other tutorials that teach you how to do this this is pretty straightforward I'll save that and then reopen it again now I've got the vehicle table and the driver table and I can specify a driver for each one of these cars let's say Rick Ross driver one has both of these driver two has that one driver three has that one and Driver for Bill Williams doesn't have any it's a simple one-to-many relationship now the problem with a one-to-many relationship is that the Chevy Camaro here can only be driven by one driver I have no way to say hey this vehicle may be driven by multiple drivers let's say this isn't a question of ownership it's not who owns what vehicle let's say you have a fleet of vehicles for your company and you have to be able to track which drivers are in which vehicles from time to time so we need a better system than just a one-to-many relationship to track that we need a many-to-many relationship many drivers and many vehicles each driver can drive multiple vehicles and each vehicle may have multiple drivers how do we set that up to set up a many-to-many relationship we need a third table sometimes called a junction table or a cross-reference table and that allows you to say this vehicle to this driver this vehicle to this driver and so on now you're not limited to having a vehicle belong to one and only one driver how do we set this up an access well let's get rid of that driver ID that we added in here we don't need it delete are you sure yes I'm sure goodbye all right save the table again now let's create a new table to act as a junction table create table design you can put an ID in here if you want as an auto number we're not really going to use it you want a driver ID that's going to be a number because it's a foreign key pointing to the driver table then a vehicle ID that's also a foreign key save this I'll call this my Junction T or junction table primary key to find sure now let's open up all three tables so we can see what it looks like okay there's all three tables I've got my driver table my vehicle table and my junction table now I can come in here and say driver one drives vehicle one driver one drives vehicle two driver two sometimes drives vehicle one and driver two also may drive vehicle for let's say all right driver three may drive vehicle one vehicle one's popular and so on you can see how we can specify relationships between any combination of these objects now of course this is just one example you can use many to many relationships for all kinds of things for example in my database I've got customers and categories what category does each customer belong to business classifications for example what type of business is it and each one of those categories or classifications can belong to multiple customers and vice versa in my access relationship seminar I go over a couple of different examples using different types of relationships one-to-many one-to-one and of course many to many and one of the examples that I use with a many demanding relationship is products and vendors you may have multiple products that you carry and each of those products can belong to multiple vendors you may get the same keyboard from three different vendors for example and you want to be able to check which one is the cheapest okay each product can belong to multiple vendors and each vendor of course will have multiple products that's a many-to-many relationship and I'll show you how to set that up in my access relationship seminar so that's how you set up a many-to-many relationship in an Access database I hope you learn something and again for a lot more information on many-to-many and other types of relationships go to my website at access learning zone comm and look for the access relationship seminar I'll put a link below the video and of course if you have any questions or comments please feel free to post them thanks for watching you

Channel: Computer Learning Zone

Views: 30,401

Rating: 4.721519 out of 5

Keywords: Many-to-Many Relationships, Junction Tables, Cross-Reference Tables, microsoft, access, microsoft access, tutorial, microsoft access tutorial, ms access, Microsoft Access (Software)

Id: 9BTTQEtWw5Y

Length: 5min 37sec (337 seconds)

Published: Tue May 14 2013