UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> New Records Inheriting Some Multivalued Field Values Of Deleted Records, Access 2010    
 
   
wolfe
post Sep 13 2018, 08:39 AM
Post#1



Posts: 21
Joined: 14-February 18



After deleting all records from the 'test' version of my database (with cascading delete relationships between different tables), I am now entering fresh data 'for real'.

However, in some - but not all - multivalued fields the new records seem initially to be inheriting some of the deleted records' selections, perhaps following the pattern in which they were entered (with different selections or blanks for different records) although I'm not entirely sure about that. The pattern in exactly which selections are inherited also seems somewhat inconsistent.

The relevant default values are all blank, and I'm fairly sure that these particular multivalued field values are not being auto-filled by any VBA code.

Please let me know if you have any ideas for stopping these 'ghost' selections from appearing! I wonder if one possibility might be to actively clear all relevant fields whenever a new record is first added, but that could be quite fiddly to implement for all relevant tables and fields. Hopefully there might be a simpler solution?

I'm aware that most experts are cautious about using multivalued fields, but I used lots of multivalued fields in my database because it seemed the most efficient way to set up the large and complex overall database structure (including many categorical variables with the potential for multiple selections) under lots of time pressure, having eventually developed most of the code I thought I needed to manipulate multivalued fields where necessary - but I hadn't anticipated having this problem!
This post has been edited by wolfe: Sep 13 2018, 08:40 AM
Go to the top of the page
 
GroverParkGeorge
post Sep 13 2018, 08:46 AM
Post#2


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


One of the reasons this particular problem is rare is that, yes, indeed, most experienced database developers do avoid things like multi-valued fields. So there's that.
You are saying that after deleting all records in a table which contains one or more multi-valued fields, "some" of those multi-valued fields still have non-null, non-blank values in them?
How did you go about deleting all records?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
wolfe
post Sep 13 2018, 08:57 AM
Post#3



Posts: 21
Joined: 14-February 18



Yes that is the problem - although the fields are all null/blank immediately after the deletion, only for "some" (I haven't yet been able to pin down exactly which of the multivalued fields and values are affected) of the values to reappear when new records are added.

I deleted all records by selecting all records in the table with the top-level 'project' records, right-clicking the record selector and clicking 'Delete Record'. This then cascade deleted all of the related records from other tables, including the tables where I've noticed this problem occurring.
Go to the top of the page
 
GroverParkGeorge
post Sep 13 2018, 09:04 AM
Post#4


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


Hm. You did NOT explicitly delete all records from all tables individually then?
I would do that.

I'll bet you're seeing the result of having "orphaned" records with those multi-value fields not be touched by the cascade delete.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
GroverParkGeorge
post Sep 13 2018, 09:14 AM
Post#5


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


I made the previous observation based on the fact that some of the multi-value fields' records are deleted, while others are not. That suggests those are not related to a parent record.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
wolfe
post Sep 13 2018, 09:23 AM
Post#6



Posts: 21
Joined: 14-February 18



Thanks, but I'm not sure they are 'orphaned records' as such because the table itself is empty after the deletion, and then the values are reappearing for the newly-added record's multivalued field (rather than their original record for which they were first selected)?

In other words, the relevant tables do appear to be fully empty after the deletion, and that's in the table itself rather than e.g. a filtered query of the table.
Go to the top of the page
 
GroverParkGeorge
post Sep 13 2018, 09:27 AM
Post#7


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


I see.
So, you performed the deletion and now there are no records in the table.
You add a new record and the multi-valued fields "magically" now have values in them? Again, because so few people use these things, it's not as well-documented. Can you provide a sample of the accdb in question to experiment with?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
wolfe
post Sep 13 2018, 09:44 AM
Post#8



Posts: 21
Joined: 14-February 18



I think that's the situation, yes. I'll provide a sample of the database in a few hours when I can strip it back appropriately! Thanks for your help
Go to the top of the page
 
wolfe
post Sep 13 2018, 09:36 PM
Post#9



Posts: 21
Joined: 14-February 18



I've done that stripping back now, but the much-simplified sample databases are both still over the 2MB upload limit here - I can send you them separately if you let me know how.

Of the sample databases, 'BlankVersionGhostData' is affected by this problem. To see it:
1) Look at the empty tables 'tbl4aEducation' and 'tblOtherKeyHHLevelData'.
2) Add a record to 'tblProjectDetails'.
3) Add a record to 'tblInterviewRoundDetails' with the same 'ProjectIDcode' as you added in step 2.
4) In 'tblOpenedData', add the same 'ProjectIDcode' to the 'OpenedProject' field and the same 'InterviewRound' to the 'OpenedInterviewRound' field.
5) In 'frm1aHHInterviewBasicDetails', enter a household code and an interview start date.
6) Look at 'tblOtherKeyHHLevelData': the first three fields should have all been added by the 'HHIDcode_AfterUpdate' VBA procedure as intended, but also at some point multiple selections will have been made incorrectly for the 'RelationshipsBtwnHHFamilyUnitsPastYear' and 'RelationshipsBtwnHHFamilyUnitsPastMonth' fields.
7) In 'frm2aHHMembers', add details of five or six different people.
8) Look at 'tbl4aEducation': the first four fields should all have been added by the 'HHMemberName_AfterUpdate' VBA procedure as intended, but also at some point multiple selections will have been made incorrectly for the 'DifferentEducationStatusesPastMonth' and 'DifferentEducationStatusesPastYear' fields.

