Clear out data (2 Viewers)

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.
Isn't that what queries are
 
The "tool" is a DELETE query with filter criteria.
@DakotaRidge, Do all tables contain sensitive data? Unless there's a boolean field for designating a row as having sensitive data, how is the query going to kno which rows to delete? Why not just delete all rows and compress db so whoever gets the app starts with an empty db? Keep in mind that if you don't split the app into front and backend, every time you provide users an update it's going to overwrite all the data they entered.
 
Last edited:
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.

If you split the database into separate front and backs ends, then you can have one back end file, distinct from your operational back end, with only the data you want others to have, e.g. referenced tables of cities for instance. You'd then distribute the front end with the 'safe' back end.

The recipient will then have to refresh the links in the front end with the back end of course. This can be done with the built in Linked Table Manager, or you can build in the means of automatically checking for a valid back end at runtime, and refreshing the links if necessary. The attached little demo file illustrates how to do this.

Regardless of this facility. Splitting a file into front and back ends is advisable in any case. By isolating the data from the interface, should any interface object become corrupted the data will remain unaffected. All that is then necessary is to replace the front end with a clean back-up copy. Splitting the file also allows you to build in a facility to back up the back end regularly from within the front end. I do this with my personal databases, allowing backups to be made to both local media and the cloud with a few button clicks.
 

Attachments

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
Not sure if you have now got ideas but here is one. If your family member has a pk that is linked to fks in various tables you can use a simple delete query for each table to delete records with that fk.

You could have an admin table to list the tables that would be affected

The same table could have a list of the fields that could contain sensitive data so these fields could be zeroed out instead of deleting the entire record

But that would mean venturing into VBA territory. Not sure it could be done with a macro
 
Might be easier to just clear the lot out and let the new user put in what they need?
Not everyone is going to shop at the same places as the developer, nor have the same medical issues?
 
I'm sure this has been mentioned.
Each family member has a unique login that prevents them from seeing other sensitive records, no need to delete any records because they can only access their own personal account.
 
I'm sure this has been mentioned.
Each family member has a unique login that prevents them from seeing other sensitive records, no need to delete any records because they can only access their own personal account.
No. My understanding is it is one DB, not even split.
DB has developer's real data in it. Though over in UA he said it was just test data and not real data, when I pointed out inconsistencies in it?
Developer wishes to hand it over for a family member to use. If another family member in that same family wants to use it, they will have their own copy.
 
I'm sure this has been mentioned.
Each family member has a unique login that prevents them from seeing other sensitive records, no need to delete any records because they can only access their own personal account.
Thanks. AccessBlaster. No one has suggested this approach.

Can you recommend an Access textbook that has a step-by-step description for doing this? I need details so I can understand the process and if there are any gotchas.

One need that I will have is to have public information in a table. When I distribute the database, it will have several records that will show a person how to populate the table. I need those records because some people may not understand what a report needs to do its job correctly. Some fields will be easy to understand. like Medicine Cost.

I have never shared an .Accdb file with anyone. This is virgin territory for me. Thanks again.
 
Thanks. AccessBlaster. No one has suggested this approach.

Can you recommend an Access textbook that has a step-by-step description for doing this? I need details so I can understand the process and if there are any gotchas.

One need that I will have is to have public information in a table. When I distribute the database, it will have several records that will show a person how to populate the table. I need those records because some people may not understand what a report needs to do its job correctly. Some fields will be easy to understand. like Medicine Cost.

I have never shared an .Accdb file with anyone. This is virgin territory for me. Thanks again.
Each member will need the same version of ACCESS as you have or higher.
 
I'm sure this has been mentioned.
Each family member has a unique login that prevents them from seeing other sensitive records, no need to delete any records because they can only access their own personal account.
Anyone who tinkers with the app could easily open the tables directly and see all records, sensitives included, so that's not a good idea. Best solution is to split the db, remove or anonymise the sensitive data, and distribute the frontend with the templated backend.

