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.

 

[Banana]

A suggestion would be to make another column for all tables with ID, to unique which copy of database it came, and use that column with the autonumber as a compound key.

This would be a quick and easy solution, but is a stopgap solution as it'll impact the performance as you now have 2 column of key, but will allow you to do what you need right away.