'TestDataVersionForGhostData' is the equivalent (simplified) version of the original test database, before the test data was deleted. However, now when I delete all records in 'tblProjectDetails' and then follow the same steps as above, I can't replicate the problem from this database...

I wonder whether perhaps this ghost data is coming from part of the more hidden data represented in each database's overall file size, which often seems to shrink (apparently without any more obvious consequences) if you do a 'save as' and use a different filename or location? I think that some of the fields which previously contained ghost data like this in multivalued fields might have lost their ghost values while I was trying to test this issue using different versions of the file.
This post has been edited by wolfe: Sep 13 2018, 09:42 PM
Go to the top of the page
 
GroverParkGeorge
post Sep 14 2018, 08:09 AM
Post#10


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


Okay, if the tables are now empty, and if you've done a Compact & Repair, that should definitely bring the size down to the point where you can compress it into a ZIP file under 2MB. I don't need the interface, of course.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
wolfe
post Sep 15 2018, 07:24 AM
Post#11



Posts: 21
Joined: 14-February 18



Great, yes they're now small enough to upload here.

You don't need much of the interface, no - the same problem does occur if you enter the data more manually in the tables (so I don't think the forms' VBA code is contributing at all to the issue), but the forms make it slightly quicker...
This post has been edited by wolfe: Sep 15 2018, 07:24 AM
Attached File(s)
Attached File  GhostDataDbs.zip ( 162K )Number of downloads: 2
 
Go to the top of the page
 
GroverParkGeorge
post Sep 15 2018, 10:09 AM
Post#12


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, you've fallen into one of the many traps laid by lookup fields. This is, in fact, one of the main reasons I personally dislike lookup fields in general, and one of the main reasons seasoned developers avoid them as much as possible.

