Merging Identical Tables

[MsfStl]

I have several Identical Access dbs with Identical 'multiple' tables. All tables use a Primary ID, however the db was created with a 'shell' program and are therefore not related within Access. The shell program creates its own relationships, but the shell program does not have a merge utility, hence I must merge through Access. I've tried 'Import Tables', but that only creates duplicate tables. I can copy and paste the data, but with twenty tables per db I feel there is too much room for error i.e., missing a table, etc. Surely, there is an easier way.
Thank you in advance for any help with this.

Peace.

 

[The_Doc_Man]

It is not clear how the databases you describe are contained in your original shell program. Separate files? Subsets of a file as identified by some text syntax? A more complex structure?

Importation is going to depend heavily on the structure of these databases. Without a bit more of a structural hint, I think we are all going to be unable to do much for you.

 

[MsfStl]

Basically, this is an epidemiological survey used for research grants. I believe the original design was to make one flat table. However, because the survey is so large, (almost 2400 variables), the one flat table is broken down into 18 tables (based on a max of 255 variables per table) and then the shell program creates two tables for meta-data to tell itself how to format, algorithms, etc, (these meta-tables all have identical information). Once the data has been collected from numerous sites, my 'new' job is to merge these into one master db that is ultimately transferred to SAS for statistical analysis.

The tables, structure, and variable names are identical for each.

I just want to take nine identical surveys with 'x'-tables and merge the data into one master survey with 'x'-tables.

I hope that cleared things up. If not, I am not sure I understand the issue with merging.

Peace - Scot

 

[Pat Hartman]

I think the problem you will have is with primary keys. It is likely that each set is assigning primary keys within the same range. Therefore if you append the data from tbl1 in set2 to tbl1 in set1, the key values would be duplicated. Is that the case? If it is, you can add a fixed increment to each set as you append the data. For example, if set1 contains 1000 records for each table, when you append the records from set2, you would add 1000 to the keys as they are appended. So, key 1 from set 2 would become 1001 and its related records would also have their keys incremented so that the records would retain their relationship. Then if set2 had 1500 records, when you append set3, you would add 2500 to the key and so on.

 

[MsfStl]

I see what you are getting at. Fortunately, the ID's are, or should be unique. So, ultimately I need to write an Append query. However, if by chance there is a dup, I want to be able to determine at run time whether or not to overwrite, or append with a rename of the ID (modify by add a flag to the name). I haven't played with SQL in a awhile. Looks like I'll have to brush the dust off the books and delve deeper.

 

[Pat Hartman]

You don't have the option when you are running an append query to examine the data and determine what to do with it. The only option is to cancel the append entirely. If you see this as a potential problem then you probably want to run a query that joins the consolidated table to the table you want to append from. If any rows are returned, those are duplicates and you can do what you want to them. Once all the duplicates are handled, run the append.