Hi Tom,
There are probably many ways to go about this, but I'll offer what comes to
mind initially. Hopefully if there is an easier way someone else will post
as well.
I would start by temporarily deleting the relationships between the tables,
as well as the primary keys. Then, add a field to each table to hold the
name of the month, or the source database, or something like that, so that
the combination of the new field and the original ID# will still enable you
to uniquely identify all records after the import is done.
Next, for all of your existing data in the destination database, I would
update the new month field with the current month in each of your tables(it
could be a text field, such as "March 2005", or a date field with 3/1/2005,
etc - as long as it is consistent in all of the tables).
Then, use a series of append queries to append the data from the other
databases. For each append query, use an expression to generate the
appropriate month value for that particular database and append it to your
new field.
When you are all done, each of your tables will contain all historical
records with the original ID's and the source database or month names. Then,
you can add a new field to the main table and define it as an autonumber
field. View the table, and unique numbers will be generated for each record.
Then, for the two related tables, add new fields as long integers and close
the tables. Then, use update queries to update the new field values to the
newly generated unique ID's in the main table (to do this, you join one table
to the main table by the old ID and month, and update the related table new
ID to the main table new ID). Finally, after you confirm that the new ID's
were generated correctly in both related tables, you can delete the two
fields containing the old ID and the month name from each table (unless you
want to keep a field to refer to the month for other purposes). Then, just
reset your table relationships using the new ID field and you should be set.
The process sounds kind of long, but it shouldn't be too bad. The main
thing will be to be careful to change the expression providing the month
value with each append query. I would recommend making a backup copy of the
database before starting just in case.
HTH, Ted Allen
