Clear out data

[DakotaRidge]

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.

 

[DakotaRidge]

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.

 

[arnelgp]

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.

 

[DakotaRidge]

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.

 

[DakotaRidge]

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.

 

[AccessBlaster]

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.

 

[arnelgp]

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?

 

[The_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.

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.

 

[MadPiet]

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.

 

[DakotaRidge]

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.

 

[DakotaRidge]

I'm trying to envision how the OP managed to define so many tables. I have an application I built to track medical stuff and it has 15 tables. I also use relationships which I'm sure the OP doesn't bother with. So, in my case, I can create a test person rather than using my own records and then delete the test person to get rid of the test data. The Doctors table needs to be handled separately since they are not directly connected to individuals. The connection is only through the visit table. So if you want doctors to remain private, you need to delete rows from that table also. There are a few other tables not related to people directly like the doctor type, procedure type, and drugs. Deleting the test person will delete his drugs and his visits but not the related drug or related doctor. So, if you want drugs to b e private, you need to delete those specifically also.

PS, if you compact the db after you delete the test data, the autonumbers reset automagically.

Thanks Pat, you taught me something that I didn't know about compact. I rarely run compact and compare because I don't do operations that change the data very often. This could be a benefit of making my forms so verbose.

I need to look for the autonumbers to change in hundreds of tables. All of my main tables have autonumber fields for the primary keys. But I don't use autonumber in lookup tables that feed MVFs. Those tables usually have only one column. I have two-column lookups when a lookup table uses five-level Likert scales. I do that often, but not with MVFs because they can't handle the math and statistics that I use in my applications.

 

[DakotaRidge]

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.

Not sure I would be proud of that since it highlights your lack of understanding of something very basic.

Pat, my database will handle new grocery stores just fine. I store the names in a lookup table and forms use dropdown fields to assign them to each transaction. Having the names in lookup tables, I can use them in simple fields or I can use them in MVFs. They are always up to date. The problem with grocery stores where I live is that the company is more likely to close them than to open a new one. One chain goes on strike every other year, fortunately I don't shop those stores very often. Thanks, I need to add a field to the grocery store table for "Strikes Likely."

 

[DakotaRidge]

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.

Not sure I would be proud of that since it highlights your lack of understanding of something very basic.

And Pat, what is the basic principle about not having lots of forms in a database?

I am curious to know why I shouldn't have 4 or 5 thousand forms and reports. They are used to store and display data differently. My database has about 4000 tables. The forms do different kinds of statistical analyses. And they have different kinds of graphics and speech for different users. I try to understand each user's personality using the Myers-Briggs Type Indicator. I hope to use the 16 personality types to create a better database system.

Last week, for example, the food recall forms went from having only maps of the USA to having images of squirrels and spirals. The form with squirrels is for a family member in Massachusetts who loves animals. The spiral graphic is for me.

If I need to, I can split a database into multiple applications. That's what I was in the process of doing last year when my PC went belly up. Fortunately, I have hundreds of backups on USBs, and the hard drive is still good.

Thanks.

 

[NauticalGent]

For context:

Post in thread 'What was/is your "largest" access project' https://www.access-programmers.co.u...ur-largest-access-project.326316/post-1885775

 

[Mark_]

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.

DELETE FROM table_name WHERE condition;
I'd suggest doing a little reading on SQL.

 

[Gasman]

Well, if it was normalised and you had cascade deletes on,deleting the top parent record,would delete all the child records.

 

[Jason Lee Hayes]

Based on arnelgp code:

Will only delete data from fields WHERE the field metadate description = [sensitive]

Within your Table Field Name Description add [sensitive] to any field you would like to clear.

 

[MajP]

Ahh, yes. The Rube Goldberg school of design. Always more fun.

Why do simple when you can be complicated.

 

[NauticalGent]

Ahh, yes. The Rube Goldberg school of design. Always more fun.


View attachment 120671

Why do simple when you can be complicated.

This is my co-worker to a "T". I had never heard of this cartoon until HE compared himself to it.

 

[Mark_]

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.

With the number of objects you have, I'd estimate your application has very poor agility. Case in point is your asking for help in clearing individual records from 50 tables when in a normalized database this would be a simple delete query OR use of referential integrity to delete the required records without additional coding.

Normalized data mitigates future structure changes. Same with your multiple lookup tables. You could replace them with a single table that stores ID (Autonumber) / SortType (Tells you what the lookup is for, say hair color or store name) / SortValue (Description to show, such as "Green" or "Walmart") / SortOrder (Optional numeric to provide a custom order). As you've posted you have lookups with one field you would need to store the full value in them inside another record instead of just being able to reference that value. Definitely not an agile approach. Even worse, you can't assign the order you want your lookup values to appear in.