Adding a 'dummy' field to an historic database and updating it (1 Viewer)

xBirdman

Registered User.
Local time
Today, 14:24
Joined
Oct 14, 2019
Messages
38
Sorry for the long post, but I preferred to try and give enough info to try to head off obvious questions.

I have a data table that should be at least two tables, but due to the history and limited future (~2 years) of the database it isn't worth a full overhaul. The table contains Cases (e.g., 19001, 19002, 19003) and within those cases, separate incidents (e.g., 19001A, 19001B, 19002A, 19003A, 19003B, 19003C). As shown, there’s no consistency to the number of incidents within a case and each record has both a [caseNumber] and [incidentNumber] field.

The “case level” information is usually consistent across all incidents, thus, if we get relevant information about incident ‘A’ and fill that in, it applies to all related incidents. I have built a form that allows the user to pull incident ‘A’ and update it, then run a background query that updates all the other incidents for that case. Sometimes, one or more ‘case level’ fields are different for a specific incident (e.g. an address) so I’ve built an incident level form that allows the user to open a specific incident and make updates. If they update any case-level information (any one of about 12-15 fields) it automatically sets a yes/no field in the case table to True, so that when the query above runs in the future, these protected records will not be overwritten with Incident A information.

All of that works like a dream. I now need to update the yes/no field for all historically entered data so that these are protected from being overwritten when those cases are pulled up. The basic logic is:

Compare each case-level field for each non-‘A’ record to the corresponding ‘A’ record field. If any of those fields differ, set Case.different = TRUE, ELSE case.different = FALSE.


I can pull the relevant fields from the database, and either via serious convolutions in Excel, or pulling it into program R I can create a loop to set the value for the differences field and then link the table back to the database and run an update query on the existing data.

Or maybe someone here has a smarter, easier, or more appropriate suggestion?

An example, simplified data record would include fields:
[caseNum], [incidentNum], [cf1], [cf2], [cf3], [cf4], and [differences] where cf* are the case-level fields.

