Clear out data (1 Viewer)

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

 

[Pat Hartman]

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.

Then maybe if you are developing an app that other people will use, you might want to create something the ordinary people will understand. If you were developing only for yourself, it wouldn't much matter how the interface worked but if you mystify the target audience, they won't use the app.

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

We start by normalizing the schema. Then we make whatever forms we need to update the schema and reports to turn details into data. Usually you will end up with only a single maintenance form per table but it really depends on the application. When you end up with multiple forms to update a single table, you need to do the common validation in a standard module that you call from the BeforeUpdate event of each update form so that you never duplicate validation logic.

es, I could have done more with normalization, but I discovered a new disease every week.

That should not have altered the schema

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.

Do you think Walmart has a separate form for every vendor it deals with? No, they don't. Somehow, normalization works for them too.

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.

I can't. Professional applications don't work that way.

I am developing the application using an Agile development process.

Agile does not mean chaotic nor does it mean with sloppy practices. You've named at least three unrelated sub-applications. Putting them in the same database does nothing for you. Developing them all at the same time simply creates chaos. Work on one section at a time so you can focus and your users can also.

I'm sure you apply discipline to research in your own field. Software development also requires discipline. The more discipline you can muster, the less rework you have. Experience also comes into play. I have a much better feel going into a new development project for the kinds of things that get changed as the development unfolds because I've been doing this for 50 years. Therefore I am less likely to make a wrong decision with normalization or program flow. I also have enough general business experience as well as far reaching interests so I can ask intelligent questions as the users are describing their business needs.

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.

It isn't a benefit, it is poor practice. Whenever you update a record, if you happen to make it longer, it may get moved to the end of the data area if there isn't room to rewrite the record in the current block. It's the same concept of how windows files get fragmented. C&R puts everything back in order and updates the statistics which ultimately makes queries more efficient.

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.

That's because the schema is not as unnormalized as your Menu form makes it look. Apparently you have some way other than updating the Menu form to get to new stores.

Two certain signs of improper normalization.
1. Field/Object names that include data. So having a menu item named "Walmart" should make bells go off in your brain.
2. Field/Object names with numeric suffixes or string prefixes indicating multiple instances of the same type of item.

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.

Without looking at the database, I can't categorize your mistakes. I can only generalize based on the mistakes other new developers who have no understanding of normalization make. In general, your mistakes are caused by not normalizing the tables, by not using parameter driven queries to select data, and by not using VBA effectively to control form behavior. You are doing way too much work duplicating and modifying objects rather than learning how objects can be reused.

The grocery forms are one example of making unnecessary objects, each form is hard coded for a specific store rather than using one form that displays data for any grocery store and using combos to define the options for each store.

 

[Pat Hartman]

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.

Whether you split the database into multiple applications is not relevant. If your user community prefers one interface to get to all the functions, leaving the FE (front -end) with all the mini-apps is fine. The advice about splitting is to remove the tables and put them into a separate database. Then you have the FE - front end which holds all the GUI objects and the BE - back end which holds ONLY the tables and relationships. That way, when you issue updates, you can replace the FE without affecting any of the user data. However, since you are using the chaos method of development, probably every new version would also require table changes and that becomes much more difficult to handle.

I have an application which is sold to the public. That means that I don't actually install updates for the users so in order to make it possible for them to do it, I have to create unique update databases that use DDL and DAO to convert ver1 to ver2 without disrupting existing data. this is the way apps like Quickbooks work. When you install a new version of the software, they replace the interface software and then update your database to add/change tables without disturbing your existing data.

If I were to be called in to take over maintenance of your application, I would almost certainly start by removing 90% or more of the objects and consolidating the data into properly normalized tables.

Professional developer is not your occupation and since you are not giving up your day job to become a programmer, there is little we can do to help you. All advice seems to fall on deaf ears. You don't seem to have any interest in whittling 4000 tables down to 40 with a corresponding reduction in forms/reports/queries so, we'll just answer questions and help you muddle through.

The one suggestion I hope you will consider is to stop branching out to new areas. Concentrate on one aspect of the project and stabilize it before moving on to a different aspect.

Another suggestion is to have a solid reason for adding columns to tables. Why you care which stores have strikes is beyond me. What does the application use that information for?

 

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

 

[BlueSpruce]

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.

If your data is properly normalized, then removing the sensitive info can be as simple as deleting the master record of the person with the sensitive info, and then all that person's sensitive data will be automatically deleted in the related child tables via cascade delete. You will also significantly reduce the number of forms, reports and queries. Are you using MVF's in this application?

 

[The_Doc_Man]

Are you using MVF's in this application?

He mentioned that usage in passing in one of the long-winded discussions.

 

[BlueSpruce]

He mentioned that usage in passing in one of the long-winded discussions.

Thanks, I must've overlooked it in this long thread. Virtually all Access developers avoid using MVF's because the values are stored in denormalized hidden tables and querying them is cumbersome. Can multiselect list boxes be used and have each selected value stored in normalized tables?

 

[June7]

Could but don't expect OP to be open to idea. Doesn't like to use VBA and totally rejects all advice for normalization. Lost cause here.

 

[MajP]

Virtually all Access developers avoid using MVF's because the values are stored in denormalized hidden tables and querying them is cumbersome.

Although lots of people avoid mvf for many reasons, the hidden table is properly normalized. It is either a junction table contraining the foreign key to the main table and a foreign key to a lookup table. Or it is a child table with the FK to the main table and the values.