An Access Subform Based on a Many-to-many Relationship

[@LarisaHere1]

Last week I started design on my first Access DB in several years. As I came across each roadblock I searched and found various videos … but this is the one I kept coming back to. It contained so much needed information in a brief video and with very clear instructions. Very pleasant listening, too!

[@barelylucid]

This has been incredibly useful ! Spent all day trying to solve this exact problem until I found this.

[@Sings1961]

Best tutorial ever. I learned what I needed to learn, plus got a view into how the overall properties box works. Fantastic.

[@RodolpheArthaud]

Great video. I learned not only what I was looking for, but also a number of tricks, understood concepts that were unclear to me. Very well realized too. Congratulations, & thanks.

[@jaylake143]

Thank you for creating and posting this, Dr. Verschuuren. It has been very helpful to me to solve this problem.

[@charlenelena]

Exactly what I needed! I was missing the query part, and your explanation was perfect! Thank you!

[@DolittleZoo]

I have been doing Access for many years and the way you have adapted a few tables to build the app and demo it above is really instructive and I did suspect you were an experienced teacher/academic to pull it off. That was not a trivial concept you covered and your coverage of the principles were an excellent credit to your knowledge and your nation. Thank you!

[@coolhacker011]

Thanks for sharing your knowledge!! I just bought Office pro 2016 in the past year. It has been 10 years since I touched access/sql server and your videos are really bringing back some serious memories and shortcuts to help me with a little app I wanted to make. So far, I like your series the most.

This is the best explanation about MANY-TO-MANY relationship...

[@boblongoria9653]

Dr. Verschuuren, Thanks so much for your great tutorial. The example that you worked was exactly the problem I was trying to solve. Not only did you provide the solution, but I learned a lot about Access that I previously did not know. I can't thank you enough!

[@fazerville]

Nicely explained. This is now starting to make sense to me. The learning curve with Access is much steeper than Excel but certainly worth the effort.

[@NJHeart2Heart]

Thank you so much sir for this insightful video. I've been struggling with this topic for over a week, but your video covers ALL the specific details involved in accomplishing this particular goal. It's very difficult to find good tutorials on intermediate subjects in Access.

[@ScotTakesOver]

Dr. Vershuuren, thank you, God bless you, I heart your video

[@iCortez87]

Thank you, dr. Verschuuren! This is just what I needed!

[@asaadfayyaz]

Thanks. It helped a lot. I am designing a database for my accounting firm with Clients and Contacts tables and Contact Details as Junction table (Many Clients to Many Contacts). I had no idea how it will work out. You made it so smooth and straightforward.

[@princess-celestia]

I cannot thank you enough for this. You are a miracle worker, and your accent is adorable.

[@projectsupportsolutions2698]

I had a slightly different scenario but the video was still greatly sufficient in helping me create a form that works. Thanks so much.

[@bmack94536]

This video saved my bacon! Thank you! I was struggling to create forms similar to this one and you explained it perfectly.

[@steinhajek3762]

Dr Verschuuren, this was a perfect video for where I am in my development in terms of attempting to figure out Access. A few comments in regards to the video which I watched about 10 times to follow closely what you are doing. One is the video is absolutely perfect and covers a lot of detail that is outstanding and really helped me accelerate my learning. Two is in regards to possible differences that may arise from your development environment to mine. I am using Windows 10 with Access for Office 2013 and at the very end I ran into a few small issues that may be explained by differences, not sure really. In the setting today's date as the default value, I had to add as [date].[now] rather than =date() due to the fact that =date() would not compile for some reason but [date].[now] did. Other item is the linking of the total fields back to the sub-form calculated total fields. I could never get them to work for some reason. I will download your sample and see what I missed. Last item is the Northwind sample database was driving me crazy for several hours with sub-datasheets that were in the Orders, Customers, and Order_Details tables. I did not realize that there was a property field that sets sub-datasheets to [Auto] - (this is for sample Northwind database I downloaded from Microsoft). Again, Dr. Veerschuuren great video! I cannot thank you enough for sharing your knowledge sir!

[@steinhajek3762]

