Clear out data (6 Viewers)

You could replace those collums with subforms:-

A Look at the Multiple Checklist Code - Nifty Access

 
Nothing you have provided, including latest post, counters viewpoint that this db is likely not optimized. And unless you share it for review, we can hardly be swayed. However, since it seems you are set on continuing with this design, I wish you luck.

Did you at least consider recommendation for splitting? Certainly should if you plan on maintaining and providing other users with updates.
Hi June7.
 
You show us a switchboard or dispatcher form that you can use to select reports or forms or both. Doesn't matter what they pull up, but it DOES matter what those things in turn pull up for you and from what those things pull something up. From your display in post #17, if those items are reports that track or forms that operate by filtering a category table, that could be perfectly OK. Which is why I'm not pointing fingers at the moment.

However, if those lower-level options represent a form or report based on separate and distinct tables for each thing you are tracking, ...

a) Your Grocery Bills selection is DEFINITELY denormalized if you have tables for each grocery. That should be 1 and only one table for that with the grocery name or ID or code as a field.
b) Your Insurance Bills represents another likely denormalized area that would need serious normalization.
c) I suspect the Diseases category has the same susceptibility.

Like I said, you showed us something that could be quite reasonable. But you didn't show us enough for us to know the difference between totally normal and totally outrageous. So with a little more insight, we might be able to smooth out that "itch" that each of us feels in the presence of a vaguely suspicious project description.
Thanks Doc_Man, the forms will allow my family members to enter the data that they will need to save money and protect their health. I am sure of that because I have stored information from some of the best minds in the world and several government agencies in the database already.

I am building the database for the way that I think and work Only 3% of the population has my personality according to the Myers-Briggs Personality Type literature.

All I need now is a way for family members to delete records with sensitive data. So far, no one here has offered me any ideas to do that. I code as little as possible, and I will need to convert code into macros for this database. I am sorry but I don't understand how normalization affects record deletions.

I am developing the application using an Agile development process. That means that I expect to change the structure in the future. Before I can do that, I need to add all of the functionality that family members tell me they need.
 
Yeah, when building a house just slop some concrete in a hole, throw in some rebar and build on top of it. In a year or two--after you've done the important things like paint the 4th bedroom the proper color of eggshell and chosen the right cabinet fixtures for the third floor bathroom you can always go back and fix the lesser issues like the foundation.
Plog, this is like building an entire city for people who have lived in the desert all of their lives. The first thing that I usually do is paste two pictures into a form, and then I add text for the form to read aloud. A family member tells me what the form will say.
 
I do not know what world you are in, but I can guarantee that everyone that knows anything about databases does not visit that world.
Being proud of the excessive amount of non-normalized data tables is like walking into a hoarders home and they are proud that somewhere laying on the floor is every piece of junk mail they have ever received. When you say in the next year you will have more tables, is a definite sign that the database is designed incorrectly. Databases do not grow in tables as data grows. They grow in records.

But good luck on this. Sounds horribly painful.
Pete, when I say that the database will have twice as many tables/forms/reports next year, it means that I am constantly finding new information to add to the database. When I started back in January the only domains that I considered were financial ones. I read lots about financial systems such as Quicken, to understand things that I could put in the database. A friend suggested that I subscribe to Quicken but no, I needed graphics and speech in my system. I have a copy of Microsoft Money somewhere, but do you think I use it?

Then I started going to public libraries to check out books and magazines about personal finance. I found a goldmine in the Kiplinger Personal Finance magazine. Then I started subscribing to Kiplinger online. More good information.

Then I started asking family members for their budget templates in Excel. Later, I purchased textbooks on finance and accounting.

I still consider myself to be a dummy about finance. I worked for an insurance company twice. I passed the state exam twice.

Later, I realized the connection between personal finance and health. Family members started telling me that they have serious health issues, so I started reading the medical literature. Each article led me to create a new module in the database. Yes, I could have done more with normalization, but I discovered a new disease every week.

Then I got hit in the face with food recalls. There was a recall almost once a week, and then twice a week. I had already dealt with recalls of medicines.

Somewhere along the line, I decided to track pet care costs. That taught me how much a family can spend on cats and dogs. It's food, medicine, doctors, and surgeries.

You and others here probably develop applications for a business or government agency. They hire you to create a database that they have defined. That's not how I work. Who knows what I will discover and want to add to the database tomorrow?

I admit that the database is designed incorrectly. But show me an application with everything that I have in my system, changes from day to day, and, and, and.

Thanks for your interest.
 
You can also do the Deleting or records from another database.
open form frmTableList from the demo and select the external db.
then select which tables to delete records from (note the Listbox
will Not Contain linked Excel/text file and the DB to open is not
encrypted with password).