@DakotaRidge, I kno you're going to distribute improvements to this app as time moves on, so spliting the app into front and backend is crucial. Otherwise, you would have to distribute accdb's that only contain the objects you have modified, and the users would have to import those modded objects into their accdb's in order to prevent their data from being overwritten by your updated version that has both the mods and your data within the same accdb. Make life easier for yourself and users by splitting the app.
 
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.
Yes, but you don't need new tables and forms to add extra pieces of information (data, datum) to a person record. You add new fields to the person record, and redesign the person form to include that field. In some cases you might decide to model it in a different way, where the person's attributes are stored in a person's attributes table, with each individual attribute in a different row.

Either way, you end up with 2 or 3 tables, not hundreds.

This second way is perhaps less intuitive, but you generally don't even need to change the database design to include new attributes with this model, and you never end up with too many columns.

You certainly don't need a new table for every person in any event.

If you have a table for every person, then no wonder you are struggling to delete the same piece of information for multiple persons.

It's also why we are all struggling to help, because the best advice is probably to start over. Sorry.
 
I took the liberty to ask ChatGPT to look at the following picture and redesign the schema to N3 with joins.

1754233207739.png


The redesigned database as follows

🔶 MAIN TABLES (Entities)​

1. FamilyMember

FieldTypeNotes
MemberIDAutoNumberPrimary Key
FirstNameShort Text
LastNameShort Text
DOBDate/Time
GenderShort TextOptional
RelationshipShort TextE.g., Spouse, Child, Parent

2. ExpenseCategory

FieldTypeNotes
CategoryIDAutoNumberPrimary Key
CategoryNameShort TextE.g., "Grocery", "Medical"

3. SubCategory

FieldTypeNotes
SubCategoryIDAutoNumberPrimary Key
CategoryIDNumberFK to ExpenseCategory.CategoryID
SubCategoryNameShort TextE.g., “CostCo”, “Physician Costs”

4. Expense

FieldTypeNotes
ExpenseIDAutoNumberPrimary Key
MemberIDNumberFK to FamilyMember.MemberID
SubCategoryIDNumberFK to SubCategory.SubCategoryID
ExpenseDateDate/Time
AmountCurrency
NotesMemoOptional

5. MedicalEvent

FieldTypeNotes
EventIDAutoNumberPrimary Key
MemberIDNumberFK to FamilyMember.MemberID
SubCategoryIDNumberFK to SubCategory.SubCategoryID
EventDateDate/Time
ProviderShort TextOptional hospital/clinic
NotesMemo

6. Medication

FieldTypeNotes
MedicationIDAutoNumberPrimary Key
MemberIDNumberFK to FamilyMember.MemberID
NameShort TextE.g., Guaifenesin
DoseShort TextOptional
StartDateDate/Time
EndDateDate/TimeOptional
OTCYes/NoOver-the-counter flag
PrescriptionYes/No
NotesMemo

7. FoodPreference

FieldTypeNotes
PreferenceIDAutoNumberPrimary Key
MemberIDNumberFK to FamilyMember.MemberID
FoodTypeShort TextE.g., “Dairy”, “Raw Food”
PreferenceShort Text“Avoid”, “Encouraged”, “Neutral”
ReasonShort TextOptional (“Kidney”, “Diabetes”)

8. Pet

FieldTypeNotes
PetIDAutoNumberPrimary Key
MemberIDNumberFK to FamilyMember.MemberID
PetNameShort Text
SpeciesShort TextDog, Cat, etc.
BreedShort TextOptional

9. PetCare


FieldTypeNotes
PetCareIDAutoNumberPrimary Key
PetIDNumberFK to Pet.PetID
DateDate/Time
CareTypeShort TextE.g., “Vet Visit”, “Grooming”
CostCurrency
NotesMemo
 
I took the liberty to ask ChatGPT to look at the following picture and redesign the schema to N3 with joins.

View attachment 120817

The redesigned database as follows

🔶 MAIN TABLES (Entities)​

1. FamilyMember

FieldTypeNotes
MemberIDAutoNumberPrimary Key
FirstNameShort Text
LastNameShort Text
DOBDate/Time
GenderShort TextOptional
RelationshipShort TextE.g., Spouse, Child, Parent

2. ExpenseCategory

FieldTypeNotes
CategoryIDAutoNumberPrimary Key
CategoryNameShort TextE.g., "Grocery", "Medical"

3. SubCategory

