6. Microsoft Access 2016 Basics: Many To Many Relationships

In this video on Microsoft Access Basics: Many To Many Relationships, Steve Bishop give you a tutorial on how to create and organize your data in a many to many relationship. After watching this lesson you will know how to create a many to many relationship using a join table as well as establish the referential integrity of that relationship.

This how to video tutorial is part of a series of videos that are tutorials on various topics involving Access 2016, visual basic for applications (vba), structured query language (SQL), and basic form design (UI). These lessons on Access 2016 will help you create applications for your business or personal use that will make you more efficient at organizing your data.

Click here for the Work Files for this series:

Click here for the Microsoft Access 2016 Basics Playlist:
----------
Become a member today for only $5 a month!

When you join, here’s what you’ll get:
* Your name in the credits
* A form to ask me for help on your project
* 10% off in the Teespring store (discount code)
----------
Looking for DAILY news and commentary? Join us on my other channel “Coffee With Steve” for daily videos where we discuss Technology, Software Development, Politics, Culture, and many other things.

----------
#MicrosoftAccess, #VBA, #SQL, #Access2016, #Programming

[goldilocks]

This video saved my brain! I really appreciate all of the details you've given about good database construction. Many tutorials I've come across have just focused on the mechanics of how to use Access, and I think that the context you give is very important. This series is looking like a very valuable refresher on database construction in general for me, alongside its instruction in Access 2016. Thanks for making these!

[GaryWilsonDigital]

This is the best example I’ve seen of the many to many relationships. Great job!

[captainkeyboard]

That was a fine show. I like to see other people take advantage of computer technology.

[farinatty]

Hey Steve your access database tutorials are the best. Would be good to see a few more in this series.

[castor]

A great way also to show how to construct a telephone directory with multiple numbers. Thank you :-)

[hyoga]

Thanks very much for this it works like magic

[deholovi]

I'm stuck with sub datasheet view! for example, I've 3 separate tables. wanna relate them with one junction table. say, number 2 table has a common field ( could be described as the master field ), which is related to two other fields. one from number 1 table and another from number 3 table. after creating the relationship, when u press the " + " sign of the sub datasheet view, " insert sub datasheet " dialogue box appears! why so? I just wanna see related fields to that particular field. why that doesn't happen?

[bryanmarquez]

Hi Steve, any tutorial on how to handle Data Entry/Editing/Deletion, with the many to many relationship on Forms & Subforms etc?

[KMANWEBER]

Thanks Steve. Can you please create a video that demonstrates how to update a many to many relationship through a form/queries? Thanks

[garycurtis]

Excellent video, I have a question. Do you use VBA code to update these tables simultaneously for the join table? Or do you update them one at a time using VBA or other means?

[matthewb]

The problem I see with this video is that when we are referencing people we reference them by name not by number, and we select phonetype not by number but by type. Presumably we are working in forms so we want to see the related field phonetype (string) and person (string) . How does that side work? Is it a subform? Or can the related fields just be dropped into the form?

[guillermoperez]

Hi Steve: A question(s). Why did you make the fields in the People_Phonenumbers table a Primary Key? Could you just have left them as Foreign Keys? It sort of breaks the rule of linking Primary to Foreign Keys. In your example, you linked Primary to Primary.

[joycemaxfield]

How can I transfer aDatabase from my laptop to a flash drive

[DArts]

Hi Steve - I have been following along with your video's and would like to take your many to many example to create a query to update data in my employee and phone number tables. I am also using the Phone types table and have set them up as per your video. Can you show how to create a proper query so that updates are possible to both the employee table and phone number table? I keep getting the ambiguous outer join error from access.

[Mechrank]

Great tutorial, i want to model a database structure by seeking your help.

The basic information that i’d like to store in this new database will be:

1- Machinery (manufacturer, model, water flow rate requirement)
2- Connection to an existing pipe network

Can i do this using MS access database?

[robinbrown]

Good morning Steve, after viewing your videos regarding erd several times due to inheriting an access database, I am totally confused since I did not create the database. I tried to use one of the templates that access has to offer, but that made me even more confused
Training_ID (AutoNumber)
Student_ID - (Number) and other attributes
2018 A Training Day
2018 B Training Day
2018 C Training Day
2018 D Training Day
2018 E Training Day
etc...

Student can go to class anytime during the year, but the training is not complete until all 5 days are complete and the training happens every three years.
After viewing your videos I understand that the database does not contain any normalization and the training days should not be listed as a field, but I am not sure what I should do.
Not sure if you have a video that would help me with one of the problems that I see with this database.

[roxinnjeru]

When i try creating a relationship between the the PhoneTypes and PhoneNumbers i get a message that reads"Relationship must be on the same number of fields with the same data types" How should i solve this?

[nancyd]

He released a memo... not the transcript...

[arete_]

This is a horrible way to manage data. Just because you remember the phone number ID from the top of your head does not mean you will remember that next time. So now you will have to check the ID everytime... 😲

[unicockboy]

So you´re telling me that a 1 year-old has a 50k salary??