the demo was made in A2021.
 

Attachments

Looks to me that you could support that information with 10 or 15 tables/child tables (20 max). What are the other 430 doing?
Pete, just because there are several forms and reports for grocery purchases, that does not mean that there are multiple tables for grocery stores. This is a situation where I created a form for the Walmart to the west of my house, the King Soopers to the north, and the Safeway to the south, plus the Costco down the street. They are different kinds of stores and I shop them in different ways. Some deliver groceries and some don't. So a form doesn't necessarily mean what you think. The different forms have different pictures and they calculate total costs differently.
 
You can also do the Deleting or records from another database.
open form frmTableList from the demo and select the external db.
then select which tables to delete records from (note the Listbox
will Not Contain linked Excel/text file and the DB to open is not
encrypted with password).

the demo was made in A2021.
Thanks arnelgp. That could come in handy with a different group of users. I am sure none of my family will ever use two databases. Thanks for sharing.
 
This is a fascinating read. It reminds me of the mistakes I made early on with my first databases. Those were frustrating times, trying to apply best practices to unorthodox designs. Man, that was tough.
 
If you have A2021 or 365 you can copy the form on the demo to your db.
run the form to delete records from your table.

there is a little table on the demo, table1. see it's content and run the form to delete it's record.

Edit: what happened to your Map thread, did you got it solved or have chosen another path?
 

Attachments

the forms will allow my family members to enter the data that they will need to save money and protect their health. I am sure of that because I have stored information from some of the best minds in the world and several government agencies in the database already.

Don't conflate the actual information with its organization.

Opening up file cabinets and upending hanging folders into a pile on the floor doesn't change the amount of raw information but it DOES interfere with accessing said information. Stated another way, the subject information doesn't change - but the meta-information (that enabled you to find it on demand) DOES change in the file-cabinet-dump activity. The entropy of the contained data doesn't change - but the entropy of the containing medium DOES. Your PhD background should enable you to understand this concept.

Let me try it this way: Normalization REDUCES the net entropy of the stored information by organizing and simplifying the internal structure. On the grand scale, entropy tends to maximize - which is to say, become disorganized. But by applying energy - the energy required to organize (normalize) - you can REDUCE the net entropy of your system, thereby affect the amount of work required. And... big hint here: If you know the relevant laws of thermodynamics, you realize that increased entropy increases the amount of work. Because it is the increased randomness of a disorganized system that produces mechanical friction (resistance) or static (random noise=unwanted signals) in whatever you transmit. Normalization is what makes it easier to more quickly AND RELIABLY reach your data. Therefore, when you want to delete the data (as indicated in the thread title), you can make THAT step easier.
 
Pete, in my world, this is a very small database. Wait until I get to year two. 450 tables is nothing for me to have in an Accdb file. This is just the start of an application that could have thousands of tables when it is done.

Normalization to DKNF is something that I won't worry about until later. I am not worrying about it yet.

"Normalization to DKNF is something that I won't worry about until later" -- that's a huge red flag right there. If you do it up front / on paper before you build anything, it saves a ton of time later on. I think I'll just quietly back away from this one... Sounds like volcanics... Oh right... and one of the nice benefits of proper normalization is that attributes are attached to the correct object... no need to hunt all over kingdom come for them.
 
I want a tool that will run through all of the tables and delete all records where "Is Sensitive" is true. Doing this in 50 forms won't be hard for me, but it could be a lot of work for someone who knows nothing about Access.
Feel free to build one. This task is not sufficiently generic enough that you are likely to find an example. The concept is flawed also since it relies on the user knowing ahead of time what is sensitive and marking it so and since you have hundreds of tables and corresponding forms, you have a lot of modifications to make to the application. Your image in #17 is about as terrifying as it gets. I'm sure many people have tried to explain normalization to you but they have failed. Sure hope no new grocery store opens near you.
Later one of my Access files grew to be more than 4,000 forms and more than 4,000 reports.
Not sure I would be proud of that since it highlights your lack of understanding of something very basic.
 
This is a fascinating read. It reminds me of the mistakes I made early on with my first databases. Those were frustrating times, trying to apply best practices to unorthodox designs. Man, that was tough.
Yes and thanks AccessBlaster. I have only developed about four Access databases in my entire life. Unlike most other Access users, I don't code and I structure tables (normalize) in my head. I know very little about financial and medical sciences. I didn't go to college and study either of those subjects. That all says that I am making mistakes left and right. But I figure that it is better than doing nothing in retirement.

Have a great day.
 

Users who are viewing this thread

Back
Top Bottom