Microsoft Access Relationships: Relating Multiple Tables, Relational Data, One-to-Many, Many-to-Many

[@ButterflyEars60]

Thank you so much for these videos! I was an advanced user some years ago. I created databases for tons of employee data, applicants....everything related to employment in a school district of 6,000. It was a life saver! I am crazy about Access. I'm trying to create a few databases and realize I have forgotten some key things about all of this. It's great to watch your videos and get refreshed on some things. I intend to take at least one of your advanced classes to refresh myself on the really good stuff!

[@599CD]

Sounds great. Welcome back. :)

[@ancestrycounts4759]

Thank you, I've been watching, by different people, various beginner access videos. One person would do a table to table link and another person do a table to junction to table link. Since each person was only showing the one example, I couldn't understand the underlying difference between the two methods. Then I found this video! Got it, light bulb went on! Starting your beginner class now.

[@xlschool7334]

Thank you for amazing and clean explanation about table relationships. I have 2 tables a student table and a teachers table, every student has to have a teacher as an advisor and a supervisor, now what type of relationship these two table can have. I mean every student simultaneously has relationship with two different record with teachers table. Highly appreciated for response and instractions

[@599CD]

You need a 599cd.com/ManyToMany relationship

[@Addicted2Learning]

Clear explanation, well-paced!

[@599CD]

thanks

[@xm_tech]

Thanks for sharing this video. It’s very helpful

[@599CD]

Welcome

[@ishmaillibbie8238]

Thanks a lots for such a wonderful tutorial. I have never open access before but I just started watching your four (4) hours plus video and from what I'm seeing, you are really good in it. God bless you for such help and besides I really want to watch from start to finish. Please help me to access the videos from lesson 1 to the last lesson please.

[@599CD]

All of my lessons are here: 599cd.com/Access

[@DoWithAbdou]

BEST TUTOR EVER 👌💯

[@599CD]

Thanks

[@kurosakibankai19]

Very helpful and informative videos. P.S. love the Star Trek references and not just any Original XD

[@599CD]

LLAP. :)

[@Yami524]

Hi Rick. I work for a restoration company we have several job types which are emergency service, repairs, owners request and mold remediation. I want to be able to enter for each customer if they have either 1 job types or they can also have many as a subform. Also I like that you have the contact subform on the customer form as well. Can I have those two as buttons on my customer form/

[@599CD]

599cd.com/QQ

[@darkshadowwolf3475]

Is their any way to have more than one foreign keys from another table. So basically, i am working on a rate sheet that would take two locations from the location table and it will have a specific rate for movements between these 2 location. The Goods delivery table would also have to have 2 foreign key to the Location table for Pick up location and drop off location. I am hoping to not have to create 2 tables for locations as the same locations would be in both tables since a job would start with the delivey of equipment and ends with the collection of the equipment.

[@hubertz.6560]

great job

[@dniezby]

Deana Troi? LOL. Love it. As you were number one.

[@599CD]

Make it so

[@philipbbrima8594]

Thanks a lot for the tutorial I am a beginner I need to learn more about Access

[@80Ichinose]

Your videos are great!

[@599CD]

Glad you like them!

[@80Ichinose]

Oh absolutely. I’m a pharmacist and I work on drug database information. Most I work with use excel but I decided to try to use access. So I’ve been watching your videos on how to do certain things! Very impressed with your delivery and methods.

[@PJones43213]

Hi Richard, I have a table that contains all of my organizations employees and details. I then have a table that contains open positions and details. Additionally, on the employee table, where there are open positions, the employee name field just lists vacant or the job req. number if one has been assigned. When someone is hired and I fill their name into the open positions table, I would like the name of the hired person to be automatically filled into the employee table in the correct vacant position row. There are other details kept in the open positions table that do not need to be pulled into the employee table so I am just looking to pull the value from a specific row and cell. Is there a way for me to do this? Any guidance would be very much appreciated. -Patrice James from Columbus, Ohio

[@599CD]

This is just a matter of storing the EmployeeID in the position table. You can then refer to all of his other information via the relationship.

[@mechtechpotato4249]

Hello Richard, I am wondering if junction tables with more than 2 tables being joined supported on a SQL server. Thanks, Carson R.

[@599CD]

Yep, they should. It's just basic SQL joins.

[@mechtechpotato4249]

@@599CD Thanks Richard.

[@alializadeh8195]

Thanks

[@joshuacarpenter5358]

Not me watching this video looking out my window at my 1994 Ford Taurus lol

[@599CD]

Haha. Funny thing was I recorded my very first Microsoft Access 101 video back in 2002 I believe and I re-recorded it four or five times since then and every time I just keep the old graphics and just put them in new slides. Sure I can redo the graphics but for me it's just a nostalgia thing. And hey they get the job done.

[@usertgvh]

My question here is about relationships and setting up tables in a given situation. I want to build a database that has a table for people who work as artists as well as designers. They sing as well as design either alone or with others. Simple many-to-many table would have created an Artist table with ArtistID as PK and a joining table llike artist_Album with artistID and albumID as PKs (composite PKs) and an Album table with AlbumID as PK but since here we have another table designer with designerID as PK as well. How can we design in this situation? Please reflect onto this situation whence one has more than 2 tables to combine in a many-to-many relationships?

[@599CD]

Feel free to submit your question at 599cd.com/TH

[@garycurtis]

Excellent video, I have a question. For the many to many relationships, 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?

[@599CD]

No need for any VBA. Access maintains the relationships using the Link properties in the subform object. Watch 599cd.com/ManyToMany and 599cd.com/EventEnrollment

[@chadthomson2169]

I am trying to create a query where i can select a work shift using a combo box (shifts are in a table), and then select the date range. It should then pull the number of items i have summed in 4 different tables. This has been killing me. can you help?

[@599CD]

599cd.com/Ask

[@kennisrogers2585]

Is access pretty much an easier way to perform xlookups?

[@599CD]

Mmm... I wouldn't necessarily put it that way.

[@theecobazar6359]

Nice Video Sir...Thanks sir.............& Eid Mubarak ..............From Bangladesh - Dhaka....... Mirpur 13

[@599CD]

You're welcome.

[@btechnical3252]

Where to Create Relationships? In the Front-End or Back ... Why we create relationship in front end... ( infact we can create these relations in back end very efficiently) If we must use front end for relationships... So how to create a validation relation in frond. To a list or to a table.

[@599CD]

I don't usually bother with global relationships unless I need referential integrity, which I usually don't bother with (I handle it in code).

[@revision_classesTM]

👍👍👍👍👍

[@599CD]

:/

[@d.thomas5357]

Is there a way to add a parameter field to a report header. For example, I want to add phrase Effective from June 2, 2021 t Thru [parameter field] where I type in a date.

[@599CD]

I would recommend using a form field for the parameter. Then you can reference it from anywhere in your report. 599cd.com/FormName

[@northmaineguy5896]

Ah yes, my father was born in Bridgeport, CT in 1925!

[@599CD]

Cool beans!

[@TomLewis-m4g]

You spent almost ten minutes explaining things with graphics instead of using the software you're teaching people to use. I watched the whole thing and you don't even teach how to create/manage relationships. Teaching has come a long way since the 90's.

[@599CD]

You need to learn the concepts and the WHY first before you get your hands dirty with the HOW. This is the best way to learn most things. Don't like my style? Plenty of other tutorials out there to choose from...

[@Sulz]

This is an introduction video to show why Access is used and what it means to be a "Relational Database". It is a great video.

[@599CD]

Thank you, @Sulz