+Stein Hajek Very strange behavior on the last bit. I downloaded your code and loaded into Windows 10 64 bit Microsoft Access 2013 and your code for default date for the Order Date on the subForm works like magic using =Date() but refuses to work in my exact same database. I opened both and the only item I could see different was "Medium Date" for format in properties for the field, but changing that there and in the underlying Order table did not correct the problem. This also causes a problem with the field for totaling Order Values for today's date which is based on the same issue where for some reason my code refuses to compile =Date(). I attempted to uncheck references and then re-check them and open again but to no avail. Complete mystery to me why two databases that appear to be the same on the same platform using the same software appear to have different results. I can use [Date].[Now] but not =Date(). If I use [Date].[Now] on the TodayTotal it breaks both the Total as well as the TodayTotal field values on the main form.

[@dr.gerardverschuuren9261]

+Stein Hajek The function DATE in Microsoft returns the current date as a whole number (e.f. 5/9/16 is 42499), whereas the function NOW returns also the current time, so it adds decimals (e.g. 5/9/16 at 12 PM would be 42499.5).

[@RichardWheeler-SE-BA-PM]

I've watched several videos on working with many-to-many relationships, and Dr. V's are by far the best. Advice to others: Watch all three of "Subform in a Form," "Combo Boxes Streamline Forms," and "Subform Based on Many-to-Many Relationship" several times. Dr. V explains a lot more than the others do, and in a wonderfully organized way. I have two suggestions for Dr. V. This is GREAT content! Unfortunately, the videos aren't linked together as a playlist. I had to go to Dr. V's profile page and search for them. Dr. V should organized his content better so it's easier to find and watch. Normally, his pace is easy to follow, but a few times, he clicks away faster than the recording can follow. The cursor doesn't appear to click on the spot that causes an action, or the click occurs off-screen. So, try to keep the pace consistent. And a request: Sometimes one uses a small hidden query, and sometimes a query object. I'd like to see a summary of when to use each. Likewise, sometimes a formula is typed in Control Source; sometimes it's in a query, and sometimes Zoom is used. I'd like to see a summary of when to use each. The patterns and differences could be explained using snippets from the existing videos.

[@nand3kudasai]

it took me a while to get accustomed to the accent, but great tutorial! clear, concise and complete. and useful! because this is one of the least documented (and most useful) features of access.

[@evewyatt3260]

Thanks for this detailed video! It was just what I needed.

[@jamesedricshaw]

Thanks so much for this clear explanation! Brilliant video

[@irenesmarty7366]

Hi thanks for the video...like the way you explain each step. How do you create a subform with a main form that has many to many relationship? For example: Main Form (Exam Plan - Table 1) ExamPlanID - Text (Primary Key) Posting start date Posting end date Subform (Requisitions - Table 2) RequisitionNum - Number (Primary Key) Department Position Name HR Analyst HR Assistant Subform (Positions - Table 3) PositionNum - Number (Primary Key) Vacancy Date An Exam Plan can have many Requisitions (request to fill a vacancy) (from many departments) A Requisition can be on many Exam Plans (multiple recruitments if the vacancy was not filled) A Position # (each position # represents a vacancy) can be listed on many Requisitions (past and future recruitments) and a Requisition can have many Position #'s I did a join table for: - ExamPlanID / RequisitionNum - RequisitionNum / PositionNum I'm at the point of establishing the relationships but stuck here do not know how to create a form that contains all three tables on one form. To open a exam plan form where you can see all requisitions associated with the exam plan and the positions associated with the requisition attached to the exam plan...do you have a video that would help with this or advice on how to do it? Any help much appreciated.

[@jmchesh1]

Excellent tutorial. Thank you so much

[@bestofroytate2104]

Great video. One of the best I've seen. I also learned some new tricks.

[@darrengodkin]

Great explanation of a difficult subject to understand, thanks!

[@MariaSilva-xz6yg]

That's what I was looking for. Thank you! And besides, wonderful voice, wonderful explanation, wonderful views, my vote: 101%.

[@GamerDave2008]

Curious how you got two tables to join with a 1-to-many with same primary key names of OrderID for Orders Table and Order Details Table. I am learning this by reconstructing your database as shared in relationship view so that everything you do I will do, and Access 2016 will not allow me to make a 1-to-many join between 2 tables with same primary key names. It only allows a 1-to-1 join no matter what I try. So I renamed the primary key in Order Details Table to OrderDetailID and added OrderID after that as a Foreign Key, then I was able to get the junction to work as a 1-to-many. But really puzzled how you got your 1-to-many join between those 2 tables with same primary key name when I cant seem to find a way for Access 2016 to allow for it?

