Combine tables of multiple copies of an Access Database

I need to comine tables of multiple copies of an Accee database so that all the records in the tables would exist into one database.
brightfarmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BadotzCommented:
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?
0
brightfarmAuthor Commented:
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.
0
Arthur_WoodCommented:
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
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

BadotzCommented:
I'm thinking it would be easier to re-enter the data into the appropriate database, eh?
0
brightfarmAuthor Commented:
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.
0
brightfarmAuthor Commented:
Do you want to copy rows from duplicate tables into a single table?

YES
0
brightfarmAuthor Commented:
To reenter the data would be disasterous because we are talking about extensive medical data for 2800 individuals.
0
BadotzCommented:
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?
0
brightfarmAuthor Commented:
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.
0
BadotzCommented:
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.
0
brightfarmAuthor Commented:
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.
0
BadotzCommented:
If you open the table in Design View (in MS Access), that list of column names and types is what I'm after.

There seems to be no easy way to copy-paste that info, although I am investigating it...

I guess you could

highlight a line
paste it into Notepad

but depending on how many columns there are...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brightfarmAuthor Commented:
Badotz

Do you need me to do that with every table in the database?
0
BadotzCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.