Microsoft Access Merge Tables

Posted Jul 19, 2003

By Danny Lesandrini

A while back, I replied to a post at the Comp.Databases.MS-Accessnewsgroup with the subject line of 'What's the simplest way to merge twodatabases?' The user had attempted to bring in the data using the File Get External Data menu option, but he discovered that all he accomplished wasto import his tables. That is a far cry from merging or combining data andunderstandably so. A properly designed database includes primary and foreignkeys and oftentimes, auto generated record ID numbers. As we will see, youcannot just append these records to a single table.

The process of merging this kind of relational data can getquite complicated, especially when you have numerous core-data tables as wellas lookup tables. While every case has its own peculiarities, I will explainthe basic steps using an employee time tracking application table schema.

You can merge the results of two or more queries, tables, and SELECT statements, in any combination, in a single UNION operation. The following example merges an existing table named New Accounts and a SELECT statement. Use a table or query as the data source Open the source database, and in the Navigation Pane, select the table or query that you want use as the mail merge data source. On the External Data tab, in the Export group, click Word Merge. The Microsoft Word Mail Merge Wizard starts.

Imagine the program has been deployed at two separate siteswith two separate back end mdb data files. Some tables contain unique data,such as tblEmployee and tblTimeRecord, and some store not so unique data suchas tblStatus.

Let's assume that each table has an autonumber ID fieldwhich starts counting from 1. Both sites will have their own employee and timerecords from 1 to n.

Merge 2 Tables In Access

  • Merge Data From Two Microsoft Access Tables into a New Table One of the most powerful features of Total Access Detective is its ability to combine (merge) data between the two tables you compared. This is extremely useful for reconciling changes made to two copies of the same table.
  • Using the example of daily transaction tables being combined into a weekly table.
  • Access provides a special type of query that you can use to vertically splice together the data from two or more tables. The tables don’t even need to have the same fields or fields of exactly the same data types. This is the union query, which can be constructed only by using the SQL View pane in the query designer.

If you merge Employee records from Site B into Site A, youwill have to renumber the Site B records so their values don't collide with anySite A ID numbers.

If you change the EmployeeID for Site B, you will have tocascade those changes to other related Site B tables, such as tblTimeRecord.How do you do this?

NOTE: Before beginning, back up your data and always workwith a copy of the actual production data!

Consider these necessary changes to Site B database...

  1. Remove the autonumber from EmployeeID field in tblEmployeefor Site B.

  2. Store largest EmployeeID from Site A in variable named lngMaxSiteA_ID.

  3. Make sure that Cascade Updates are turned on for everyrelationship

    in the Site B database that has a relation on EmployeeID.

  4. Run a simple UPDATE query to add the value, lngMaxSiteA_ID,to EmployeeID for every record at Site B tblEmployee. The Cascade Updatesshould propagate those changes throughout your database.

  5. Append all Site B records from tblEmployee to the SiteA tblEmployee.

    (Even though Site A still has an autonumber on EmployeeID,the records will go in without error and the correct ID numbers will beassigned.)

  6. Repeat this process for every table with an autonumberID.

That was easy, now wasn't it? However, we have not gottento the hard part yet.

The REAL problem comes in when dealing with 'lookup' tables,like the one I named tblStatus. Let's say that each task in tblTask has anassociated status.

If each database (Site A and Site B) is installed with apreset list of statuses, then there is no problem, but if the end users canenter their own values, you will end up with something like this:

In this case, you will need to create a mapping table toshow how these two status tables relate to each other. This tblMapStatus iscreated in the Site B database and looks like this.

Microsoft access merge tables examples

See the problem here?

Site A has a status that we do not see in Site B. That isnot a problem from one aspect, namely that Site B does not use the status'Needs Approval' so we do not need to map to it. But we do need its ID number,#4, for our 'Pending' status.

Therefore, you need to go back to tblStatus at Site A, addthe missing status ('Pending') and make note of its newly assigned ID number. In our example, that number is 5, so we update our tblMapStatus accordingly.

Now we will have to update our database at Site B by runningan UPDATE query...

Once again, this process must be repeated for allhelper/lookup tables and where necessary, cascade updates must be turned on sothat changes imposed by the mapping tables will be propagated throughout thedatabase. When finished, the mapping tables, as well as the lookup tables suchas tblStatus can be discarded since their counterpart in the Site A databasenow contains all possible values.

Well, that describes the basic process but as you can see, itis a lot of work, and a lot of custom coding. I created a tool for one clientthat was specifically designed to help their customers merge copies of thedatabase. My utility automated the process for this particular application,even providing an interface to assign new values for lookup tables like tblStatusdescribed above. While my utility will not merge your data, it may help youdevelop your own tool to merge your databases.

Microsoft Access Merge Tables Free

If you would like to see my code, drop me a line [email protected]

»See All Articles by ColumnistDanny J. Lesandrini

Microsoft Access Merge Tables

Microsoft Access Combine Multiple Tables Into One

Latest Forum Threads
MS Access Forum
Help With Microsoft Accesskasy0September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC errorJava1August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access)katty.jonh1July 25th, 06:45 AM
Query Issuealgebroni7July 23rd, 04:22 PM

Microsoft Access Merge Tables Cheat