Merging Multiple Tables with Auto-Numbering

[JBouchard]

I have 5 copies of a database that 5 users enter data into (including Pre, Post and Follow-Up information). These users send me their database quarterly and I am suppose to merge the data into one database from which to run reports, etc.

The problem comes in with the auto-numbering. Each "Pre" table has an ID that is auto-numbered. This auto-number is also stored when a corresponding "Post" (or follow-up form) is entered so that we can be sure the pre-, post- and follow-up are all linked to the same individual. When I merge the data, they are reassigned ID numbers in the Pre (while the Post still has the original Pre ID number) so now I've lost the link as to one individual's pre, post and follow-up form.

I realize now that the database shouldn't have been built this way, but is there any way to work around this. I've even tried exporting all of the rows for each of the 5 users into Excel and then importing them into Access in a new table (just so I could have the ease of running queries) but I seem to be getting errors doing that as well.

Any ideas?

Jen B.

 

[EMP]

Try this:

Create a table to hold the records. Do not use autonumber in that table; just set the ID field to Number, Long Integer.

Then use 5 append queries to append the records from each of the five tables to that table.

^

 

[neileg]

I'd agree with EMP but add one more thing. If your 5 databases are all starting at 1 for the autonumber, then you are going to get duplicates in the ID field. To get around this you need to do something that makes the ID from each database different. You could do this by appending another digit. For example from your first database, take the ID and multiply by ten and add one, in the second multiply by 10 and add 2, etc.
12345 would become
123451 from the first databse
123452 from the second
123453 etc
123454 etc
123455 etc