[@rickschellenberg9074]

This was a really helpful video that helped this all very understandable.

[@stuartb3502]

Thanks - this was hurting my head but you provided an excellent clear explanation.

[@Noufchou]

You explanation is perfect, I followed everything step by step. I just could not add new records in the subform, if anyone knows why, please help!

[@michellearnon3931]

How do I add record in the subform with another data?

[@rockyrocks2049]

Hi Gerard, Suppose we have one employee and store table additionally. Store to employee is 1:N. Just fit this relationship into your table relationship and please explain if we want to add a new employee record in the Employee table then how we do that from FORMS??? In other words if we have a employee form then can we add a new employee record with the one to many relationship built.

[@mikestubestation]

This is very close to what I've been stumped on for weeks. My design calls for a Maximum allowable discount to be associated with each customer. The problem is making the customer record max discount available to the subform as a for use as an edit against the order discount.

[@davidglotfeltyart]

How would you use a combobox to bring up the Customer you're searching for in the main form?

[@ShamsQaend]

Thank you so so so much! Very very good tutorial

[@benjaminguiracochabloomer5172]

Wonderful tutorial--thank you! :)

[@surendra19801980]

Great video. Remove my doubts

[@HeuristicsEnjoyer]

How would I go about allowing the user to create a new option in the combo box? In your example that would be a new product name, productID, and unit price. (My application is address1, address2, zip, etc)

[@eric78730]

Great tutorial! Thank you

[@DolittleZoo]

Really great video but if you look closely at the video instructions at 4:59 iwhen you move CustomerID out of the subform that you were creating you won't get a link to the main form after dropping it into the main form afterwards.

[@brianrisselada5430]

Yes, I'm confused by this. How did he do this?

[@davidthomasxyz]

This is a fantastic video! Implementing a many to many without something like this is very difficult for the novice (myself). It did get me thinking though; why does one even need to do the query? I tried just creating a form with all of the required fields in it and it looked great but it didn't work! I was not able to enter certain values and my data did not appear. For myself, I think that this is the difficult thing about Access; trying to understand why some things work and others don't. There should be some overall rules about what to do in certain situations.

[@jacoblebeau8317]

I get the error message "the changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship" etc. any time I try to add a new record from my drop down in the subform...I am a beginner and I tried to follow this step by step, the only difference is that in my database I'm not using any foreign keys like he has in the Orders table. I just have three tables (the two tables, and the linking table). Any suggestions would be greatly appreciated...

[@laithhameedalazawy]

you are great man

[@kalpeshsangita]

Hello Sir I created a junction table in my access file and created many to many relationships. When I create a query I can not see data from my other two forms which are liked through the junction table! Please help.

[@craigpoconnor]

Dr. Verschuuren, Many thanks for the tutorial - it helped me understand much more about how to implement this type of form within Access. When I downloaded the example database you linked below, I noticed that each time you add a new order line using the form, it creates a new "Order". So now each order has only one item associated with it. Is there a way to implement essentially the same thing, except with the ability to have multiple items per order? Or would that require significantly more complications? Thanks!

[@dr.gerardverschuuren9261]

Certainly more complicated: a subform within a subform. I let you figure that out on your own.

[@craigpoconnor]

Ah right - utilizing much the same method that you used to implement this one, perhaps with a combo or list box to control it? Thanks for the tip!

[@vbaaccess1365]

Hello Dr.Verschuuren, Very helpful subject, but what do I have to do if I want use CompanyName field as ComboBox instead of navigation buttons and make to change other fields simultaneously? Thank you in advance.

[@dr.gerardverschuuren9261]

That's what my CDROM is for: www.amazon.com/dp/1932802037

[@nightstar1946]

Love your accent 😁😁

[@falahjomor]

Thank you so much Dr. How about one order have more than one product !! i.e. can we create (Main-sub-sub ) forms, but the forms can shown separately not combined in a query ??

[@dr.gerardverschuuren9261]

Will do that in one of my upcoming videos, combined with a tabbed form.

[@falahjomor]

Dr. Gerard Verschuuren I appreciate it Dr. . Hope you show us how to print it out with report. I'm having big problem in how to print the Main- sub -sub records for each customer. Thank you

[@jadadocs8844]

