Append several tables from different databases

[Taigum]

Hi All

I have started working with a database/s that was not set up by me and I am having problems appending tables from different databases.

Overview -
There was one original database set up to collect clinical data with a 'mainTBL' (health data) and a 'subTBL' (medications data) linked within the 'mainTBL'. Rather than having one continuous database the data entry person has copied the database and used a copied version for each month (where the records were deleted so new records could be entered) - so I have six databases for six months of data entry and I need to make it into one big database.

There is an ID field in the 'mainTBL' that is an autonumber (and primary key) and this has a relationship with an ID field in the 'subTBL'.

The problem -
The autonumbers in each monthly database are not unique across the six different databases (Eg the autonumber in both february and march starts at 74 so there is duplicate ID's in february/march). I have to append all the monthly databases (including the 'subTBL's) and then start a new autonumber so they are all unique and new data can then be entered. I am having trouble appending because of the duplicates and the relationship between the ID fields - what is the best way around this.

Thanks for any help in advance.
Taigum

 

[Geoff Convery]

Hi Taigum, try this:
1) Define new versions of the tables with an extra text column in each for the old Main record id (called "oldMainRecord",say)
2) Define a query for each of the old tables with an expression column for "oldMainRecord" which appends the month to the id number (you do this by typing something like - oldmainrecord:[mainid]&"Jan" - into the field box of a blank column. Specify each column separately, though, don't use the asterisk to include all the columns and do ensure that the expression is exactly the same for the new main record and the new subrecord.
3) Run the queries to check that they are returning what you want then turn them into append queries inserting into the new tables. This will create new records with new autonumbers but leave the existing relationships recorded in the "Oldmainrecord" columns
4) Define an update query that associates the sub records with the main records on the basis of the "oldmainid" but inserts the new (autonumber) mainid from the main record into the mainid column in the subrecord.

If you insert all the old records into the new table appending the right month to the old id you can then run the update query from step 4) just once to build the new relationships. If you want to retain the month analysis you should add a date column to the new subtbl and insert the start date of the month into it using the query defined at step 2. For future use you could then insert the system date every time you add a new subTbl record.
Hope this helps