Clear out data (1 Viewer)

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

 

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

 

[Gasman]

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.

View attachment 120701View attachment 120702View attachment 120703

TBH, I would be asking why you are supplying something that whilst can be selected, should not even be shown?

I remember seeing a program where one had to select a town in England, yet the combo showed towns in Scotland and Wales.
If you selected one of those, then you got a similar message, yet a simple filter for the country would have prevented that.

When I mentioned it, I was told 'Well yes, in a perfect world, that would be nice'
One simple filter, 5 seconds to implement, 20 secs to test.

 

[cheekybuddha]

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.

Welcome to AWF

 

[The_Doc_Man]

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.

Can't speak for others, but this is how I see it.

When you are in a classroom and you ask a question or make a comment that is based on an erroneous assumption, don't you want the teacher to correct you? And in that public situation, should the teacher allow the others who heard the bad question be left unclear about the error?

Are we going to worry so much about the erroneous student's feelings as to be unable to correct him/her AND correct the impressions taken by others who witnessed the exchange? If we cannot offer correction in a public forum, what is the point of the forum existing, much less being public?

 

[GPGeorge]

There is a difference between correction and public humiliation, though, is there not?

 

[BlueSpruce]

TBH, I would be asking why you are supplying something that whilst can be selected, should not even be shown?

Because there's historical data that has those deprecated values and users need to know they can no longer select them.

I remember seeing a program where one had to select a town in England, yet the combo showed towns in Scotland and Wales.
If you selected one of those, then you got a similar message, yet a simple filter for the country would have prevented that.

As much as many Scots would love for Scotland to be an independent sovereign nation, it's part of the United Kingdom. If you were to setup cascading ComboBoxes and add Scotland as a separate Country, then you can filter Counties that belong to Scotland, and towns that belong to those Scotish Counties. You could also create a Possession lookup table and put Scotland, Northern Ireland, and other British Possessions in it.