Your "lookups" (or at least the ones I've looked at so far) store the actual value fields from the related tables, NOT the primary keys, and that is an unwieldy design. In order to work on this, I'd start by correcting that error. Can't even get to the part where the "ghost" values seem to be added to new records until then.
I'll followup later.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
GroverParkGeorge
post Sep 15 2018, 10:48 AM
Post#13


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


Although using composite keys (i.e. two or more fields in a table jointly defined a primary key), it's probably one of the more difficult ways to manage Primary Keys. It results, as in this database, in tables with as many as four fields being REQUIRED for a relationship. Again, not logically incorrect, but certainly less practical than using the AutoNumber field for that purpose. This one is more of a matter of efficiency, not design. In other words, whenever you have to join tbl2HHMembers in a query with tbl4aEducation, all four of those fields have to be dragged along. There's an additional twist here, too, because ONE of those key fields is called "HHMemberName" and it presumably contains a person's first name? You are probably "safe" to assume that two members of the same household will not have the same first name, unless they happen to be parent and child. As a matter of fact, Sr. and Jr. are used precisely for that reason, are they not? In that case, you'd have to ADD DateofBirth to the composite key to get a unique identifier. See where this goes? Now FIVE fields are required to define the relationship between HH Members and Eduction.

Also, I see a field called "AutonumberForSorting" in the tblEducationValuesEachFieldIndependent table. Sorting is NOT what autonumbers are for. Relying on them for that purpose is fraught with danger. That same table has NO primary key defined. I'm going to suggest that you address this problem, as well. Add a field called SortOrder and use IT to determine the sort order of records in queries when needed. Make this AutoNumber the primary Key for the table. Use IT, not the IndEducationFTPT field, as the foreign key in related tables. These changes will help bring clarity and discipline to the database.

Another table with no primary key is called tbl4AEducation.

I could review all of the tables and the issues I see in them, but it begins to be the case that I'm being unnecessarily negative. Let's just say that we can probably make many things work better by going back to some fundamental database design considerations.
BTW: I'm tempted to suspect that these so-called "ghost values" that you are seeing are, in fact, the result of having to drag along multiple Key fields when adding related records. Does that account for what you are seeing, perhaps?
If you haven't already done so, now would be a good time to invest in studying our newcomer's reading list

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
wolfe
post Sep 18 2018, 10:43 AM
Post#14



Posts: 21
Joined: 14-February 18



Many thanks for your advice - much appreciated.

I realise that some important aspects of my database structure aren't ideal. However, other than this particular issue, all the main things do seem to 'work', and I have reams and reams of query structures, forms and VBA code which are built on the current structure. Significant structural changes would take a very long time to implement, and unfortunately I don't have this time now after many months of working on the database (I've already gone way, way over the time I could afford to spend on database development before deadlines for data collection and analysis).*

I hope this doesn't sound like I'm ungrateful or unwilling to learn though - and I definitely do need to solve this issue, as these 'ghost values' could have quite serious implications for data quality!

I have now tried to implement your suggested improvements to the primary keys and 'lookups' in this much-simplified and stripped-back version of my database, but they don't seem to have fixed the problem (at least for the 'ghost values' which are already in the database). Below are some explanations of why I structured the database the way I did, and revised steps for testing the issue with the updated database.

Primary keys
Other than a basic introductory course I've had to learn most things along the way, and when developing my composite primary keys I was (perhaps wrongly) wary of autonumbers in case they caused problems when trying to combine offline data from multiple copies of the database. For the purposes of this database it can be ensured that each 'HHMemberName' within a household is unique. And I can't remember exactly why I didn't set up any primary keys in tbl4aEducation, but I think it was probably because all of the records should be added automatically through a form for the prior tbl2aHHMembers.

Autonumbers and 'lookups'
The reason why I called the autonumber fields 'AutonumberForSorting' in tables like tblEducationValuesEachFieldIndependent was because so far I've used them mainly to avoid problems from occasional unintended sorting of the table by other fields while I was manually pasting in the values (I don't envisage further sorting being needed for these tables). I could make these autonumbers the primary keys for their respective tables, yes. I'm not sure these lookup fields are really true 'look-ups' though(?), in the sense that they're not actually used to 'look up' other values from e.g. tblEducationValuesEachFieldIndependent - they're only providing the category values for categorical variables.

If you were going to fix these 'lookups' in the way you suggest, would you move the relevant autonumber to column 1 and make it 'shown' (with e.g. 'EducationType' in column 2), then set the column count to 2 and keep the bound column as 1? A practical issue with this would be that having the autonumber primary key (rather than the categories) stored in the table would make it more difficult for interviewers to quickly see the category indicated in each field (without me adding an extra form structure on top of each relevant field, which again unfortunately I don't have time to do).

Testing this problem with improved primary keys and 'lookups'
The attached databases have single-field primary keys (V2) and also 'lookups' storing primary keys (V3), but the previous 'ghost data' is still coming back at the same points:
1) Look at the empty tables 'tbl4aEducation' and 'tblOtherKeyHHLevelData'.
2) Add a record to 'tblProjectDetails'.
3) Add a record to 'tblInterviewRoundDetails' with the same 'ProjectIDcode' as you added in step 2.
4) Add a record to 'tbl1aHHInterviewBasicDetails' with a household code, an interview start date, and the 'InterviewRoundID' autonumber from step 3.
5) Add a record to 'tblOtherKeyHHLevelData' with the 'HHInterviewID' autonumber from step 4. Immediately after you add this record, multiple selections will have been made incorrectly for the 'RelationshipsBtwnHHFamilyUnitsPastYear' and 'RelationshipsBtwnHHFamilyUnitsPastMonth' fields.
6) Add five or six different records to 'tbl2HHMembers', again with the 'HHInterviewID' autonumber from step 4.
7) Add those five or six different records to 'tbl4aEducation', with the 'HHMemberID' autonumbers from step 6. Immediately after you add records 3 and 4, multiple selections will have been made incorrectly for the 'DifferentEducationStatusesPastMonth' and 'DifferentEducationStatusesPastYear' fields.



