Append Query - key violation - no duplicates, records exist in par

  • Thread starter Thread starter Tammy
  • Start date Start date
T

Tammy

Hi,

I have a table containing phone numbers and the ID number for the person who
the phone number belongs to. I would like to transfer the phone numbers into
another table which already contains general info about a person and contains
the field I need to populate. I figured an append query was the way to go.

Since I want to make sure the correct phone number gets listed for a
specific person (ID), I thought I would need the ID from the phone table
along with the person's phone number in the design of the query. However,
when I run the query, I receive the error that Access could not append any of
the records due to key violations. All of the ID's exist in the table I am
appending to, and there are no duplicates.

I am not trying to append the ID again, just need it to make sure the phone
number appends to the correct ID. Somewhere along the way I'm going wrong.
Any suggestions on how to set up the design?

Thanks!
 
Then you need an UPDATE query to change data in an existing record. An append
query adds NEW records.

Assuming tables named GeneralInfo and PhoneNumbers with fields named ID and
Phone, the query in SQL would look like the following.

UPDATE GeneralInfo INNER JOIN PhoneNumbers
ON GeneralInfo.ID = PhoneNumbers.ID
SET GeneralInfo.Phone = [PhoneNumbers].[Phone]
WHERE GeneralInfo.Phone is Null
and PhoneNumbers.Phone is Not Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Not append but update.

First backup your database.

Create a query with both table, join on the ID number, and run as a select
query to check data.

If all is correct then change to update query, fill in the Update To with
[TableName].[FieldName] below the field to be updated.

Run and check data.
 
Thank you sooo much, John! The first sentence of your post made it perfectly
clear to me - I've been doing so many Append queries lately and had forgotten
all about the other guys! Thanks very much!

John Spencer said:
Then you need an UPDATE query to change data in an existing record. An append
query adds NEW records.

Assuming tables named GeneralInfo and PhoneNumbers with fields named ID and
Phone, the query in SQL would look like the following.

UPDATE GeneralInfo INNER JOIN PhoneNumbers
ON GeneralInfo.ID = PhoneNumbers.ID
SET GeneralInfo.Phone = [PhoneNumbers].[Phone]
WHERE GeneralInfo.Phone is Null
and PhoneNumbers.Phone is Not Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi,

I have a table containing phone numbers and the ID number for the person who
the phone number belongs to. I would like to transfer the phone numbers into
another table which already contains general info about a person and contains
the field I need to populate. I figured an append query was the way to go.

Since I want to make sure the correct phone number gets listed for a
specific person (ID), I thought I would need the ID from the phone table
along with the person's phone number in the design of the query. However,
when I run the query, I receive the error that Access could not append any of
the records due to key violations. All of the ID's exist in the table I am
appending to, and there are no duplicates.

I am not trying to append the ID again, just need it to make sure the phone
number appends to the correct ID. Somewhere along the way I'm going wrong.
Any suggestions on how to set up the design?

Thanks!
Click to expand...
Click to expand...
 
Thank you very much for your response, Karl! Between your post and John's, I
understand what I'm doing now! Thanks again!

KARL DEWEY said:
Not append but update.

First backup your database.

Create a query with both table, join on the ID number, and run as a select
query to check data.

If all is correct then change to update query, fill in the Update To with
[TableName].[FieldName] below the field to be updated.

Run and check data.

--
Build a little, test a little.


Tammy said:
Hi,

I have a table containing phone numbers and the ID number for the person who
the phone number belongs to. I would like to transfer the phone numbers into
another table which already contains general info about a person and contains
the field I need to populate. I figured an append query was the way to go.

Since I want to make sure the correct phone number gets listed for a
specific person (ID), I thought I would need the ID from the phone table
along with the person's phone number in the design of the query. However,
when I run the query, I receive the error that Access could not append any of
the records due to key violations. All of the ID's exist in the table I am
appending to, and there are no duplicates.

I am not trying to append the ID again, just need it to make sure the phone
number appends to the correct ID. Somewhere along the way I'm going wrong.
Any suggestions on how to set up the design?

Thanks!
Click to expand...
Click to expand...
 
Back
Top