Any case ‘A’ record need not be set ‘TRUE’ because that is the root record against which the others are compared. I note that I can set all historic records to FALSE and then just run the update to catch the changes to TRUE if that helps.
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:24
Joined
Jan 23, 2006
Messages
15,364
In general terms (make something up if that's easier) what is a Case? What is an incident?
Can a Case have 0,1 or possibly many Incidents?
Forms are not the big issue in my view, it's what are the subjects involved and how are they related.
Good luck with your project.
 

xBirdman

Registered User.
Local time
Today, 14:24
Joined
Oct 14, 2019
Messages
38
A case will always, by default, have an 'A' incident. It may then have 1-many more incidents that are ultimately wrapped in the same case, thus Inc.A is first, and sequentially Inc.B, Inc.C, etc. for as many incidents as are warranted. A good analogy is a court case. There are can be multiple incidents (charges) and each incident has its own outcome. So one could be found guilty of 2 charges and innocent of 2 others, and all four make up the case.

I agree the forms aren't the big issue, just explain those so my need for the dummy variable makes more sense (I hope).
 

vba_php

Forum Troll
Local time
Today, 16:24
Joined
Oct 6, 2019
Messages
2,884
I have a data table that should be at least two tables, but due to the history and limited future (~2 years) of the database it isn't worth a full overhaul. The table contains Cases (e.g., 19001, 19002, 19003) and within those cases, separate incidents (e.g., 19001A, 19001B, 19002A, 19003A, 19003B, 19003C). As shown, there’s no consistency to the number of incidents within a case and each record has both a [caseNumber] and [incidentNumber] field.

The “case level” information is usually consistent across all incidents, thus, if we get relevant information about incident ‘A’ and fill that in, it applies to all related incidents. I have built a form that allows the user to pull incident ‘A’ and update it, then run a background query that updates all the other incidents for that case. Sometimes, one or more ‘case level’ fields are different for a specific incident (e.g. an address) so I’ve built an incident level form that allows the user to open a specific incident and make updates. If they update any case-level information (any one of about 12-15 fields) it automatically sets a yes/no field in the case table to True, so that when the query above runs in the future, these protected records will not be overwritten with Incident A information.

I now need to update the yes/no field for all historically entered data so that these are protected from being overwritten when those cases are pulled up. The basic logic is:

Compare each case-level field for each non-‘A’ record to the corresponding ‘A’ record field. If any of those fields differ, set Case.different = TRUE, ELSE case.different = FALSE.
given everything you say, i would assume the best thing you can do is upload ur file so people here have something to compare your words against.
I can pull the relevant fields from the database, and either via serious convolutions in Excel, or pulling it into program R I can create a loop to set the value for the differences field and then link the table back to the database and run an update query on the existing data.

If you structure ur DB properly you won't have to do this anymore.
 

xBirdman

Registered User.
Local time
Today, 14:24
Joined
Oct 14, 2019
Messages
38
given everything you say, i would assume the best thing you can do is upload ur file so people here have something to compare your words against.
[/FONT][/FONT][/COLOR][/SIZE]
If you structure ur DB properly you won't have to do this anymore.

As I noted though, the database is very old (i.e. I didn't design it, just inherited it) and has a limited future use window as we bring a new system online over the next two years. I've been directed specifically to just increase efficiency for the users, but don't overhaul the system.

I can't upload any part of the database because it contains sensitive, personal information protected by state and federal statue. I think I've explained pretty well what I need but maybe it's too complex to make really comprehensible.
 

Mark_

Longboard on the internet
Local time
Today, 14:24
Joined
Sep 12, 2017
Messages
2,111
Do you already have a query that returns all "Incidents" for a given case and another that lists only the cases (the "A"s)?

If yes, looks like you've already got what you need. If not, I'd suggest doing both so you can treat your table like a "Parent/Child" pair. You can then modify the "Child" part to update your flag that says "Don't touch me".

I'd need to see the table's layout and some sample data if you want help in creating this pair.
 

xBirdman

Registered User.
Local time
Today, 14:24
Joined
Oct 14, 2019
Messages
38
Do you already have a query that returns all "Incidents" for a given case and another that lists only the cases (the "A"s)?

I don't have this pair of queries, but they are easy to create.

If yes, looks like you've already got what you need. If not, I'd suggest doing both so you can treat your table like a "Parent/Child" pair. You can then modify the "Child" part to update your flag that says "Don't touch me".

I'll play around with it and see if I can figure out the modification but may come back with another question. Thanks for the suggestion - hadn't thought of pairing two queries based on the same table.
 

vba_php

Forum Troll
Local time
Today, 16:24
Joined
Oct 6, 2019
Messages
2,884
I can't upload any part of the database because it contains sensitive, personal information protected by state and federal statue. .
it's not difficult to run an update query or 2 to replace the data with fake data that can be used by us!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:24
Joined
Feb 19, 2002
Messages
42,976
I'm not sure what makes something "historical" rather than current. Given that the table is not currently normalized, I expect that you will find many differences between the various instances of the same data. Are you saying that if you call up the "B", "C", "D", ... instances and their "parent" fields are different from the "A" instance, you want to update a checkbox to indicate that they are different? For what purpose? Setting the flag to true or false by using an update query is trivial but I don't see what that gets you. Wouldn't it make more sense to either prevent data from being changed for these shared fields or if you want to allow the data to be changed, run an update query to update ALL related rows so that the values are consistent across all occurrences.

So, the question comes down to - do you want all instances of the repeated data to be consistent or do you want to allow deviations? If you want to allow deviations, what are the specific rules under which something may be changed?
 

xBirdman

Registered User.
Local time
Today, 14:24
Joined
Oct 14, 2019
Messages
38
So, the question comes down to - do you want all instances of the repeated data to be consistent or do you want to allow deviations? If you want to allow deviations, what are the specific rules under which something may be changed?

So I was able to put together a set of queries that correctly checks the records and essentially locks those which I don't want to be overwritten. I haven't had a chance today to come back and close the request, but I appreciate the additional discussion.

Just to answer your questions, first I do want to allow for deviations. As a simple example a single case might have incidents that span 2-3 different locations, and we'd want those location fields, once entered as different, to remain that way, despite later updates to the parent case record. Given that a case could have many associated incidents, (the most ever was around 35) where possible I don't want users to have to type each record individually when updating the parent can update all the others. The current system is being phased out, but recent legislative changes mean we have to use this older database for another 2 years but we have higher priorities than reassembling a 15 year old dB since we're transitioning to a new one. Not the most fun, but every day is a new challenge! Cheers.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:24
Joined
Feb 19, 2002
Messages
42,976
Hopefully, your replacement application will have a properly normalized schema.
 

xBirdman

Registered User.
Local time
Today, 14:24
Joined
Oct 14, 2019
Messages
38
Hopefully, your replacement application will have a properly normalized schema.

Indeed! It has its own quirks but it is at least a proper database. What I inherited was a glorified (and very messy) filing cabinet...

Thanks all for the feedback and thoughts.
 

Users who are viewing this thread

Top Bottom