Clear out data (1 Viewer)

[MajP]

MajP normalize the tables

I am not sure if normalization is really the problem as much as consolidation if that is a term.

For example you commonly see people build a table of Customers, Suppliers, etc.. Or a different table for different types of contacts. Sometimes these tables often have the same fields or nearly identical fields. FirstName, LastName, CompanyID, Street, City, State, Zip .....
And that is perfectly Normal to have 2 tables. But as a developer do I really want to maintain 2 or more table, if I can consolidate into 1? Although the OP prides himself in creating as many tables as possible, everyone else in the world wants to limit the number of tables to build in maintain. When possible we add a ContactType field to the table (Suppier, Customer, ...) and now there is one table.
Same with Expenses. I can have groceries, gasandoil, insurance etc. And that is probably normal but can this be consolidated into a single table of expenses. Can I genericize certain things to make it fit into one table? Consolidation is harder because you have to really try to decide what to do if different entities have unique attributes and can you design around that. Are Doctors and Dentist that different (in sense of DB)? TblMedicalProvider maybe?

The big difference is that most of us will look for opportunities to consolidate because we know the time and effort savings in design and maintenance. We know that we do not get extra credit for having lots of tables. The OP thinks somehow that there is something impressive about creating more of everything, when in fact everyone knows it is far harder and more skilled to create less. We understand the downstream time and effort savings.

You can actually go to extremes and possibly do this entire DB in 5 forms.

Dynamic/reusable forms

New to this forum, triggered by a thread, but alas I cannot find that thread. So I will sketch my situation. Since long I work on generalization of code to make programs more powerful and robust. Code must be as structured as normalized data. This has resulted in automation of many of the...

www.access-programmers.co.uk

That is a little on the extreme side, but experts will go in this direction because of the long run time and effort savings.

There are times that I might denormalize if I know the maintenance and time savings are worth it. For example the Entity Attribute Value model is probably not a normal design, but can save a huge amount of time and resources in the correct application.

record value in table when one of many buttons are clicked

That's exactly what I did with my tblParts, tblFinalProducts, and tblAssemblyComponents. Exactly that.....and it works great. I get that. Thank you for the effort of showing me a "map." Works well for me. If that is what we're all talking about then it may be conceivable to incorporate that...

www.access-programmers.co.uk

This is a little harder to build, but way easier to maintain

I think the OP really does not get it. The OP seems to somehow think we are impressed by the size and scale of the project. He brags about this, which to most of us is like bragging about how many times you have gone out drinking and driving and have not been caught. It is cringy. In reality it is the complete opposite. We are sad for the time and effort wasted and see this as not being impressive but a disaster and waste of time. That is why so many people are trying to help because they see the train wreck and could save the OP an immeasurable amount of effort.
A beginner can make a huge inefficient db, but it takes knowledge to make a compact and efficient one. When I write code or build a db I am embarrassed if it is long and hard for the user to understand. If it is compact, easy to follow, flexible, reusable, and efficient then I am happy.

 

[DakotaRidge]

I am not sure if normalization is really the problem as much as consolidation if that is a term.

For example you commonly see people build a table of Customers, Suppliers, etc.. Or a different table for different types of contacts. Sometimes these tables often have the same fields or nearly identical fields. FirstName, LastName, CompanyID, Street, City, State, Zip .....
And that is perfectly Normal to have 2 tables. But as a developer do I really want to maintain 2 or more table, if I can consolidate into 1? Although the OP prides himself in creating as many tables as possible, everyone else in the world wants to limit the number of tables to build in maintain. When possible we add a ContactType field to the table (Suppier, Customer, ...) and now there is one table.
Same with Expenses. I can have groceries, gasandoil, insurance etc. And that is probably normal but can this be consolidated into a single table of expenses. Can I genericize certain things to make it fit into one table? Consolidation is harder because you have to really try to decide what to do if different entities have unique attributes and can you design around that. Are Doctors and Dentist that different (in sense of DB)? TblMedicalProvider maybe?