* As well as this other bug potentially adding further complications to the process of making significant changes to table relationships at this point: lots of important tables have become inaccessible in the 'Relationships' window, and I have to actively 'show' again each relevant table that had disappeared from that window before I can delete the relationships or make any changes to primary keys...

Attached File(s)
Attached File  BlankVersionGhostDataV2andV3.zip ( 114.45K )Number of downloads: 0
 
Go to the top of the page
 
GroverParkGeorge
post Sep 18 2018, 01:04 PM
Post#15


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


"...wary of autonumbers in case they caused problems when trying to combine offline data from multiple copies of the database."

That's a valid concern, if you are importing data from multiple copies of an accdb. There are several caveats to that, though.
First, WHY would you need to do that? In a typical scenario, there is a single back end accdb in a shared folder on the network. All users have their own copy of the front end accdb which all link to that shared be.
There are situations where you can't do that, such as having two or more users in totally disconnected remote locations. Perhaps an office in one city and another office in a city hundreds of miles away. Not common, but possible.
If that scenario does apply, of course, there are other ways to deploy the (properly designed) shared back end. The most frequent implementation is a server-based RDBMS, such as MySQL or SQL Server. Even SharePoint lists would be a step up in effectiveness, for three reasons. First, SharePoint lists can be linked externally to an accdb to support distributed use. Second, SharePoint lists can be used off-line, and, third, the re synchronization process in SP is quite robust.

While the composite primary key approach does work, and is probably the preferred approach in some situations, it does lead, as I suggested, to more cumbersome queries, etc.

As you may or may not be aware, AutoNumbers are guranteed only to be unique, not that they will be sequential. Ordering for the purposes of a sort is a process that requires both.

In addition, using Lookup fields in tables is widely understood to be a less-than-optimal strategy. You have "improved" them, but if they are still there, they remain a potential risk.

Nearly all tables, regardless of how you add records to them, need primary keys. That's a fundamentally important practice. I'm saying "nearly all", rather than making it an absolute because, well, I never like to say "never" or "always". In any table recording data, as opposed to lookup tables, I would edge much closer to making it absolute.

Whatever is going on here is made that much more complicated, I'm afraid, by the complexity of a) multi-value fields and b) composite primary keys.
That's really the bottom line. BTW, here's a link to a recent blog article that I wrote in a light hearted way to illustrate how I think about such things. Making things MORE complicated seldom leads to trouble-free applications.

Using your reproduction steps, I hope someone can persist in tracing through the process to see if the source of the problem can be identified.
This post has been edited by GroverParkGeorge: Sep 18 2018, 01:06 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
wolfe
post Sep 18 2018, 03:22 PM
Post#16



Posts: 21
Joined: 14-February 18



"Having two or more users in totally disconnected remote locations" is exactly the type of situation I think I have to prepare for with my database. I'll check all my data-recording tables and that the ones currently without any primary keys still work with them!

That's really useful to know about those other options for deploying a shared back-end in this situation, but they'll probably have to wait until a future iteration of the database.

Where possible, would you suggest using value lists rather than lookup fields for categorical variables' pre-loaded category values?

And unless/until someone can find the source of this problem, do you think a pragmatic solution would be to wipe clear all non-primary key fields whenever a new record is added? For the full version of the database I already have some 'AfterUpdate' VBA code linked to these (N.B. not causing this problem!) which could potentially include this step.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 06:18 AM