Clear out data (3 Viewers)

[DakotaRidge]

I have been working on a database since January. I am about ready to send it to members of my family, but first I want to remove confidential data that I used to test the application.

Of the 450 tables, perhaps only 50 of them have any sensitive information that I would remove. The sensitive data are medical records. There are no social security numbers or credit card numbers, but someone may want to store that information in the future.

Perhaps only one record in a table with a hundred records may have sensitive data.

I would also like to give my family an easy way to clear out their personal data if they ever want to send the database to another family member.

Is there a tool that will do this? The tool needs to only delete selected records in a table. I will add checkbooks to tables for this purpose.

Thanks.

 

[Gasman]

That tool is you.
I would just delete all the data.
I do like to set the autonumbers back to 1 as well. Just my preference.

However, I thought all that data you posted on UA, was just test data?, as that was what I recall you said when I commented on anomalies?
If it is one record out of 100, then manually.

Next time, perhaps add a field to indicate sensitive or not, if you are going to have this issue in the future.

 

[MajP]

Of the 450 tables

Something sounds very concerning. Unless you are building a commercial grade ERP I highly doubt this database is properly normalized with 450 tables. Correct me if I am wrong, but I really doubt it.
Not trying to insult anyone here, but you are asking a somewhat trivial question but at the same time you have a database that is orders of magnitude bigger than anything I have ever built and I have built thousands of databases. I would be very interested in seeing 450 viable tables. I have seen databases this big, but they are commercial grade products.

 

[June7]

450 tables for a database distributed to family members? I also suspect this db is not optimally structured. Suggest you post db here for review.

Keep of a copy of db without data. Be aware, any changes to design should be made in a master copy and db should be split so updates to GUI do not cause loss of data.

 

[Gasman]

Something sounds very concerning. Unless you are building a commercial grade ERP I highly doubt this database is properly normalized with 450 tables. Correct me if I am wrong, but I really doubt it.
Not trying to insult anyone here, but you are asking a somewhat trivial question but at the same time you have a database that is orders of magnitude bigger than anything I have ever built and I have built thousands of databases. I would be very interested in seeing 450 viable tables. I have seen databases this big, but they are commercial grade products.

@MajP
Save your breath. You are just wasting it. It was said time and time again at UA and ignored.
O/P will have as many forms as well.

 

[Pat Hartman]

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.

 

[XPS35]

Something sounds very concerning

Get used to it
OP used to post on UtterAcces and seems to have moved to AW after UA disappeared. He is "famous" for this.

 

[DakotaRidge]

That tool is you.
I would just delete all the data.
I do like to set the autonumbers back to 1 as well. Just my preference.

However, I thought all that data you posted on UA, was just test data?, as that was what I recall you said when I commented on anomalies?
If it is one record out of 100, then manually.

Next time, perhaps add a field to indicate sensitive or not, if you are going to have this issue in the future.

Gasman, I am in the process of adding "Is Sensitive" fields to some of the tables. Users will check boxes to set those values to True.

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.

 

[DakotaRidge]

Something sounds very concerning. Unless you are building a commercial grade ERP I highly doubt this database is properly normalized with 450 tables. Correct me if I am wrong, but I really doubt it.
Not trying to insult anyone here, but you are asking a somewhat trivial question but at the same time you have a database that is orders of magnitude bigger than anything I have ever built and I have built thousands of databases. I would be very interested in seeing 450 viable tables. I have seen databases this big, but they are commercial grade products.

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.

 

[Gasman]

Easy enough to walk through all tables and delete anything with IsSensitive set.
Otherwise store those table names in yet another of your tables, and walk that table.. It is not like it is going to make a huge difference.

 