The big difference is that most of us will look for opportunities to consolidate because we know the time and effort savings in design and maintenance. We know that we do not get extra credit for having lots of tables. The OP thinks somehow that there is something impressive about creating more of everything, when in fact everyone knows it is far harder and more skilled to create less. We understand the downstream time and effort savings.

You can actually go to extremes and possibly do this entire DB in 5 forms.

Dynamic/reusable forms

New to this forum, triggered by a thread, but alas I cannot find that thread. So I will sketch my situation. Since long I work on generalization of code to make programs more powerful and robust. Code must be as structured as normalized data. This has resulted in automation of many of the...

www.access-programmers.co.uk

That is a little on the extreme side, but experts will go in this direction because of the long run time and effort savings.

There are times that I might denormalize if I know the maintenance and time savings are worth it. For example the Entity Attribute Value model is probably not a normal design, but can save a huge amount of time and resources in the correct application.

record value in table when one of many buttons are clicked

That's exactly what I did with my tblParts, tblFinalProducts, and tblAssemblyComponents. Exactly that.....and it works great. I get that. Thank you for the effort of showing me a "map." Works well for me. If that is what we're all talking about then it may be conceivable to incorporate that...

www.access-programmers.co.uk

This is a little harder to build, but way easier to maintain

I think the OP really does not get it. The OP seems to somehow think we are impressed by the size and scale of the project. He brags about this, which to most of us is like bragging about how many times you have gone out drinking and driving and have not been caught. It is cringy. In reality it is the complete opposite. We are sad for the time and effort wasted and see this as not being impressive but a disaster and waste of time. That is why so many people are trying to help because they see the train wreck and could save the OP an immeasurable amount of effort.
A beginner can make a huge inefficient db, but it takes knowledge to make a compact and efficient one. When I write code or build a db I am embarrassed if it is long and hard for the user to understand. If it is compact, easy to follow, flexible, reusable, and efficient then I am happy.

Well guys, I have decided to leave the Access community. Splitting a database makes no sense to me. 2Gb is is not enough for me. Standard normalization is a pain. I focus on graphics and speech in my applications, and almost no one else in the community does that. For me, the report is key, not the tables and queries. I don't code, and I really don't want code in my databases.

With the demise of UtterAccess, much of my Access world is gone. Losing UA was a shock. I didn't see that coming. AWF helped for awhile, but UA was my community of choice for years.

I thank you all for your help. Always being told to split and normalize has been stressful at times.

Live long and prosper. Maybe I will go out of town and get Covid.
So, au revoir.

L. David Nealey, PhD.

 

[The_Doc_Man]

Fare thee well, Dr. Nealey from Dr. Hunt. Understand that when you ask for advice from people with experience, we know what will eventually come back to bite us on the butt. The multiple admonitions on splitting databases and suggested modes of normalization related to our own derriere scars that left us looking pretty gnarly, not to mention cushioned chairs becoming mandatory. We were trying to spare you the cost of a plastic surgeon to cover up those scars. Your reluctance to take advice is, of course, your choice. Good luck in your future endeavors, but since we cannot share your vision completely, we probably will not be able to help you a lot. And it is important to have that understanding.

 

[DakotaRidge]

Fare thee well, Dr. Nealey from Dr. Hunt. Understand that when you ask for advice from people with experience, we know what will eventually come back to bite us on the butt. The multiple admonitions on splitting databases and suggested modes of normalization related to our own derriere scars that left us looking pretty gnarly, not to mention cushioned chairs becoming mandatory. We were trying to spare you the cost of a plastic surgeon to cover up those scars. Your reluctance to take advice is, of course, your choice. Good luck in your future endeavors, but since we cannot share your vision completely, we probably will not be able to help you a lot. And it is important to have that understanding.

Thanks again Dr. Hunt for your help over the years.

