Appending two tables

[Guest]

Hi guys,

I have a problem where I have two databases with similar table structures
that need to be combined. I have run a File - Get External Data - Link
function from one of the dbs and now have two copies of each table. The
original one from within my db and a table with a '1' at the end as the one
linked from the second db.

Makes sense so far... The problem I have is that one of my tables has got a
field called "ID" set as the key. The data type of this field is "Number".
When I run my append query I am getting errors saying there is a key
violation. I dont have this problem in tables where the field data type is
set to "Auto Number". In the two tables, the ID entries both start from 1 but
have differend details so I can't just use the table with more entries in my
final database.

Any advice on this one? Does anybody here have the no doubt guru-ish level
of knwledge that I'm lacking to advise me on how to combine the contents of
these two tables?

Thanks very much,

cheers
Matt

 

[John Vinson]

Hi guys,

I have a problem where I have two databases with similar table structures
that need to be combined. I have run a File - Get External Data - Link
function from one of the dbs and now have two copies of each table. The
original one from within my db and a table with a '1' at the end as the one
linked from the second db.

Makes sense so far... The problem I have is that one of my tables has got a
field called "ID" set as the key. The data type of this field is "Number".
When I run my append query I am getting errors saying there is a key
violation. I dont have this problem in tables where the field data type is
set to "Auto Number". In the two tables, the ID entries both start from 1 but
have differend details so I can't just use the table with more entries in my
final database.

Any advice on this one? Does anybody here have the no doubt guru-ish level
of knwledge that I'm lacking to advise me on how to combine the contents of
these two tables?

What do you want to use as the ID if there are two records in the two
tables which have the same ID? Are these tables stand-alone, or are
they linked by ID to some OTHER table or tables?

If there are no tables in a "many" relationship to either of these
tables, you may be OK. Create a THIRD table, by copying one of these
*design mode only*. Change the datatype of the Primary Key to
autonumber (and make any other field type or size changes which you
want in the final table).

Then run two Append queries, appending everything BUT the ID field
into this new table. The ID will autonumber, so the values in the ID
field will not match the ones you already have - but if there are no
related tables, and if you properly conceal the actual value of the
autonumber from view, this shouldn't matter.

John W. Vinson[MVP]