Compare and Combine record fields

[State]

I have a single table that has duplicate records. For example, two records (call them "record1" & "record2") would have 5 fields, which are identical in both records. Record1 might have 4 other fields which have data, and Record2 might have the same 4 fields without data.

What I would like to do is compare the records. The compare would check to see if the 5 fields are identical. If the five fields are identical between the two records, replace the 4 empty fields in record2 with the 4 populated fields from record1. Next, I would want to delete record1 (maybe copy record to an archive table) and retain record2 (the "good" record).

Has anyone come across this, have an example or has tried doing this? Or, is there a better way to go about doing this, short of manually copying & pasting the data from record1 to record2?

Thank you in advance for any assistance offered.

Paul

 

[SimonC]

Just curious - if record one has perfectly valid data and record two is missing things, is there a particular reason why you don't just keep record 1 and junk record 2?

If that was OK, a fairly straightforward delete query would do the job.

Also is it possible that both record 1 and record 2 could have values in the other 4 fields or that, for example, record 1 could have values in the first two fields and record 2 in the last two?

Also, will there only ever be duplicates or could there be three or more versions of the same record? If so, which do you want to keep? How do you determine this?

 

[State]

Record1 has data in fields 1,2,3, and 4 whereas record2 has data in only in 1 and 2 (3 and 4 are empty). For example, record1 (the "good" record for which I want to keep) and record2 (the record for which I want to extract the data that is not in record1, copy to record1 and then delete record2) both have common fields, like Street and City. Record2 also has a Price and a Quantity field, which does not exist in record1. So, I would like to first compare both records ("if record1.Street = record2.Street AND record1.City = record2.City"), and if the compared fields are identical, copy the Price and Quantity data from record2 to record1.

There are no duplicates of data, other than the common fields (Street, City, etc...) and any fields that are empty in both records (price may be empty in both records.)

I hope this helps explains things.Thank you.

 

[David R]

Just to clarify, field 1, 2, 3, 4, 5 aren't related are they? They're just fields you haven't named? I ask because I wonder on your comment "just the common fields" if you have a data normalization problem.

Simon has a point that this is not going to be easy, but if this is a one time operation it can be arranged with a series of Update Queries. If this is an ongoing problem you need to look at revising your data entry scheme.

[This message has been edited by David R (edited 04-26-2002).]

 

[State]

Here's a bit more background. This database contains a table which was a result of merged record fields from two databases, one table in each database. There are three "types" of records from each table:

1) Common populated fields from each table (examples: Street, City, code, etc...).

2) Other common fields, table1 having none of the fields populated, table2 having some fields populated (example: Phone number).

3) Unique fields in both tables (examples: table1.Rent, table2.Heat).

The new database contains All of the fields from each table - single common fields (i.e., each record has only one Street, City, code fields), other common fields, and the unique fields. In other words, there is one record representing all of the data from table1 plus fields from table2, and one record representing all of the data from table2 plus fields from table1. A record (let's say for example that the record was derived from table1) will have blank fields if the fields did not originally exist in table1. I.e., the record is the merge of the table1 fields and table2 fields.

Below is an illustration:

Table1 (old database)
Code Street City Phone Rent
1 2 main st NY 123-4567 $500

Table2 (old database)
Code Street City Phone Heat
1 2 main st NY $200

Existing Table (new database)
Code Street City Phone Rent Heat
1 2 main st NY 123-4567 $500
1 2 main st NY $200

Desired Resultant Table
Code Street City Phone Rent Heat
1 2 main st NY 123-4567 $500 $200

I hope this offers additional clarification.

 

[David R]

Ohhhh, gotcha. I was hoping it was something like that, where you're fixing old data and not dealing with a bad design that will be ongoing.

Is

 '1 2', or is it '1' and then [Street] is '2 Main St.'? Anyway, I'm assuming/hoping these records have a identifier in common from Table1 to Table2. Street might be a valid choice, if it is ALWAYS identical between both entries. PhoneNum is another possibility; we need something to tell Access 'these two records are identical, and we need to merge their fields.'

Have you done any changes on the new database tables yet, or can you still go back to the old ones and get the exact same data? It will be easier my way from the old tables:
Step 0: Copy both old tables into your new database. Make your new table with all the fields you'll want, but leave it blank. (You may have already done this). Make a backup!
Step 1: Create an Append Query that moves all fields (Table1.*) from Table1(Old) to NewTable. Table1 should be, if possible, the table which has "more" data in it. No criteria should be necessary, we're just getting the old records into the new table.
To do this if you're not familiar with SQL/Append Queries: Create a Select Query, Show Table: Table1, Query>Append Query, select the table to Append to: NewTable (substituting names as appropriate). Drag/double-click the Asterisk and hit the red [!] button to run it. Agree to the changes. If there are problems appending, you may have to take the Required value off of some of the NewTable fields temporarily, or find out why it is having problems.
Step 2: Create Update Queries that copy any fields which might be different/added in Table2 into NewTable. You'll have to do the fields individually here, [i]one per query[/i]. Make sure for each field you put in the Criteria: Is Null.
To do this: New Select Query, Show Table: NewTable AND Table2. Make sure the tables are 'joined' by the unique identifying field we mentioned earlier. There should be a line connecting them. Drag/double-click a field you'll need to update, and in the Update To: field, put the reference to the old field in table2: [Table2].[Rent], [Table2].[PhoneNum], etc. You can use the Expression Builder to give you the correct syntax but it will become mind-numbingly obvious after you do a few of them. Make sure the Criteria section says Is Null or you will overwrite existing data! Preview the Query with the button you usually use to View a query (all fields should be blank, which will freak you out the first time you do it). Then [!] Run the query, and agree to changes if it sounds right. Now change your query to the next field you need to change, and the Update To: line, etc.
The reason you need separate queries is otherwise you cannot specify the Is Null criteria for each field individually. If you put it all on the same line it will only update those entries where ALL of the fields are Null, and if you put it on separate lines it will overwrite old data when ANY of the lines is Null.

Step 3: Review your data, make sure everything copied over correctly, then you can delete your old tables and go on about your day.

Good luck,
David R

 

[State]

David:

Your steps make sense and I will try it out. I really appreciate your help. Thanks again!

Paul