Building Too Many Tables to Store Similar Types of Data in Microsoft Access. Consolidate Your Data.
HTML-код
- Опубликовано: 27 мар 2022
- In this video, I'll show you how to consolidate your different types of data into few tables. Don't create tables for every different "type" of person or business you deal with when one would suffice.
Henry from Everett, Washington (a Platinum Member) asks: I've been building my database following your lessons. I run a travel agency. I've got tables set up for airlines, hotels, restaurants, and rental car companies. I also have a table for my customers who book trips through me. Sometimes I also have to invoice a hotel or a restaurant for package deals we set up. Would I also make a separate table for those invoices? I'd also like to track all of the contacts between any of my people and these companies or my customers. Do I need separate contact tables for each of these too? I'm really confused.
Silver Members and up get access to an Extended Cut of this video. Members will learn how to create a single button to open up whichever specific extended info form that customer type requires, and if there isn't one, the button disappears. Oooh... Ahhh... Magic!
MEMBERS VIDEO:
• Too Many Tables EXTEND...
BECOME A MEMBER:
RUclips: / @599cd
or My Site: 599cd.com/THMember
LEARN MORE:
599cd.com/TooManyTables
SUGGESTED COURSE:
Access Expert 4: 599cd.com/ACX4
LINKS:
Relationships: 599cd.com/Relationships
Relational Combo Boxes: 599cd.com/Relational
Value From a Form: 599cd.com/FormName
Compact & Repair: 599cd.com/Compact
ABCD: 599cd.com/ABCD
Union Queries: 599cd.com/Union
OTHER COURSES:
FREE Access Beginner Level 1: 599cd.com/Free1
$1 Access Level 2: 599cd.com/1Dollar
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: 599cd.com/THFree
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Contacts
Order Entry & Invoicing: 599cd.com/Invoicing
More Access Templates: 599cd.com/AccessTemplates
ADDITIONAL RESOURCES:
Donate to my Tip Jar: 599cd.com/TipJar
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/TechHelp
Consulting Help: 599cd.com/DevNet
Twitter: / learningaccess
en.wikipedia.org/wiki/Microso...
products.office.com/en-us/access
microsoft.com/en-us/microsoft...
KEYWORDS:
microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, one-to-one relationship, Table Relationships, what tables do i need, tables for multiple customer types, too many tables, max tables, max fields
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.
Rick, as a fellow Buffalonian, thank you for your classes and tech help videos. As you stated in one of your earlier videos, there are several ways to perform tasks in Access but sticking to one method definitely limits head banging!! Thank you!!
I'm a native Buffalonian, but I've been a Floridian for the past almost 10-years now.
You are absolutely awesome. For some reason, all MsAccess books leave so much practical stuff out. This channel does an amazing job of filling in the blanks. Subscribed!!
Glad you're enjoying.
Just a general observation ... I only tripped across this material yesterday, and since then I have watched several videos (probably too many because it is like getting a drink of water with a firehose) ... but they are captivating ... exceptionally well done ... extremely informative ... the best I have seen (on any topic) ... 👍👍
Thanks for the compliment. :)
I think it is interesting when I watch your videos that some of the suggestions you make are new to me and really helpful; other times I just laugh a little at the fact I just kind of stumbled across these solutions without even realizing that I had done it exactly the way you describe. Looking forward to the next video.
Awesome, thank you!
Excellent explanation from Argentina
Thank you from Florida.
🙂This show is educational and entertaining to watch.
Thanks, Captain.
@@599CD You are welcome. I like to know or see that people are using Microsoft Access. I am satisffied to know that I am not alone.
Hello Richard. Thanks for your awesome videos. I've learnt a lot in the last few days. I noticed that you never use the Database Tools -> Relationships, is there a reason for that? I know it is not required, but saves the headache when updating or, more importantly, deleting records preventing orphaned records being left over from the operation. Is there any reason you don't use the table relationships?
Do you have a video example of what you mention at 20:27 - one extended info table with all the different fields and then separate forms for the different types of customer? Also, would the resulting nulls for empty table fields present an issue?
Hi Heather. Post that in the Forum. 599cd.com/AF
Great
Thanks
I have a follow up question! If an Entity can be more than one Customer Type, what do you recommend? From watching your many-to-many video, I think this means I need a JunctionT but I do not completely understand the benefit of the JunctionT if I will be creating an AirlineT and a HotelT anyway.
One reason I think it would be helpful to keep the JunctionT is that it would make a handy way to answer the question: "What Entity Type is Deanna Troi?"
Here is my "try it yourself": EntityID & HelperID are foreign keys to EntityTypeID in a JunctionT.
EntityID = 3 (Deanna Troi) as Airline => EntityTypeID = 1
EntityID = 3 (Deanna Troi) as Hotel => EntityTypeID = 2
In the subsequent AirlineT, would you refer to Deanna Troi Airline by her EntityID = 3 or by her EntityTypeID = 1 (and why)?
599cd.com/Ask
Your advice on consolidating customer data is great, but here (11:31) you suggest making additional tables to store extended info, which seems to get away from the original goal of minimizing tables. As time goes on, the user may need more and more new entities and have to keep adding tables. That's not a good prospect, because it makes query design difficult if you keep adding table names and field names. If I were in this situation, I would put all the extended info in a pair of fields, ExtInfoName and ExtInfoValue, which let the user enter both the name of that info and its value. If there is too much extended info, store them in a child table and link it to the main customer table via one-to-many. That way, queries would be a piece of cake, since the two field names are always known to you and will never change. P.S. This is like our cell phone's contact app that lets us enter custom labels -- e.g. we can enter "Grandma's number" as a label next to a phone number, or any conceivable names we want to use.
That's definitely an option. Thanks for sharing! :)
That's funny, if they need more entities, that requires more tables doesn't it? Tables split up entities, don't they? I have to assume you mean more CustomerTypes, then that would make some sense. You mentioned putting all the extended info in a pair of fields, but did not say where those fields would go. Can you please clarify. The only way that would make sense if it was in another table and there was again a linking field of CustomerID, then you would potentially have to enter the same name over and over again for different customers in the same CustomerType which leads to variations in the name (user input error). If I'm misunderstanding you, help me to understand what your point is. I guess you could use combo boxes for the ExtInfoName fields but then you would have value list or another lookup table that would have to be maintained anyway.
@@michaelkrailo5725 As I said, look at your smartphone's contact app to see how that's done. You can add "custom fields" to any person's contact info and call them whatever you want. You can add blood type, height, weight, relation, date of birth, employer, anything and everything. And the user doesn't need to modify the database design to do that.
Even though the navigation buttons are turned off if I press page/down I can still move through records, how do I stop that from happening? The current record works for tab but not page up/down.
You'd have to intercept the keystroke and ignore it, or open the form with only that record in the recordset.
I'm stuck with sub datasheet view! for example, I've 3 separate tables. wanna relate them with one junction table. say, number 2 table has a common field ( could be described as the master field ), which is related to two other fields. one from number 1 table and another from number 3 table. after creating the relationship, when u press the " + " sign of the sub datasheet view, " insert sub datasheet " dialogue box appears! why so? I just wanna see related fields to that particular field. why that doesn't happen?
599cd.com/Ask