Consolidating Multiple Small Tables in Microsoft Access into One Table using a Helper Data Table

[@mitchellfolbe8729]

Watched once to understand. Watched second time to practice. Great fun.

[@599CD]

That's how you learn. :)

[@scottaxton2513]

Lots of useful little tidbits in the comments of the videos. The little nuances that you provide are worth their weight in gold. Thanks!

[@599CD]

Glad you approve. It's the little things that I don't think of adding to my "normal" courses that I enjoy putting in these. :)

[@ZahidKhan-mb6oc]

Very very useful tips, you are making life easy for many Access users around the globe. Thanks for your efforts.

[@599CD]

Happy to help

[@gregoryjeffries8475]

Saw this concept in business world several years ago. Look to incorporate into my db builds going forward. Great idea.

[@599CD]

Thanks

[@garethwoodall577]

Fantastic video Richard! Many thanks for sharing.

[@599CD]

Very welcome

[@farinatty]

Most useful additonal naming convention I have come across is to add FK (foreign key) to every ID where it is a foreign key. HelperID in the helper table, HelperID_FK in each other table it is used.

[@599CD]

That's a good idea. Personally I try to avoid - and _ in my VB names, which is funny because I used to be a C programmer and first_name was the preferred naming convention. But yes, if adding FK helps you keep everything straight, then by all means do it. I usually name the PK the same as the table so that helps me know what's what. CustomerT has CustomerID. OrderT has OrderID and so on. Every other ID would be a FK.

[@shadowsychrealm8311]

Thanks Richard! I really appreciate all the work you do. If it's not too much to ask, could you explain how you would get the real data back from the table? I have a db where i need about 9 conditions in one helper table, but I also need to be able to copy and paste an understandable log of the db's main table values. Thank you! I know it's a longshot but you'd be saving me a lot of agony.

[@shadowsychrealm8311]

NVM, I still think you should make a video on it but I figured out the solution by scrolling through the comments & downloading the "Help Desk" Techhelp Template. I was able to find an applicable example with how the "StatusID" & "PriorityID" worked. Thanks Rhichard! Doing the world a service!

[@599CD]

You're welcome. This isn't an easy concept to grasp at first. That's why in my beginner lessons, I just teach people to make all of the separate individual tables. Only after you've mastered relationships should you attempt this.

[@artistryartistry7239]

What if for "Web" as the lead source you need to get more granular and know which search engine(s) they used. Would you be forced into breaking that out into its own column?

[@599CD]

You could either add it to the same list like "Web - FB", "Web - Twitter", etc. or make a secondary list and use cascading combo boxes: 599cd.com/Cascading

[@NimishP]

In addition to access forms, can you make videos that employ C# forms.

[@599CD]

Nope. Not my jam.

[@philpatterson1366]

Can anyone explain the relationships of the helper tables to the tables using the data? If the helper table relates to multiple fields on the main table, how do you choose which field to link the helper ID to?

[@599CD]

That's what the HelperTypeID is for.

[@abdullahfelemban953]

Very helpful videos

[@MrSilentProtagonist]

I thought doing something like this before due to the simple fact that it takes less space than a text file. However, I used a lot of VBA coding and I was reworking an existing code. Using numbers to represent the state of the check started to make the code harder to read. Obviously, I could have just made a module with a function that accepts a string returns the corresponding number but 1) I haven't thought of that earlier 2) how do I update the module?

[@599CD]

I don't understand what you're asking me.

[@MrSilentProtagonist]

@@599CD I was saying that problem of using a module to keep track of helper data was the code must modified everytime that a new entry is added but it helps with the readability of the code. For example, if I want to filter checks to be printed, I could make a module called Helper with a function getchkstat("ToPrint") to get the HelperID in the Helper table.

[@thereviewers117]

Hi, I have a form on my database that displays different projects and each project has contract manager, site foreman, and surveyor. However, I'm trying to include this as a sub-form on the contact list for all the staff of the business. The issue is it only filters via contact manager whereas some people on that list might be a qs or site foreman. Is there a way to make the subform filter the records so if the name appears in cm, sf, or qs the record appears for that named person?

[@599CD]

Yes, I'm sure it can be done, but I'd need to know a lot more detail about how you have everything set up. I can't tell from just your question here. Feel free to submit your question at 599cd.com/TH

[@BanditFX7]

I using this technic on my old quickbasic pds 7.1 and its great for create compacte data base

[@599CD]

Thanks

[@josephmessina5689]

Veey cool excellent video. I love it Very helpful!

[@599CD]

Glad it was helpful!

[@pusolution4445]

If I use "Helper Data Table" my actual table gets numbers instead of actual data. My Qusetion is how can I search a record as indicated in " kzbin.info/www/bejne/n56ogHqrnpqYn8U " as there is no actual data but numbers.

[@599CD]

Join it in a query with the table that has the names.

[@pusolution4445]

@@599CD can you please suggest your video tutorial for this

[@remcop1989]

How come I didn’t see you add the relationships between the tables? I thought I knew about relationships between tables…😅