Combine tables of multiple copies of an Access Database

Please clarify your request:

Do you want to merge all of the data into a single table?

Do you want to copy all tables in several databases into a single database?

Do you want to copy rows from duplicate tables into a single table?

The users (5) were entering data into 5 copies of the same Access database. The users were inputting different records into the databases and I need now to merge all the tables into a single database. I then need to remove any duplicates in each table.

How do you define 'duplicates' (is the data in the  fields in the 'duplicate' records the same (value in field1 in record 1(in one database) = value of the same field in record 2(in another database), for all fields?)

Does each table have a Primary Key, and how do you plan to deal with records which are clearly DIFFERENT in the several database, but which happen to have the same Primary Key?

AW

I'm thinking it would be easier to re-enter the data into the appropriate database, eh?

Each copy of the database has identical forms and tables and the users are inputting the medical data on 2800 employees. The tables have primary keys (Employee ID). The Employee ID is the primary key in each table. Each user is entering data for different individuals. After all the data has been entered, I need to merge all the data into a single database.

Do you want to copy rows from duplicate tables into a single table?

YES

To reenter the data would be disasterous because we are talking about extensive medical data for 2800 individuals.

How do you define "duplicate data"? How do you know when data is unique? Blindly merging rows form TableB into TableA is fraught with peril...

One wonders why this scenario existed in the first place?

One wonders why this scenario existed in the first place?

Response:
An Access 2003 database was created for a project to study the employees of a large company. Forms were created that mirrowed the various exams that were compeleted during a yearly mandatory physical for each employee. Initially a single employee utilized the Access database to input the results of the exams. It was soon became apparent that it would take many years if only one person worked on the project. As additional joined the project, the original database was copied and used by the new person to input medical records. The project grew to 7 people inputting the records with each person being given a copy of the original database. 2800 medical folders were divided amongst the project members. The project leader assumed that after all the files have been inputted, they could then be merged into the original database. This project leader transfered to a different agency and I was tasked with the merging of all the separate databases into a single database. The previous project manager was unaware of replication database or the need to create a database capable of being used by multiple users working in the database simultaneously such as SQL Server, Oracle, etc. After attemping many times to merge all the databases together, I suggested that it might require that all the data be reentered into a SQL Server database. As a result of my suggestion, the members of the project became quite hostile with several of the members threatening physical harm (being shot, decapatated, poisoning, etc.). Therefore I am in hiding until I come up with a solution (other than reinputting). I have only one more day of food in my bunker so I need a fix quick.

If, as you say, the 2,800 medical records were divvied up, and that you can (safely?) assume there is no duplicate data - since the MR were split, conceivable this is so - then a simply insert query should do the trick.

I would designate the table with the most rows as the Master, and designate the others as servant_1, servant_2, etc.

You will have to change the insert query for each of the table names, but the bulk of the SQL will remain the same.

If you need help writing such a query, post the structure of the Master table and I'll see what I can do.

This sounds straightforward, but you must be sure that all of the tables share an identical layout.

Badotz,

Thanks for the responding so soon. Could you please tell what information that you would need to write an insert query? I am IT retarded so I don't understand what you mean about providing the structure of the database.

Badotz

Do you need me to do that with every table in the database?

Did you find a way to do this? Sorry I wasn't more help in writing the INSERT query :-(

There is no need to award points when closing a question if there is no solution.