FieldTypeNotes
SubCategoryIDAutoNumberPrimary Key
CategoryIDNumberFK to ExpenseCategory.CategoryID
SubCategoryNameShort TextE.g., “CostCo”, “Physician Costs”

4. Expense

FieldTypeNotes
ExpenseIDAutoNumberPrimary Key
MemberIDNumberFK to FamilyMember.MemberID
SubCategoryIDNumberFK to SubCategory.SubCategoryID
ExpenseDateDate/Time
AmountCurrency
NotesMemoOptional

5. MedicalEvent

FieldTypeNotes
EventIDAutoNumberPrimary Key
MemberIDNumberFK to FamilyMember.MemberID
SubCategoryIDNumberFK to SubCategory.SubCategoryID
EventDateDate/Time
ProviderShort TextOptional hospital/clinic
NotesMemo

6. Medication

FieldTypeNotes
MedicationIDAutoNumberPrimary Key
MemberIDNumberFK to FamilyMember.MemberID
NameShort TextE.g., Guaifenesin
DoseShort TextOptional
StartDateDate/Time
EndDateDate/TimeOptional
OTCYes/NoOver-the-counter flag
PrescriptionYes/No
NotesMemo

7. FoodPreference

FieldTypeNotes
PreferenceIDAutoNumberPrimary Key
MemberIDNumberFK to FamilyMember.MemberID
FoodTypeShort TextE.g., “Dairy”, “Raw Food”
PreferenceShort Text“Avoid”, “Encouraged”, “Neutral”
ReasonShort TextOptional (“Kidney”, “Diabetes”)

8. Pet

FieldTypeNotes
PetIDAutoNumberPrimary Key
MemberIDNumberFK to FamilyMember.MemberID
PetNameShort Text
SpeciesShort TextDog, Cat, etc.
BreedShort TextOptional

9. PetCare


FieldTypeNotes
PetCareIDAutoNumberPrimary Key
PetIDNumberFK to Pet.PetID
DateDate/Time
CareTypeShort TextE.g., “Vet Visit”, “Grooming”
CostCurrency
NotesMemo
Wow!.. I didn't realize ChatGPT had that capability. That's powerful!
 
Yes, but you don't need new tables and forms to add extra pieces of information (data, datum) to a person record. You add new fields to the person record, and redesign the person form to include that field. In some cases you might decide to model it in a different way, where the person's attributes are stored in a person's attributes table, with each individual attribute in a different row.

Either way, you end up with 2 or 3 tables, not hundreds.

This second way is perhaps less intuitive, but you generally don't even need to change the database design to include new attributes with this model, and you never end up with too many columns.

You certainly don't need a new table for every person in any event.

If you have a table for every person, then no wonder you are struggling to delete the same piece of information for multiple persons.

It's also why we are all struggling to help, because the best advice is probably to start over. Sorry.
Thank Dave,
I don't have a different table for each family member. I am not sure why you think I do. And I do not have a different table for each grocery store, food recall, utility company, disease, medicine, and physician. There is a command button on one form for Walmart stores because most of my family frequently shop at a Walmart in Florida, Colorado, and California. The data comes from one table. There aren't different tables for Walmart, King Soopers, Publix, etc.

When you see frmMember 1, frmMember 2, and frmMember 3 in my screenshots, they are for different forms because they have different layouts designed for different user personalities. The forms have different graphics. Some Food Recall forms, for example, have maps of the US, while others will have pictures of squirrels and tigers or images of the solar system. Some forms will have icons indicating speech. Those command buttons read information aloud. Some forms have three speech icons because they read different information. This is true for food recalls. The data come from the same table, the messages are different.

Some users will want the minimum amount of information to be shown on a form, whereas others will want to see everything. So I may have four or five forms for ENFJs, INTPs, ENTPs, and ISTJs in my family. These are personality types defined by the Myers-Briggs Type Indicator (MBTI).
 
If you don't have certain tables, can you provide some clue as to what causes you to have hundreds and why they are growing. Do any of your tables have similar structures? Help us understand what you have created.
 
@DakotaRidge
Given your layout in #16, I presume you have a different form for each expense type listed. If you add more expense types, presumably you have to re-design this form and create more forms for the new expense types.

