Merging two seperate databases with duplicate as well as unique information

[MausGMR]

Hi guys, I know it's a little cheeky asking others to help me do my work for me, but I currently am trying to help someone piece together two seperate Access Databases into one, which is the way it was always supposed to be.

Basically, this person accidentaly created a duplicate of the database in question, and has been updating both independently at different times, meaning that whilst a lot of the database information is duplicate, there are some instances where each seperate database copy has unique information on both existing contacts, and new unique contacts.

The database itself is about schools, and has tables concerning information such as calls made, contact details, school details, etc etc. Obviously all of this needs putting back together into one set of tables in one file.

I've been messing around with append and import functions, but am having issues with duplicate ID fields with append queries, and a general lack of direction on the best way to go about fixing this problem, if its even possible.

Here's an example of what i've been trying to do:

INSERT INTO Contacts ( ContactID, ContactTitle, Name, Surname, JobTitle, TelephoneSTD, Telephone, FaxSTD, Fax, Locale, SubjectKeyword, SchoolID, Reply received] )
SELECT [1Contacts].*, [1Contacts].ContactID, [1Contacts].ContactTitle, [1Contacts].Name, [1Contacts].Surname, [1Contacts].JobTitle, [1Contacts].TelephoneSTD, [1Contacts].Telephone, [1Contacts].FaxSTD, [1Contacts].Fax, [1Contacts].Locale, [1Contacts].SubjectKeyword, [1Contacts].SchoolID, [1Contacts].[Reply received]
FROM 1Contacts;

Any help on this would be very much appreciated.

 

[EternalMyrtle]

Have you tried a basic append query? So long as the index is established so as not to allow duplicate values, the append query should only append the unique values (I think).

 

[EternalMyrtle]

Update: I tested this and it works. If an index is set up in the destination table to prevent duplicates, the unique values will copy to the destination table but the duplicate values will not copy. You will get an error message stating that Access cannot append all the records in the append query because of duplicates. You can click "yes" to ignore the message and append the unique values.

 

[way2bord]

It appears that you may be using autonumbers for your unique ID fields. Be sure you DO NOT use these when merging data -- the two databases were updated simultaneously, unique data may very well have overlapping autoID's.

If this is the case --
You will need to create a new unique index between tables and migrate the data using that criteria.

You will then need to adjust all your remaining data to properly point to the new unique IDs (note - other fields pointing to existing autoIDs will all be broken!)

 

[EternalMyrtle]

Funny, a couple of minutes after I posted that last message I started thinking that if the OP tried to append the autoID field it could be the source of the problem. If it were me, I would just append one table to the other, excluding the autoID field from the append query, and let access create new ID#s for the new values.

 

[way2bord]

Funny, a couple of minutes after I posted that last message I started thinking that if the OP tried to append the autoID field it could be the source of the problem. If it were me, I would just append one table to the other, excluding the autoID field from the append query, and let access create new ID#s for the new values.

Can't migrate data like this. This results in bad joins to the rest of the tables if they're linked by AutoID Primary Keys.

Again -- this assumes that the data integrity has been violated when the duplicate database was used in parallel with the existing database. If you review the data and find that there are no overlapping PKs... you save yourself a world of trouble.

 

[MausGMR]

Hi guys, thanks for the responses so far.

So yes, my id field is using autonumbers, so it looks like thats one fixable problem. What do you think is the best way to go about doing this whilst retaining all the information? I'm not too familiar with the methods required that have been discussed so far.

Much appreciated.

 

[EternalMyrtle]

I can tell you what I did step by step. It has been working with no glitches but I am now freaked out that my method may be flawed (see way2bord's post above)

I recently combined two tables into one table using an append query. These were called 1. tblClients (PK= ClientID) and 2. tblCompanies (PK=CompanyID). I appended tblClients to tblCompanies. The fields in each table contained the same information (same data types) with different field names so I renamed them so that the field names matched and added a couple of missing fields to the destination table.

They both had an autoID field for the PK and they were all overlapping.

So, what I did was to set up a basic append query using the following steps:

1. Copy both of your tables into a database
   
2. Make sure at least one field in your table other than the PK is indexed so as not to allow duplicates (or create a multiple field index if you need to allow certain duplicates, i.e. many people will share a last name so you usually need a multi-field index for contacts).
   
3. Go to Create > Query Design
   
4. When the "Show Table" window appears just close it
   
5. Go back to the Design tab and select "Append" (plus sign with exclamation mark)
   
6. Choose the destination table (i.e. the table you will be appending to); in my case, this was tblCompanies
   
7. Show the table you will be appending (in my case, tblClients)
   
8. Select the fields you plan on appending but do not select the autoID field. The "Append to" is the field in the destination table that the data will be appended to.
   
9. Run the query. Access puts the unique records into the selected fields of the new table and creates new ID#s in the table's PK field (by default the PK is indexed to allow no duplicates).
   
10. Relink everything from the PK field in the table that was appended (ClientID) to the new table's PK field (CompanyID). So I had to relink all of the queries, code, etc. This took a ton of time and I did it manually but there is probably a better way.

In your case, since the tables are the same and presumably have the same field names, it seems that you shouldn't have to relink.

I deleted the old table (tblClients) and now have just one Companies table.

This seemed to work for me. I did it at least a month ago and have not noticed any problems. Obviously, all my records from tblClients have new ID#s but this hasn't affected anything since everything is relinked to the new PK: CompanyID.

 

[jdraw]

There is a database merge analysis tool at this site (Bob Larson - AWF long time poster/guru)
http://www.btabdevelopment.com/ts/freetools

 

[EternalMyrtle]

I don't know if the OP is still following this or not but I forgot to mention one important thing...

After the PK values from tblClients were updated to the new PK values in tblCompanies, I had to update the ClientID values in any related tables to the new CompanyID value.

That is probably what way2bord was referring to when he said you could end up with bad data. You could easily forget to update the values and end up with a big mess. I did this by sorting and using update queries but I also only had around 60 records so I could easily do a visual check.