I have never asked anyone in UA or AWF about splitting a database or normalizing a table. Those are topics that folks forced upon me. I have said many times that I am not splitting. If that means my databases cannot exceed 2Gb then that's something that Microsoft needs to address. I suspect your smartphone has more than 2 Gb of storage, why not Access?

Next, I will get off Outlook. I use the previous version, but it also loses messages.

And this darn AI is a pain. I didn't ask for it. I don't want AI on my computer or phone. It is taking too many jobs at MS and elsewhere. I am glad to see my state take a stand, I hope that it is the right one, which is to outlaw AI.

Again, goodbye to the community. I will not see your emails any longer.

 

[NauticalGent]

What is tragic, is one of the most brilliant minds in the wide world of Access (IMHO), offered to take this one on - no strings. I for one, was anxious to see what came of it. But for some reason, he refused.

Sad actually.

 

[MajP]

Well guys, I have decided to leave the Access community. Splitting a database makes no sense to me. 2Gb is is not enough for me. Standard normalization is a pain. I focus on graphics and speech in my applications, and almost no one else in the community does that. For me, the report is key, not the tables and queries. I don't code, and I really don't want code in my databases.

Oh I see. You simply need one of the many available applications with the following
1. low code, no code application
2. supports unstructured data including free text
3. Allows development of graphics and speech based GUIs without code
4. Flexible to allow continuous additions
5. Design interface to easily manage thousands of objects
6. Secure to handle sensitive information
7. Non-Microsoft Product
8. I assume you want the freeware version

According to Chatty gives these apps a try, but I strongly recommend the subscription version of
Unobtainium.exe


And the results

1. Appgyver
Type: No-code platform
Features: Supports unstructured data, rich visual interface, multimedia elements (including speech), and scalable design.
Security: Enterprise-grade security options available.
Cost: Free for small-scale projects with limitations, suitable for initial development.
Suitability: Good for creating GUIs with graphics and speech integration.

2. Thunkable
Type: No-code app builder
Features: Supports multimedia, voice, and unstructured data, allows visual development, manages numerous objects, and supports continuous updates.
Security: Provides security features suitable for sensitive data.
Cost: Free tier available.

3. MIT App Inventor
Type: No-code/low-code for mobile apps
Features: Visual interface, supports multimedia, voice, and free text data, scalable design considerations.
Security: Basic security features, with additional security potentially via integration.
Cost: Free and open-source.

4. Bubble
Type: No-code web application builder
Features: Supports unstructured data, flexible design, large-scale object management, and multimedia.
Security: Advanced security options.
Cost: Free plan with limitations

I was kind of joking at first, but now I am intrigued.

 

[The_Doc_Man]

If that means my databases cannot exceed 2Gb then that's something that Microsoft needs to address.

Actually, they DID address it - by choosing to NOT widen internal addresses when they did the 64-bit upgrade with Word and Excel that expanded THEIR internal addresses. We must remember that old adage... "not to decide is to decide." MS apparently didn't want the engineering headache of going through all of the code involved in Access to switch to 64-bit pointers. Their choice, not ours. At least some of us conjecture that they didn't want to create something that would compete in some limited way with SQL Server.

 

[Uncle Gizmo]

Well guys, I have decided to leave the Access community

I'm disappointed, you can't even flounce properly!

 

[arnelgp]

goodbye my friend it's hard to die..

just make frequent backup.
just remember it's your db, you own it, nobody tells you what to do.
and if it is not broken, don't fix it.

at the end of the tunnel, there is light and i'll see you there..

 

[BlueSpruce]

Well guys, I have decided to leave the Access community. Splitting a database makes no sense to me. 2Gb is is not enough for me. Standard normalization is a pain. I focus on graphics and speech in my applications, and almost no one else in the community does that. For me, the report is key, not the tables and queries. I don't code, and I really don't want code in my databases.

With the demise of UtterAccess, much of my Access world is gone. Losing UA was a shock. I didn't see that coming. AWF helped for awhile, but UA was my community of choice for years.

I thank you all for your help. Always being told to split and normalize has been stressful at times.