Really you need a single table of all the different expenditure classes, maybe grouped by a single lookup type as appropriate, so 2 tables. Conceivably you might need some other tables.

You can select the expenditure class you want with a combo box, or maybe a list box, and then you never have to redesign your database at all.

So your form would show a combo box to let you pick your expense class (the 20 or so black headers), and then drill down into the detailed expenses you want to consider within the selected header, by a combo box, or maybe a list box. So all of the detailed line items are hidden away until you need them, and you can add new line items by adding a new record to the expense category table, linked to the expense class. So a user could add an expense called "MRI scans" within the "Medical Bills" class, and you don't have to make any changes at all. The "MRI Scans£ category works like any other medical expense category.

Maybe some expense types need columns that are peculiar to those expenses, but that can be managed more easily than by proliferating tables and forms for each "special" category.

I don't use a personal expense tracker, but I'm sure the ones you can buy use a more generic and consistent approach that doesn't require them to release a new version every few weeks. Have you compared what you do with some of those.

For example, how on earth have you singled out "colonoscopy" as meriting special attention?
 
If you don't have certain tables, can you provide some clue as to what causes you to have hundreds and why they are growing. Do any of your tables have similar structures? Help us understand what you have created.
Hi Duane,

I have so many tables because my database does many, many things. It has two major domains: personal finance and health. Finance addresses income and expenditures. The expense domains include utilities, insurance, groceries, home repairs, etc. The grocery domain considers questions such as how much a family spends in a month at Walmart versus Safeway versus King Soopers? The Utilities domain considers the amount that a family spends on water, electricity, and gas. In addition to expenses, the utilities domain also considers the consumption. How many gallons of gas did the family use this month? How many kilowatts of electricity did the family use? For these, why did the amount change from month to another? Did the family water the lawn more in June than in December? Why? For phone costs, which service is more expensive? Is it T-Mobile or AT&T? Then should the family consolidate and use only one telco?

In the health domain, the database seeks to help family members understand the diseases that each person has. Do they eat bad foods regularly? What are bad foods according to dieticians? What do family members' medical test results show about their next medical issue? Will a person have kidney problems if he doesn't stop drinking alcohol? Which person needs to do more exercise? Most tables in the health domain have a field for each family member.

The database addresses each person's health separately. These are main form/subform constructs. The subforms are continuous forms. The subforms store data for each event/expense with a person's name, health issue(s), event dates, and cost to treat. Aggregate information will tell folks the frequency, total cost, and actions taken to prevent a problem from occurring again or worsening.

The database will track ER visits month by month. I have one family member who visits the ER every week. The staff has a room set aside for him. They probably have a GPS tracker in his car to tell them when he is on his way :rolleyes:

In recent weeks, food recalls have been front and center. What foods were recalled yesterday? Which recalls were Class 1? Where in the country was the product shipped? How many people died from the product? Sometimes I think that I should stop eating food:cry:

Because I initially built the database for personal finance, almost every table in the health domain has a field for cost. The database tracks the cost of doctors' visits, prescription meds, health equipment, and over-the-counter meds. It will aggregate medical costs over time, by doctor, and by pharmacist.

The database uses the five W's for everything. I add new fields to tables when I discover that I forgot a question to address. That happens from time to time because I am always reading books, magazine articles, or website posts about finance and health.

I hope this helps, Duane. As I mentioned before, I am not a financial consultant or a physician. I read a little, and then I build a capability into the database. I am sure it has lots of flaws.

I am also sure that no one has ever developed a database like mine. How could they? No one knows my family's medical history, where they live, and what they eat. They don't know which individuals are on medicare, and which ones are on a company health plan.

Most developers have built applications for use by extroverts, and some have built them for introverts. But I think that most developers do not consider personality when they create a database. For them, normalization and technical issues are most important. Being introduced to MBTI in the federal government, I consider personality first and foremost. I consider reports to be more important than forms. Reports are how I share information with others. I use forms to enter data in the system. The forms will show the person what data is missing so he/she can make a decision about what to do next. You won't see spreadsheet-like tables in my database. The forms show context data for each person, disease, store, expense, etc.

