Clear out data (1 Viewer)

[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. Lost cause here.
MVFs are essentially a normalized structure, just hidden. Beyond that, OP has rejected advice to normalize schema. See post 9.

 

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

 

[BlueSpruce]

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.

Please explain how those hidden tables can be adequately queried to read, update, or delete values. It's my understanding that in order to retrieve one or more elements stored in arrays you have to use non_standard .value subscripted queries.

 

[MajP]

Please explain how those hidden tables can be adequately queried to read, update, or delete values. It's my understanding that in order to retrieve one or more elements stored in arrays you have to use non_standard .value subscripted queries.

@isladogs has a good explanation

Multivalued Fields

Multivalued fields (MVFs) were introduced with Access 2007. This article explains what MVFs are and how they can be used. However, most experienced developers avoid them completely. This article explains why this feature is really not a good idea

isladogs.co.uk

Also this demo shows a lot of functions that can be used on a MVF.
This demo uses three MVF to query three MVFs.

At the top you can use the MVFs for Region, Work Category, and Work Type. This will filter the corresponding records in the Company table. You will see additional functions to write and delete from these lists.

I am not saying I use these things, I am just trying to describe the reality of what they are and how they work.

 

[GPGeorge]

I think an essential point to understanding the problem that is MVFs would be that they are seductive to new, inexperienced people, who are more likely to abuse and misuse them. At the same time, experienced, knowledgeable developers, who do understand the inner workings of MVFs, tend to be less motivated to use them, precisely because of that knowledge.

 

[LarryE]

I think an essential point to understanding the problem that is MVFs would be that they are seductive to new, inexperienced people, who are more likely to abuse and misuse them. At the same time, experienced, knowledgeable developers, who do understand the inner workings of MVFs, tend to be less motivated to use them, precisely because of that knowledge.

I am just glad the OP is only using ACCESS for his own purpose and not an architect designing buildings or bridges.

 

[Uncle Gizmo]

This is an excellent video on Multi Value Fields from >>> Takeshi K --- demonstrating lots of ways you can programmically interact with multi-value Fields --- for more information see here :-

Multi Value Fields Links​

Multi Value Fields Links - Nifty Access

Multi Value Fields Links Consider a scene at a Microsoft office where an innovative individual, perhaps from the marketing team, suggests, "MS Access requires 'Multivalued Fields' 💡 - our aim is to simplify the usage of MS Access for individuals!" Envision the developers emphatically voicing...

niftyaccess.com

 

[Mark_]

@MajP, but that intersection works just perfectly when you realize that each line is intended for a different type of traffic! One for blue traffic, one for yellow, one for red, one for green, and an extra to cover non-standard traffic that doesn't adhere to the standards! Also has a bypass for overly slow or quick traffic, just to be safe!

 

[Pat Hartman]

@Mark_ and @DakotaRidge If you have databases with lots of lookups, this mini-app may help you. I actually conceived the idea and built the first version of it 40 years ago using COBOL and DB2. It's gone through several other platforms in the intervening years and undergone minor changes but it is still alive and well today in Access and whatever BE my FE is linked to so the BE today is DB2, SQL Server, ACE, and a couple more options and thanks to the flexibility of Access, the BE is pretty much irrelevant. You just relink the tables.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

As others have pointed out, MVF's are actually properly normalized (so each one adds a separate hidden table to your schema) although you need specialized, non-standard SQL to work with them. Experts avoid MVP's due to the baggage and because they know how to build the lookup table themselves and they know that MVP's create more issues than they solve. One for starters is that in the real world, you sometimes have to retire old values. You need to have them in the list to support existing records but you don't want existing or new records to be changed to the discontinued value SO, you need an additional column in the table to indicate which items are archived and the mini-app solves that problem. It also has simple security so you can limit the users who are authorized to add/change lookup values. As you can see by the example, I create a query for each lookup and that is what the app uses to select the RowSource for a combo or listbox.

 

[BlueSpruce]

I am just glad the OP is only using ACCESS for his own purpose and not an architect designing buildings or bridges.

The OP clearly stated he is not a developer so I feel it's inappropriate to humiliate him, or any other AWF member who is not an experienced developer.

 

[BlueSpruce]

...in the real world, you sometimes have to retire old values. You need to have them in the list to support existing records but you don't want existing or new records to be changed to the discontinued value SO, you need an additional column in the table to indicate which items are archived and the mini-app solves that problem. It also has simple security so you can limit the users who are authorized to add/change lookup values...

Some of my lookup tables have logically "Deleted" and "UserAdded" boolean columns. If a value has a Deleted checkmark, the value will still appear on the list, but an X will appear in the 2nd column of the dropdown list and users won't be able to select it. In certain comboboxes I allow users to dynamically add new values on the fly when users enter values not in list. However, a msgbox popup warns users to verify if any values already on the list should be used instead of adding the new value.

 

[LarryE]

The OP clearly stated he is not a developer so I feel it's inappropriate to humiliate him, or any other AWF member who is not an experienced developer.

Thousands of non-developers and beginning ACCESS users have come into this forum and taken the advice given to them. They wished to design their projects properly. This OP clearly stated he knows he is designing his project improperly; he wants to design it wrong, and several people have given advice as to how to do it correctly. He refuses to take the advice given and states he wants thousands of tables and forms and doesn't understand what is wrong with that. No one is humiliating him. He did that to himself. He does not want to use the product in the manner that it was designed to be used and refused help. I do not have any sympathy. And I still don't want him designing anything that matters if he is going to do it backwards.

 

[Gasman]

The OP clearly stated he is not a developer

Not anymore perhaps, but he used to continually boast of his main DB he created (that had even more objects) that dealt with I cannot remember what now, such is my memory, but involved million dollar projects.
This DB he mentions here started off as a finance DB like Quicken, and evolved to encompass medical data.