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