|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Helper Data By Richard Rost 
6 years ago
Consolidating Multiple Small Tables into One Table using Helper Data 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? MembersI'll 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.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! Update
Links
IntroIn this video, I will show you how to consolidate multiple small lookup tables, such as name prefix, suffix, contact type, lead source, and other helper lists, into a single set of helper tables in Microsoft Access. You will learn how to create two related tables to organize and store various types of helper data and how to use these tables efficiently in your forms using combo boxes. I will demonstrate step-by-step how to set up the tables, link them to your main data, and configure combo boxes to display the right list for each field.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I am going to show you how to set up a helper data table that will allow you to consolidate tons of multiple small tables into one big one. Today's question comes from Sarah in Osaka, Japan. Sarah asks for help. She has too many tables and linked tables in her database. Most of them are small, with only a few records in them, but she needs them. She has tables for name prefix, suffix, contact type, lead source type, employment status, gender, marital status, and more. Sarah asks if there is any way to consolidate this into one table. Yes, of course, Sarah. You can put all that data into one table. Actually, I am going to suggest using two tables because I like a second table to describe what kind of data each set is. For example, your contact type or your prefix or suffix. So we will have two tables. We will take all these dozens of different tables and consolidate them down into two. We just have to know how to use them in our database, like in our queries or in our combo boxes. Let me first explain for everybody exactly what we are talking about here before I show you how to do it. If you want to be able to select an item from a list, for example with a combo box, then you have to have that data either stored in the combo box itself or in a table. Now I do not like storing this kind of data in a combo box because if you use this combo box on different forms, you have to update it every time you want to make a change. For example, here are prefix and suffix. This is my ABCD, my Access Business Contact Database. Prefix: Mr., Mrs., Miss, etc. If I want to add more items to this, I do not want to have multiple copies of this combo box on different forms. Same thing with the suffix: Jr., III, IV, and so on. I have also got them for gender, because sometimes you have male, female, unknown, or anything else you want to put in there, or titles like president. I do not want separate tables in my database for all of these. It is nice to put them all into one table, which I call my helper T, my helper table. I have got a second table set up here that just stores what kind of information is stored. Let me show you how I built this. We do not want 10, 15, or 20 little tiny tables over here for prefix, suffix, organization, and so on. We want them to be in one big table. So let's open up this new database I have over here. This is my sample class database. Now, let's say I want to do the same thing - prefix and suffix. Instead of making a prefix table and a suffix table to load combo boxes over here, we are going to put them in a helper table. But first, let's create a helper data type table so that we know what is in each type of data set that we are going to be storing. So let's go to Create - Table Design. This is going to be HelperTypeID. That will be our autonumber, and then a description, and that will be text. Save this HelperType table and make it the primary key. This is going to simply be a list of the different kinds of data that we are storing in our helper table. What kinds of stuff do you want? Name prefix, name suffix. Suffix is things like Jr., Sr., and so on. Prefix is Mr., Mrs., Ms. You saw that in my other database. You can have in here marital status: married, divorced, single, and so on. You can have gender. You can have whatever else you want. You can have a lead source where you define them: TV, print, website. This is just a list of your different lists, so you know what each set of data is. This will make more sense once you see it in action. Now I am just going to move this over to the side so I can still see that list, so I know what is in here. Now, let's create another table to store the actual data. This will be my actual helper T. So right here, HelperID, that will be my autonumber for this table. HelperTypeID will be a number pointing to this other table: 1, 2, 3, 4, 5. Then, of course, the actual data - HelperData, we will call it. That can be text. Save this as my HelperT. Now let's throw some data in here. This is where you set up your lists. Let's do our name prefixes first. Name prefixes: we got Mr. 1, Mrs. 1, Miss 1, Dr. 1, and so on. What's next? Name suffixes, 2. Suffixes: we got Sr. 2, Jr. 2, III 2, IV 2, etc. You can add as many of these as you want. If you have someone who is the fifth, just add that. That should be a 2, my bad. Let's skip down to lead sources. You have TV, print, word of mouth, web, and so on. Now you see how we are eliminating the need for all these different small tables in your database. If you have any serious work to do with the table, like you are going to be doing reporting on all kinds of complex stuff, then yes, make it its own table. But for this little tiny stuff, that's all you need. Throw it in here, and you still have the flexibility of being able to add to it and change it. When it comes to storing this information in your other tables, this is what you are going to store - that HelperID - as whatever ID you want it to be. I am going to get rid of this guy here real quick, save changes here, and move this one over to the side now, because we are going to be working with this guy. If you are unfamiliar with what I just did here, this is the foreign key. I have got lots of different videos on relationships between tables, so you should understand what one-to-many relationships are. If not, I will put a link in the description below. Go watch my videos on relationships and then come back to this one. You have got to have a firm grasp of how relationships between two tables work in order to get this. Now, in my customer table, I want to store some of this information. Probably all this information eventually, but we will just do two fields today: prefix and suffix. Let me close my customer form. Let me close all of this down. Now, let's go into the customer table, design view, and we will come in here. Instead of putting prefix and suffix in here as text, because we do not want people just typing anything in here (they could type in Mr. as M-I-S-T-E-R), we want our data to be normalized. We want it to be so that they have to pick from a list. So I will put prefix. Now this is going to be a number now because the prefix is going to be one of these HelperIDs. I like to call it prefixID. That just tells me later on, hey, this is relational; it is pointing somewhere else. We will do suffixID. I will do one more, too: leadSourceID. Save that. What is going to be stored in the table is going to be ID numbers. If I just put some data in here so you see it, let me slide over here. Up top, I happen to be a junior. So over here, prefixID is going to be Mr. What is the prefixID for Mr? Oh, that is one. My suffixID is going to be a junior. So junior is six right there. That just identifies me as Mr. and as a junior. Where is my son? My son is in here too. He is down here. There is my son. My son will also be a one, but he is the third, so he will be seven. Now I want you to see how this works with the data in the tables. That is what we are actually storing as these IDs. It is not going to have to be where you have to do that when you are entering data in the forms. In the forms, we can still use a combo box. Now, how do we do that? Let's go over to the customer form. Now that data is in our table underneath the form, so we are good. Let's go to design view. Let's open this up. I am just going to drop these combo boxes down here for now. Go up top and select combo box. Drop it there. The wizard starts up. I want the combo box to get the values from a table or query. That is fine. Next, we are getting our data from the helper table. Next, what do we need? We need the HelperID and we need the HelperData. We do not need the HelperTypeID in the combo box. We are going to refer to it in just a minute, though. I am going to come back to that. Hit Next. How do you want to sort the data? We are going to sort it by HelperData. Next, this is what it is going to look like. Now we see all of it here. We are going to limit this list in just a minute. Next. Then we are going to store the value in whatever field we are picking. Let's do prefix first. Next. The label is prefix. Finish. If you do not know what I just did, you need to learn combo boxes. I have got lots of lessons on that. I will put a link in the description box below the video. Let's see what we have here. Let me save this, close it, and open it again. I open this up now, I see all of those items. What I want to do is limit this list of items to just the prefixes, not all the helper data. What is that? That is where the HelperTypeID is one. So I am going to edit this box and just show that stuff. If you go into the box, here is where we get a little more advanced. This is the property sheet for that combo box. The combo box is called Combo26. That is the first thing I am going to change. I am going to change this to prefixCombo, or whatever you want to call it. I usually call combo boxes prefixCombo. On the Data tab, this Row Source right here is where it gets its data from. If you have any interest in being an Access developer, I strongly recommend you learn SQL. Here is the SQL. Here is what it looks like. Basically, this says: select HelperT.HelperID, HelperData from HelperT, and order by HelperData. Let us say get these two fields from this table and sort by the HelperData field. Now, for those of you who know some SQL, we are going to add a WHERE condition on here. But I am going to show the beginners how to do it by just using this little builder button right here. If you are familiar with building queries, this is easier for you. Click on that. That opens up this query builder in the background. I am going to close the property sheet for now. This is what the query behind that combo box looks like. We want this to just be prefixes. So I am going to double-click on HelperTypeID and put a 1 down here under criteria. I am also going to turn that off, because we do not need to see it in the box. This is the Show row. This is basic query design. Again, I cover this in my lessons. Close the query builder. It is going to ask if you want to save the changes made to the SQL statement and update the property. Say yes. What just happened? If you look at that property sheet again, now you can see what it did. So select HelperT.HelperID, HelperData from HelperT where HelperT.HelperTypeID equals 1. That is what we just added. It is a WHERE condition. It says, "I only want to see the helper information for group 1, which is prefixes." That is what the query builder did for us. You can write this by hand if you know it. I strongly recommend you learn some SQL. I have lots of lessons on that too. I will put some links down below. One of the beautiful things about Microsoft Access is that you can build some really cool stuff. You can build some really powerful databases without having to learn SQL and without having to learn Visual Basic. But once you learn those things, Access goes to a whole other level. You can do all kinds of really cool stuff with minimal amounts of programming if you learn a little bit of SQL and a little bit of Visual Basic. Trust me, get the basics down first. Learn your tables, queries, forms, and all that. That is great. That is my beginner series. Learn relationships - that is next. That is the beginning of my expert series. But once you get into that, start learning a little bit of SQL. So this guy should now just show us all the records where HelperTypeID equals one. Save it, close it, and open it back up again. Customer form. Ready? There we go. There are only the prefixes. See how that works? You can change it right there. Now let me show you how easy it is to do the next one. That was a little bit of work setting the first one up. Want to do suffix? Copy and paste this guy. Change this label to suffix. Open up the property sheet. There are a couple things we have to change. Go to the Alt tab. First, change the Control Source. The Control Source is where this box stores its data. We are going to store this one in the suffixID in the table. That is what Control Source means. I am also going to change the name to suffixCombo. Now go to the Data tab. Here is that SQL statement. You do not have to run through all that setup again. Just change that one to a two. That is all you have to do. Hit OK. Close that. Save changes. Open it back up again. Look. There is my Junior, III, IV, and so on. The same thing one more time for LeadSource. Design view. See how easy it is now? Slide these up a little bit. Of course, you can make this pretty and put these up where they belong. I am just showing you for class. Copy and paste, and do the same thing one more time. This is the LeadSource. Open this guy up. Properties. Go to the Alt tab. We are saving the data; that is the Control Source. LeadSourceID. The name will be leadSourceCombo. You can call it leadSourceID if you want as well. Sometimes I have both the text box and a combo box for it on the same form, but you do not have to. You can call this whatever you want as long as it is unique. Go to the Data tab. Open this up. LeadSource is data type 5. You can store any kind of information this way that you want to. Simple stuff. I would not put whole customers and such in here. You can do simple things. Helper data is called helper data because it helps. It does not need its own separate big, giant table. There you go. If you want to learn more about using these helper data tables, I have got an extended cut edition available for members only, Silver members and up, where I show you how to make a form to edit and add items on the fly. If you want to quickly add a new prefix or suffix or whatever, we will pop up a little form here. I will show you how to combine all this data together in a query. If you want to put this stuff into reports or other queries, you have to know how to join this data together, using inner and outer joins. It is a little tricky, but not that hard. I will show you how to do it in the extended cut edition. Here is a database. Right now, if I want to add a prefix, and it is not in this list, I have to go back to the helper table, add it in there, close that, and close this form and reopen it so it adds to the list. It will not refresh by itself. In the extended cut, I teach you how to just double-click. Now I have the name suffixes up, or you can double-click and there are your lead sources. Put a new one in there, and when you close this, it will be right in that list. That is in the extended cut for members. How do you become a member? Click on the Join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you will see a list of all the different perks that are available: Silver, Gold, Platinum, and Diamond. But do not worry. These TechHelp videos will keep coming. As long as you keep watching them, I will keep making them, and they will always be free. If you enjoyed this video, please give me a like and share. Click on the Subscribe button to subscribe to my channel and be notified of any new releases. Check for additional resources down below the video. Click the Show More button and you will see a list of other links to other videos, downloads, resources, lessons, and more. If you have not yet tried my free Access Level 1 course, it is three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just $1 and that is free for my members. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can post your question there. Also, be sure to stop by my Access Forum on my website. Also, look for me on Facebook, Twitter, and of course, YouTube. Once again, my name is Richard Rost with AccessLearningZone.com. Thanks for learning with me, and I will see you next time. QuizQ1. What is the main reason for consolidating multiple small tables into a helper table in Access?A. To make the database look simpler B. To increase the number of tables C. To reduce maintenance and make updates easier D. To decrease database size Q2. Why is storing values directly in combo boxes not recommended for shared lists in Access forms? A. Because combo boxes cannot store text data B. You have to update every combo box individually whenever you want to make a change C. Combo boxes can only be used once per database D. It is forbidden in Access databases Q3. What is the main purpose of the HelperType table in the video? A. To store customer contact information B. To list the different types of helper data sets (like prefix, suffix, etc.) C. To store only prefixes D. To contain unrelated data Q4. In the helper table setup, what does the field HelperTypeID represent? A. The unique identifier for each data entry B. The description of the data C. A foreign key referencing the HelperType table D. The primary key of the customer table Q5. When entering data for the customer table, what is stored for fields like prefixID and suffixID? A. The text value such as "Mr." or "Jr." B. The ID number from the Helper table that corresponds to the desired value C. The full description from HelperType table D. A randomly generated number Q6. What is the advantage of using IDs instead of text for fields such as prefixes or suffixes in data tables? A. It makes the table easier to read B. It prevents users from entering incorrect or inconsistent values C. It allows for faster typing speed D. It reduces the number of queries Q7. What must be modified in the combo box Row Source properties to ensure only relevant list items (e.g., only prefixes) are displayed? A. No modification is needed; all helper data is shown B. Modify the WHERE clause in the SQL statement to filter by HelperTypeID C. Change the Name property of the combo box D. Set the combo box to Display All mode Q8. Which SQL keyword is used to limit the combo box to items where HelperTypeID is a specific value? A. GROUP BY B. ORDER BY C. WHERE D. HAVING Q9. According to the video, what is strongly recommended for Access users to learn besides tables, forms, and queries? A. Networking principles B. SQL and Visual Basic C. Python programming D. Database hardware maintenance Q10. What does copying and modifying a combo box make easier when setting up multiple similar fields like prefix, suffix, and lead source? A. It creates unrelated fields in the form B. It avoids duplication of records in the table C. It speeds up the process of creating similar combo boxes for other fields D. It removes all errors from the database Q11. What should you change when copying a combo box to make it serve a new field (e.g., suffix instead of prefix)? A. Its Control Source and the WHERE clause in the Row Source property B. The font size only C. The database name D. Delete and re-create the entire form Q12. What is the main benefit of consolidating helper data into a single (or two) tables instead of having dozens of tiny tables? A. It allows for larger tables B. It makes updates and maintenance much simpler C. It increases storage space needed D. It reduces the functionality of Access Answers: 1-C; 2-B; 3-B; 4-C; 5-B; 6-B; 7-B; 8-C; 9-B; 10-C; 11-A; 12-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone covers how to organize your Microsoft Access database by consolidating several small tables into a streamlined helper data table structure. Many students, like Sarah from Osaka, run into the problem of collecting dozens of small tables in their databases – things like prefixes, suffixes, contact types, lead sources, employment status, and more. While each table may only have a handful of records, managing so many separate tables quickly becomes cumbersome.There is a much cleaner way to handle this using what I call helper tables. Instead of creating a new table for every small list – such as prefixes like Mr. and Mrs. or suffixes like Jr. and III – you can keep these values in just one main table, or better yet, two related tables: one that defines the type of the data, and one that stores each item along with what kind of list it belongs to. Here's how it works. First, you build a data type table, which might be called HelperType. This table stores the different categories for your lists: name prefix, name suffix, marital status, gender, lead source, and so on. Each one gets a unique ID and a description. Next, make your main helper table – I usually call this HelperT. Each record in this table holds the item itself (like "Mr." or "Jr."), another field that links back to HelperTypeID (so you know which list it comes from), and a unique HelperID. This design keeps all your helper data in one place. It is easy to update and maintain, and you will never have to chase through 20 different tables again. Once your helper structure is set up, you start to use these tables everywhere you would have previously needed a dedicated one. For example, when dealing with customers, instead of storing the prefix as simple text, you use a number field called prefixID that points to the correct item in the helper table. This ensures data integrity in your database – users cannot free-type mistakes like "Mister" or mix up naming standards. To let users pick these values conveniently, use combo boxes on your forms. Rather than storing a static list in the combo box itself (which would need careful updating on every form), now you dynamically pull the right records from your helper table. For instance, to display just prefixes in a combo box, filter its data source to show only those helper records where the type ID matches your prefix group. This setup means that you can easily add new prefixes, suffixes, or any other type of small helper value just by adding a new record to the helper table under the correct type. You can reuse the same combo box setup on multiple forms, and the right list will always be available and up-to-date. When you design your forms, set up each relevant combo box (for example, one for prefix, one for suffix, and one for lead source). Adjust each combo box's underlying query so it only pulls items from the helper table matching the appropriate list type (for instance, using a WHERE clause to filter by HelperTypeID). Then, map each combo box to save its selection as the correct field (such as prefixID or suffixID) in your customer table. If you need to expand, just add more items to the helper table or more types in the HelperType table. For basic lookup lists and classification data, using this two-table helper structure is a huge improvement over keeping a mess of small, individual tables. For those interested in more advanced usage, in the Extended Cut for members I show how to make a maintenance form so you can add or edit these helper items on the fly. You can double-click from within the form to add a new prefix, suffix, or lead source, and the list updates automatically. I also demonstrate how to join this helper data with your main tables in queries, and how to use both inner and outer joins to produce reports or consolidated views of your data. Using helper tables streamlines your data management. It prevents redundant structures, makes forms easier to build, and keeps your Access database normalized and tidy. For complex lists or where you need heavy reporting, use separate tables, but whenever possible, let a helper table do the work for simple classifications. If you are new to combo boxes, SQL queries, or table relationships, be sure to check out my foundational lessons on those topics. They will help you get even more out of this technique. A complete video tutorial with step-by-step instructions on everything discussed here is available on my website at the link below. Live long and prosper, my friends. Topic ListConcept of consolidating lookup tables in AccessDesigning a Helper Types table for data categories Creating the Helper table to store various lookup items Populating the Helper table with example data Establishing relationships between Helper tables and main data Setting up foreign key fields in the Customer table Storing normalized lookup values by ID in tables Adding combo boxes to forms for lookup selection Configuring combo box row source using SQL Limiting combo box values with WHERE clause based on type Renaming controls and updating their control sources Duplicating and modifying combo boxes for different fields Using property sheets to manage combo box settings Ensuring data normalization with lookup tables Demonstrating the process for prefixes, suffixes, and lead sources |
||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access consolidate multiple tables, merge many tables, supporting data tables helper tables PermaLink Consolidate Tables with Helper Data in Microsoft Access |
