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!!
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) ... 👍👍
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!!
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.
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?
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)?
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?
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 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.
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?
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.
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.
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. :)
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.
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?
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
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
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.
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
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.
Great
Thanks