Live long and prosper. Maybe I will go out of town and get Covid.
So, au revoir.

L. David Nealey, PhD.

Sayonara for now, David. I imagine you'll come back

 

[NauticalGent]

at the end of the tunnel, there is light and i'll see you there..

And in his case, it just might be the on-coming train...

 

[BlueSpruce]

Just so you know, if you use a CC or Debit card, you can download the monthly statements and import them to a database. Saves a lot of work. My checking account is with my investment account so all the cash is in a money market fund that pays the top short term interest rate which is just under 4% these days instead of the paltry amount paid by banks. I just moved it two months ago. The software is crap but I'm hoping enough people complain so they make it better. But at least you can download the details including your notes when you write the checks to pay the CC bills. I

And if you use a Walmart CC at Walmart or Sams, their printed receipts as well as the monthly statements list the details of each item you purchased. Their website provides downloading that detail to Excel files which can then be imported into Access.

 

[MajP]

I find it funny that anyone would not want to use VBA over macros now a days. Since you do not have to write it yourself.
I can ask chat to write pretty complicated things in vba and it comes out pretty complete. Maybe a little tweak if I did not ask the question well. It is faster for me to do that do that then look through my libraries of example code.
If I ask it to make a macro for any real thing it will normally say you cannot do that with a macro, but here is the vba.

 

[BlueSpruce]

I am not sure if normalization is really the problem as much as consolidation if that is a term

How can you merge all those forms if the data is not normalized? He has several fields for items in same categories, and tables with repeated fields in other similar tables.

 

[MajP]

How can you merge all those forms if the data is not normalized? He has several fields for items in same categories.

I did not say there was not normalization problem I said it was not as much of a problem.
1. The OP probably does not have a lot of forms because the tables are not normalized, they have a lot of forms because they have multiple tables doing the same thing. Expense are expenses. Do not need and oil and gas form and a pharmacy expense form once you combine them into a single expense table. Then you have a form for expenses.
2. I there are 10 tables with normalization problems but they can all be combined into a single table you do not need to fix the individual tables' normalization problems. You need to combine the tables and ensure you fix it there.

 

[AccessBlaster]

I'm keeping my eyes peeled for any new members with states in their handle, just saying

 

[BlueSpruce]

I am not sure if normalization is really the problem as much as consolidation if that is a term

How can you merge all those forms if the data is not normalized? He has several fields for items in same categories

A lot of the forms were built on queries that selected hard coded criteria. So, even though there was one table to hold all grocery stores (not quite normalized), there were separate forms for each named store so Walmart had its own form for reasons that were never actually explained.

But, looks like Dr. Neeley wants to go away now so say good-bye.

I should've said "How can you merge all those forms without first normalizing the data?"

 

[BlueSpruce]

The data is not as unnormalized as it could be but the form proliferation makes it look worse than it is.

I'm pretty sure he has repeated fields scattered across several tables, and specific fields, like WalmartItemName, MedicationName1, Name2, etc.

 

[BlueSpruce]

But we don't care any more. You're talking to a person who is no longer with us. Let it go.

Well, I know he still receives emails of our posts. He'll be back...

How many other Access apps with similar design do you think exist?.. Millions! I've come across several, all built with macros, tons of tables, queries, forms, reports.

Why do they build apps like that?... They're using their common sense without first learning relational design. It's in several books, including Access For Dummies.

 

[MajP]

How many other Access apps with similar design do you think exist?.. Millions! I've come across several, all built with macros, tons of tables, queries, forms, reports.

From what I have seen, there is 1 and only one! You are looking at it.
I have seen thousands of examples on this forum and other forums of inefficient designs. I have never thing anything even 1/100th of the purported scale of inefficiency.

Everything else I have ever seen, you could help the OP take a dozen forms, queries, or tables and consolidate. Or normalize the data and knock it down from 10 tables to 3. There is only one person in the world who has hundreds/thousands of objects to do something that could be done in 1/100th of that.