The medical history of my family goes back generations. Members live from Florida to California. Some members have strict diets and others eat every that they see. Some are Black, some are White, some are Hispanic, and some are Asian. There are men and women. All of these differences need to be built into this database.

Thanks for helping me think through all of this. I haven't created a navigation diagram yet, I am afraid that Access may not be able to show all the linkages. My copy of Visio is ancient.
 
@DakotaRidge
Given your layout in #16, I presume you have a different form for each expense type listed. If you add more expense types, presumably you have to re-design this form and create more forms for the new expense types.

Really you need a single table of all the different expenditure classes, maybe grouped by a single lookup type as appropriate, so 2 tables. Conceivably you might need some other tables.

You can select the expenditure class you want with a combo box, or maybe a list box, and then you never have to redesign your database at all.

So your form would show a combo box to let you pick your expense class (the 20 or so black headers), and then drill down into the detailed expenses you want to consider within the selected header, by a combo box, or maybe a list box. So all of the detailed line items are hidden away until you need them, and you can add new line items by adding a new record to the expense category table, linked to the expense class. So a user could add an expense called "MRI scans" within the "Medical Bills" class, and you don't have to make any changes at all. The "MRI Scans£ category works like any other medical expense category.

Maybe some expense types need columns that are peculiar to those expenses, but that can be managed more easily than by proliferating tables and forms for each "special" category.

I don't use a personal expense tracker, but I'm sure the ones you can buy use a more generic and consistent approach that doesn't require them to release a new version every few weeks. Have you compared what you do with some of those.

For example, how on earth have you singled out "colonoscopy" as meriting special attention?
Dave, I would say that that is true. The database already has several navigation forms. I call one the Master, but it is several months old. I have run out of space to put more buttons on it. I am waiting for large monitor support.

One navigation form is for Finance, and one is for Health. One is a mix of Finance and Health. I am thinking about creating one for Food. When a person opens the database, he/she will tell it what is of most interest at the time.

Currently, I do not have subdomains for airline flights or cat care. I would have to think hard about what data to have in the Airline domain because I haven't been on a plane since Covid hit. The fields would be for baggage costs, carry-on bags, routes to and from the airport, toll road expenses, meals on board, rental car at destination, where to stay, and other things. None of these fields are currently in the database.

For the cat care domain, the database would store the names of each cat, their ages, genders, veterinarian's name, vet costs, food likes and dislikes, and their medications. Some of those fields could be the same as in the dog care tables/forms/reports. For each animal, all I would need to do is assign it a species. However, I would probably need special fields for cats versus dogs. I would do that because I like to keep data separate.

My forms would also need to aggregate data for dog meds, foods, and vets. I would have similar forms for cat care with aggregated data for them. I do aggregations in forms and reports, not in queries. I want the calculations to be simple so my users can change them themselves. I will teach folks about the sum, maximum, minimum, and count functions. That's all they need to know. They don't need to know about SQL or about opening a query wizard. They will never need to leave the form.

The diseases that cats suffer from may be different from the ones that dogs have, so I don't want a master list with pet diseases. I could use MVFs for animal diseases.

The other thing about my navigation map is that it has one button for each operation/task. I don't want my family members having to decide which buttons to click if they do not relate to them. So I may have ten Master navigation forms in the database.

I call the form in #16 my master navigation map. Each version of the database will have a Master, so I plan to work with each family member to understand what he/she wants to do with the database. I will then remove buttons from the Master and create forms for each person. For example, if a person suffers from diabetes but not have cancer, he won't see buttons for cancer in his navigation form. The diabetes buttons will open forms that that person needs to manage their health. My Master form will be in the .Accdb file but it won't be front and center. There are too many buttons on it.

If a person comes down with a new disease, all he will need to do is copy buttons about that disease to his master navigation form. Again, I want family members to use simple portals to get to where they need to go.

The database will open to the Master navigation form. That form can open secondary navigation forms, I haven't designed them yet.

Here's a quick-and-dirty chart that shows some of the linkages between subdomains. The chart does not show all the subdomains. Forms will open related reports. Each form and report will read aloud information about that domain. Images on forms will also be specific to that domain.

Thanks again. This information will help me develop my User Manual.

1754257106248.png
 

Users who are viewing this thread

Back
Top Bottom