Using Many-to-Many Relationships in Microsoft Access to Track Employee Certifications, Courses, etc.

Learn how to create a many-to-many relationship in Microsoft Access. This is useful to track relationships between multiple records on BOTH sides of the relationships. For example: students to classes. Each student can be enrolled in multiple classes, and each class can contain multiple students. I'll show you how to set up a Junction Table to track these relationships.

Lucas in Anchorage, Alaska (a Platinum Member) asks: "I need to track employee training, when they took the certification test, and when their certification expires. I need a list of which employees have certifications expiring soon so that I can send them for retesting. I'm having trouble matching up the employee to the certification. Can you help?"

BONUS FOR MEMBERS:
Silver Members and up get access to an Extended Cut of this video. I'll show you how to build the forms necessary to make working with these relationships easy. We'll go both ways: we'll build a customer form that will show all of their courses in a subform, and then a course subform that will show all of the enrolled customers in the subform. We'll make a double-click event to update the course date, and then an After Update event to set the expiration date equal to a year in the future. Then we'll make a query showing all of the customers who have certifications that are expired or are expiring in the next 30 days.

MEMBERS VIDEO:

BECOME A MEMBER:

LEARN MORE:

LINKS:

LEARN MORE:

FREE TEMPLATE DOWNLOADS:

ADDITIONAL RESOURCES:

KEYWORDS:
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, teacher, training, certifications, tracking, many-to-many, many to many, courses, junction table, class enrollments, books to authors, students to classes, employees to certifications, products to manufacturers, songs to artists, data modeling, employee to project, theory, many-to-many relationship access, query, diagram

QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[juststoppingby]

THANK YOU. This opens up so many doors for me.

[dianecoverdale]

Thank you. Now I was wondering if you clicked on your ContactT table would you see the list of your customers with the sub list of the certificates taken? I've been trying to set up something like this for
work. A product table, a raw material table and a raw material test table. But my problem is that often times the raw material can be used an another product, causing duplicates. Would the junction table remedy the duplicates?

[CGML]

Thanks bud! great video and instructions.

[zakiqdb]

excellent teacher, you cover the subject step by step
can we create budget business within organization to manage the budge
ex. we have annual budget expanse required to pay invoice when received

[bertholdtohoover]

Hello.
Thanks for the videos, I really appreciate it. I just have one question, how would I in this example add multiple courses to a single customer?

I'm still a beginner, so apologies if this has already been explained.

I'm actually trying to build a database as a practice for the local restaurants in my area, I was wondering if it's possible to say, search for all restaurants that serve Pizza.

[ishaqmuhammed]

Simple and excellent explanation, really appreciate. How can I create a report, let say to show the list of cars I owned on the report? Can you help?

[calebhansen]

Great Videos. This is really going to help me out with fixing our processes, or lack thereof, at my company. Question, I became a Gold member but I can't seem to find the Many to Many extended cuts video in the members page. Can you share a link for that?

[huwjlfy]

So at what point did you set up the relationships? I must have missed it.

[Hrubicundus]

Love it Richard. But i have a question and it is a little bit spicy. How do you search for multiple values? For example, show me all the customers who got the Star Trek 101 certificate AND the Access Beginners certificate? I can't figure out how to do this query. Any ideas Ricardo?

[lawrenceayanful]

Stellar work chief... want to to know how many levels do your Access course have {beginner to exper}. And how much per level.

[movingmarz]

Thank you, your videos are so helpful. Question: If the many-to-many list of cars was all types of vehicles (cars, motorcycles, scooters, bicycles, hoverboards, etc.) and you needed to include specific details about the different vehicles, what is the best way to do this, especially since details for cars (seating capacity, cargo room, # of doors, etc) will be different from details for bicycles (pedal type, frame type, etc.). Also, how do you include multiple colors for each vehicle? Based on your Too Many Tables and Multiple Phone Numbers videos, I think the solution is a separate table for multiple colors (or multiple anything) and an extended details table for each type of vehicle (car details, bicycle details, etc.) to avoid null values of details that aren't applicable?

[lpanades]

About nomenclature: you created a junction table in the example of the certifications. Should I use an "j" like first letter o table name to identify it like a junction table? But, you put dates of conclusion so this kind of table is "fact table" if I'm not wrong. Question: Should I use one "j" or one "f" before table's name?

[habibullahsamander]

If I buy membership in your website, will I have access to all the previous tech help videos ?

[saeedbaryal]

Respected Sir,
I want you to show me the way how we can make a database work only in a specific PC. I have seen many databases they work only one PC, but if you copy them to other PCs they don't work.

[jcwynn]

I'm not the expert, but I think for junction tables, an autonumber ID field as the primary key seems like bad practice. The primary key should be the combination of the two foreign keys (a conposite key). The way this is set up, you can have the same 2 IDs for multiple records and the database would allow it because it just uses another autoID, but in practice it makes no sense to do so.

[gayanthafernando]

Pls sir release the vedio how to create many to many relashionship form design

[mehrasayat]

سلام
آموزش های خوبی هستند ولی برای من که توی ایران هستم پرداخت هزینه امکان پذیر نیست. آیا راه دیگه ای برای دریافت آموزش های شما هست؟

[stanTrX]

You never use table relationship, do you?