[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. Do 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, your model is similar to mine. My database is designed to store both financial and health data. I suspect some family members will not want anyone else to see their data. But they may want an adult child or spouse to see certain information, so they will need the ability to delete specific records in a table. They would leave other records to show folks how to correctly populate a table.

Examples of test data could include new credit card or bank accounts. Similarly, a person's 46 medical tests is considered sensitive data. Those data will be sensitive if an insurance company is able to deny a policy based on a test result. Been there.

 

[plog]

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

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.

 

[DakotaRidge]

Thanks Pat, your model is similar to mine. My database is designed to store both financial and health data. I suspect some family members will not want anyone else to see their data. But they may want an adult child or spouse to see certain information, so they will need the ability to delete specific records in a table. They would leave other records to show folks how to correctly populate a table.

Examples of test data could include new credit card or bank accounts. Similarly, a person's 46 medical tests is considered sensitive data. Those data will be sensitive if an insurance company is able to deny a policy based on a test result. Been there.

Also Pat, I read a lot and so I get a constant stream of ideas from books and magazines such as Mosby's Manual of Diagnostic and Laboratory Tests, Dave Ramsey's Complete Guide to Money, Harvard Business Review, MIT Sloan, and Kiplinger Personal Finance. I also get information about food recalls via MSN, FDA, and CDC. And members of my family tell me things about their health, so I know a few things about diabetes, heart disease, kidney failure, lupus, etc.

But I am not a physician, and so I am always learning something new about diseases, illnesses, medicines, food contamination, and money. And I am always learning new things about Access.

Thanks again.

 

[MajP]

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.

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.

 

[The_Doc_Man]

I am not going blindly criticize, but I will say that the U.S. Navy Reserve database, at the time I retired from my contracting position, was 240 tables, about 60% of which were translation/code-lookup tables. These tables included three top-level tables: the person, the billet (job), and the Navy Reserve unit. A lot of the tables were "sparse" in that they were joined via OUTER JOIN rather than INNER JOIN, and we did null testing for cases where "did not apply" was the correct response. Things like dependents, for example. And 240 tables was a LOT of tables, but for the military I was told that was about par for the course when dealing with personnel.

 

[LarryE]

Thanks Pat, your model is similar to mine

I doubt that....LOL.

 

[DakotaRidge]

I am not going blindly criticize, but I will say that the U.S. Navy Reserve database, at the time I retired from my contracting position, was 240 tables, about 60% of which were translation/code-lookup tables. These tables included three top-level tables: the person, the billet (job), and the Navy Reserve unit. A lot of the tables were "sparse" in that they were joined via OUTER JOIN rather than INNER JOIN, and we did null testing for cases where "did not apply" was the correct response. Things like dependents, for example. And 240 tables was a LOT of tables, but for the military I was told that was about par for the course when dealing with personnel.

Thanks Doc_Man. I admit that my applications have

I am not going blindly criticize, but I will say that the U.S. Navy Reserve database, at the time I retired from my contracting position, was 240 tables, about 60% of which were translation/code-lookup tables. These tables included three top-level tables: the person, the billet (job), and the Navy Reserve unit. A lot of the tables were "sparse" in that they were joined via OUTER JOIN rather than INNER JOIN, and we did null testing for cases where "did not apply" was the correct response. Things like dependents, for example. And 240 tables was a LOT of tables, but for the military I was told that was about par for the course when dealing with personnel.

Thanks, Doc_Man.

I admit that my databases cannot hold a candle to the ones that professional developers create, which is why I ask questions here and did so on UA until one day it was gone. When I was obtaining my first PhD, I knew nothing about Access or RDMS. Near the end of that period, I was introduced to DB2 and later to rBase5000. Later one of my Access files grew to be more than 4,000 forms and more than 4,000 reports.

Here's part of the navigation form for the database that I started in January. Each button opens a different form in the 27 domains. Many of the forms have text-to-speech, and most have two images.

Thanks for your service. I drilled with a Navy Reserve unit in Phoenix circa 1975 when I was in the Army Reserve.

I want to help my family members use the database to the fullest when I release it to them. It is not a commercial product.

 

[June7]

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.

 

[MajP]

Here's part of the navigation form for the database that I started in January.

Looks to me that you could support that information with 10 or 15 tables/child tables (20 max). What are the other 430 doing?

 

[The_Doc_Man]

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.