Append query, duplicate key problem

[RichO]

If I want to append records from one table to another but some of the primary keys are duplicates even thought the record data is different, how do I do this short of selecting each field separately in the SQL?

In other words, I want to append all fields except for the primary key, because the table will create an autonumber PK for the appended records.

Is there any easy way to do this?

Thanks

 

[MagicMan]

Yup, don't specify the primary autonumber field as a field in the append query.

 

[RichO]

Well there are 36 fields in this table so I was hoping there was a simple workaround for

INSERT INTO Table1 SELECT * FROM Table2

Rather than

INSERT INTO Table1 (Field1, Field2, Field3.......etc) SELECT Field1, Field2, Field3.....etc.... FROM Table2

 

[gemma-the-husky]

do it in the query design window

then you can

docmd.openquery "queryname"

OR

currentdb.execute "queryname"

 

[MagicMan]

Absolutely, that was what I assumed was being done...I know...do not assume!