Consolidating Multiple Small Tables in Microsoft Access into One Table using a Helper Data Table
ฝัง
- เผยแพร่เมื่อ 2 ส.ค. 2024
- Do you have a million tiny tables in your database? Name prefix, suffix, title, lead source, marital status, gender, the list goes on. You want to store this information in a table (properly so) because you want to be able to easily edit the list and use that same list anywhere in your database. However, all those tons of little tables in your Navigation Pane are annoying and clutter your database. In this video, I'll show you how to consolidate all of those tiny tables into one Helper Data table to keep things nice and tidy.
Sara from Osaka, Japan (a Gold Member) asks: Help! I've got way too many tables and linked tables in my database. Most of them are small with only a few records in them, but I need them. I've got tables for: name prefix, suffix, contact type, lead source type, employment status, gender, marital status, the list goes on. Is there any way to consolidate this into ONE table?
BONUS FOR CHANNEL MEMBERS:
Silver Members and up get access to an EXTENDED CUT of this video which covers additional examples. That video will show you how to create a popup form where you can quickly edit the items in the combo box that you're working with, and also how to include the supporting helper data in queries and reports.
MEMBERS ONLY VIDEO:
• Helper Data - EXTENDED...
BECOME A MEMBER:
/ @599cd
UPDATE:
I cover the feature where the HelperF passes the new value back to the combo box that called it in my ABCD Part 2 database lessons: 599cd.com/1387
LINKS:
Get on my Mailing List: 599cd.com/YTML
Relationships: 599cd.com/relationships
Combo Boxes: 599cd.com/combo
ABCD Database: 599cd.com/ABCD
SQL: 599cd.com/sql
ADDITIONAL RESOURCES:
FREE Access Level 1: 599cd.com/Free1
FREE Customer Template Database: 599cd.com/XCDT
$1 Access Level 2: 599cd.com/1Dollar
Your Questions Answered: 599cd.com/TechHelp
Learning Connection 50% OFF: 599cd.com/Connection
Survey: 599cd.com/survey
Richard's Blog: 599cd.com/blog
Access Forum: 599cd.com/AccessForum
Twitter: / learningaccess
en.wikipedia.org/wiki/Microso...
products.office.com/en-us/access
microsoft.com/en-us/microsoft...
Email Me: amicron@gmail.com
KEYWORDS:
microsoft access, ms access, #msaccess, consolidate multiple tables, merge many tables, supporting data tables
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.
Lots of useful little tidbits in the comments of the videos. The little nuances that you provide are worth their weight in gold. Thanks!
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. :)
Watched once to understand. Watched second time to practice. Great fun.
That's how you learn. :)
Very very useful tips, you are making life easy for many Access users around the globe. Thanks for your efforts.
Happy to help
Fantastic video Richard! Many thanks for sharing.
Very welcome
Saw this concept in business world several years ago. Look to incorporate into my db builds going forward. Great idea.
Thanks
I using this technic on my old quickbasic pds 7.1 and its great for create compacte data base
Thanks
Very helpful videos
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.
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.
Veey cool excellent video. I love it
Very helpful!
Glad it was helpful!
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.
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!
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.
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?
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
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?
That's what the HelperTypeID is for.
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?
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
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?
I don't understand what you're asking me.
@@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.
In addition to access forms, can you make videos that employ C# forms.
Nope. Not my jam.
How come I didn’t see you add the relationships between the tables? I thought I knew about relationships between tables…😅
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 " th-cam.com/video/imEOGui5eic/w-d-xo.html " as there is no actual data but numbers.
Join it in a query with the table that has the names.
@@599CD can you please suggest your video tutorial for this