Hello, really enjoy watching your video and find then extremely handy. I a looking to create a customer database which will hold many customers and their onging product purchases and 6 monthly product services. I want ot be able to open a single customer to see their purchase history and what products are due for service. Also looking to be able to put a report for all overdue services from the entire database. I have tried to create a subform but would like to use an excel sheet to show this information. Please send me a link if you have any videos that may cover all of this. Many thanks,

[@sahajisam964]

Thank you sir, great video. but I still have problems making these tables. for example how could you make two primary keys in the Order Detail table? did you make a video about how to make these specific tables in the video?

[@TheRapidhome]

I have curated a small data base, I would like to select forms using a combobox along with a command button to filter. Can you show me how to accomplish this? Just started using access 1 years ago.

[@dr.gerardverschuuren9261]

See www.mrexcel.com/accesstraining.shtml and www.mrexcel.com/access2007vba.html

[@Leorisar]

Great tutorial! I thought inserting records into query isn`t possible. Will this example work in Access 2010?

[@dr.gerardverschuuren9261]

It certainly will, as long as you use the foreign keys (not the primary keys) for the connections in the query.

[@stanTrX]

Very good!

[@deepzajac2217]

It is not working properly for me. The problem is in the form wizard once I am creating the form based on the query. After pressing next in the form wizard the layout radio button does not follow as in the video but the question "How do you want to view your data" since data come from several tables. Why in the presentation the question does not pop ups? The issue is that in the video the foreign keys do not have to be selected in the form wizard but exist in the sub-form as are used in the connection with the main form. In my case once the subform consists from several tables the foreign keys do not exist there automatically and I am not able to make relation with the main form.

[@stiquin]

for all those having the same problem, i found a solution for it (by try and error): when creating the form using the form wizard that will end up being the subform, do not deselect the ID you want to link it with in the main form. Then you can link it as shown in the video. After adding it as a subform to your mainform the ID will be visible in the subform. if that bothers you than simply close the main form, open the subform, delete the ID field in the design view and save. no worries, it will still be linked to the ID but the ID will no longer be visible in the subform.

[@ChrisSimmsJr]

Maybe you can help me with an issue that I've having. For my scenario I want to assign events for volunteers to work. I followed the video but used the tables for my scenario. I was able to add multiple events for a contact but what I'm seeing is that my many to many join table isn't being updated but instead my main events table is starting to add records based on the events that I assign to a contact. Do you know if this is how it's supposed to behave? Hopefully I'm clear on what's happening.

[@malsaso]

Excellent

[@pablomarcelmx]

Exelent video

[@ehabhamdy2240]

Hello Mr. , I am very thankful for Your great Video But I made the same steps You mentioned in Your Video and this message appeared to me "the microsoft access database engine cannot find a record in the table with key matching" actually I do not know what does this message actually means , this message appeared to me once I tried to enter a new record in the subform via combo box.

[@dr.gerardverschuuren9261]

Here is the file: www.genesispc.com/download/manytomany.zip So you can find out yourself what you did wrong.

[@ehabhamdy2240]

Dr. Gerard Verschuuren Thanks Dr. , I will see what I can find

[@ehabhamdy2240]

Ehab Hamdy Actually I opened Your file , I could not find the mistake I made, the situation is like the following: can add records to my junction table once I do this directly from inside the table and the records appear in the data entry form, but once I try to enter data via the data entry form the message I mentioned in my first comment appears , I wonder what does this message means and what the mistake I did Kindly check the file in the link (dropbox) , if you have enough time to find the problem I would appreciate a lot Here is the link: www.dropbox.com/s/fgomk0wo202cg0p/O%26G-HR-Database.accdb?dl=0 Thanks in Advance

[@ehabhamdy2240]

Ehab Hamdy Ok it's alright Dr I found the Mistake !!!!!!!!! , Thank You very much , the mistake was in creating the subform it self in defining the relationship between the form and the subform :))

[@arfatschoolsystemjhang9255]

Nice Video

[@bondniko]

Amazing

[@Tina-lu5mc]

This did not work for me

[@prabu67]

I have many material request (MRNo, ItemNo, MRqty), One order (Main form: order no, MRNo (from many MR), SupplierNo. Subform: Itemno, POQty (addition of two MRQty from many MR , Price, amount.) Prabu

[@romeowhiskey1146]

Sorry, this item is temporarily out of stock Can't SELL if you don't HAVE

[@saronasgedom8291]

NeXt nExT :'D

[@bradmonk69]

Sir, you are a legend.