append query problem

[Ray Hinse]

I am having a bit of a tough time copying records from a database
table to another table using an append query. I'm not sure I am
setting up things correctly since I keep getting key violation
errors when running the query.

I am trying to copy a column of data transferred into a temp table
from Excel to an existing table in the database.

The temp table has the structure:
PIN Field2 Field3
3001
3002
3003
....

The existing table has the structure:
PIN y2001 y2002 y2003 y2004
3001
3002
3003
....

I try to create an append query to copy all the records in column
Field2 in the temp table to y2004 in the existing table. The query
seem to build with no problem, but running it causes a key violation
and I can't seem to make it work properly.

I tried to import the column directly from an Excel spreadsheet, but
I am getting the 'Field F1 doesn't exist in destination table...'
and am trying to use the append query as a workaround recommended in

http://support.microsoft.com/default.aspx?scid=kb;en-us;208380

Any help appreciated.

 

[Nikos Yannacopoulos]

Ray,

Is the PIN field the primary key in the destination table? If yes, does the
table already have records for the PINs you are trying to append, and what
you want is to import the values of the other fields? If that's the case,
then you get this error because you are trying to create new records through
the append query, with PIN values that already exist, while the PK field
will accept each value just once. If this is indeed your case, what you need
is an update query, not an append one. The query should incorporate both the
target table and the temporary one, linked on the PIN field.

HTH,
Nikos

 

[Ray Hinse]

Thanks Nikos.

I gather that the append query solely adds records and cannot really update or
add to an existing record by adding another field and value.