Merge data from two records in the same table

[Fairwin]

Hello Experts

I am new to Databases, so please bear with me:

I am looking for (or trying to develop) a script that will allow me to merge two similar records in the same table, and then delete one (or both) of the original records.

This is a basic example of what I want to do:

Record one

Event: EV00162

Name: Jimmy

Case: Ear Ache

Time In: 12:00:00 pm

Time Out:

Record two

Event: EV00162

Name: Jimmy

Case: Ear Ache

Time In:

Time Out: 4:00:00 pm

I want to merge these two records so the "time in" and "time out" fields are both populated in a single record, then I want to delete the incomplete records.

... Is this possible?

[LaRetta]

WELCOME TO FM FORUMS!

Is it always the same field which is missing information and same field which holds the information or do the fields vary? In other words, would you need to check the records field by field and see which held the information?

And what if the same fields from both HAD information but the information is different? How would you determine which took precedence? If you can provide specific rules then yes, it can be scripted.

I need to also understand whether these records are within the SAME table or different tables/files. Also, do the field names match exactly in both tables (and same number of fields - no fields in one that aren't in the other? And what if there are records in one table that aren't in the other and vice versa?

Edited June 2, 2009 by Guest
Added paragraph of concern issues and welcome.

[Fairwin]

WELCOME TO FM FORUMS!

[color:red]Thanks!

Is it always the same field which is missing information and same field which holds the information or do the fields vary?

[color:red]One record will have a null "Time In" and the other will have a null "Time out". Otherwise, the two records will be exactly the same.

In other words, would you need to check the records field by field and see which held the information?

[color:red]I'm not sure about this. I do know that certain criteria will have to be met before the merge. In other words, all field EXCEPT "time in" and "time out" need to have identical content.

And what if the same fields from both HAD information but the information is different?

[color:red]I think this would have to be an:

IF the field is NOT NULL

then there would be no change made to the record.

... is this possible?

How would you determine which took precedence? If you can provide specific rules then yes, it can be scripted.

I need to also understand whether these records are within the SAME table or different tables/files.

[color:red]

They are in the same table. In the process of trying to figure this out on my own, I have created a self-joining relationship for this table if that helps.

Also, do the field names match exactly in both tables (and same number of fields - no fields in one that aren't in the other? And what if there are records in one table that aren't in the other and vice versa?

[LaRetta]

If you view the existing data (in my demo file), you will see there are several records which go against the theory that “one record will have a TimeIn and no TimeOut and the other duplicate will not have a TimeIn but have a TimeOut.” The data I am accounting for has several combinations … some records have a time in both fields, some have different times in the fields and some have no times. I am allowing for this complex contingency (including differences in the other fields) because it never fails that it will happen. And when viewing large volumes of data, you may not spot these inconsistencies.

The method I chose was based upon using ! to find duplicates. I could have used a relationship and I could have checked every field to every duplicate field. But instead I believed it would be easier to concatenate fields, bring them into synchronization and then just delete the exact duplicates.

Since all fields must contain identical information except the timeIn/TimeOut fields, we will concatenate EventID, Name & Case. Then we will perform a find for duplicates. Only those records with a duplicate will be worked on. If any record is not in this set, it means that it has a variation in one or more of these OTHER fields. In the existing record set in this file, there will be 4 records omitted because of a difference (difference is highlighted in red, ie, in Name or Case).

The resultant duplicates will be sorted and any blank times filled in from the other record. At this point, it doesn’t matter which record we decide to keep – our purpose is to synchronize the data. We are only allowed to fill in blank times and never change a time (so any duplicates with different times will NOT be changed).

After all possible blanks have been filled in from the duplicate, we will again perform a find for duplicates but this time it will be based upon ALL fields (cConcatenatedResult) which will include TimeIn and TimeOut. This means that one of the records can be deleted and it doesn’t matter which one. It can also mean that the records may still have a blank time (either TimeIn or TimeOut or both) but it would mean that they are still identical to its duplicate so one record can be deleted.

After one of each record (in this found set) is deleted, you are done. You can click the ‘Find Records with Problems’ to see which records the process refused to handle, ie, they have difference in Name (Jan vs. Jannie), they have difference in Case (Twisted Ankle vs. AnkleS). And EventID 000162 has different start times and EventID 000177 has different end times.

MergeData.zip

[Fairwin]

This is exactly what I needed!