I am attempting to update information in a particular field using an update query, and all of my records suffer a 'key violation'. The scenario is such:
- The source table contains a text field named RESPONDENT. It was developed in an mdb using Access 2003. The field contains a single value per record.
- The target table contains a text field named RESPONDENT, but it is a multi-value field, developed for Access 2007.

My query returns a "key violoation" for every record, regardless of whether I update [target_table].[Respondent] or [target_table].[Respondent].Value

The target Respondent field is not a primary key field; it does not require a value; it is allowed to contain zero-length values; it is not indexed.

The SQL reads:
UPDATE Source_table INNER JOIN target_table ON Source_table.PrimaryKeyfield = target_table.PrimaryKeyfield SET target_table.Respondent.[Value] = Source_table.Respondent;

What might the source of the error be?

Thanks for any advice.

J

can you post a small sample?

I don't know if this will help, but even if you are updating a field that is not a primary key, you still can't create a null primary key by updating a field, and you still can't put a duplicate record in a primary key field. So is your query causing a null or duplicate of the key?

MiltonPurdy,

Thanks for your comment. The target_table already exists, and I am not touching the primary key of that table. The inner join merely relates the record of the source table to the appropriate record of the target table.

J

J -

Is Respondent part of any relationships to other tables?

Danny,

The query is part of a large database. I can tell you about the structure of the two tables, and the query.

Source table:
[Complaint Record ID] .... Autonumber
[Respondent]...................Text, Field size=30, Indexed Duplicates OK, Unicode compression=Yes, IME Mode='No Control', DisplayControl: text box

Target table:
[ComplaintRecordID] .... Autonumber
[Respondent].................Text, Field size=30, (Indexed property is not listed), Unicode compression=No, IME Mode='No Control', DisplayControl: Listbox, Allow Multiple Values=Yes

Query:
UPDATE [Complaint Details table] INNER JOIN tblComplaintDetails ON [Complaint Details table].[Complaint Record ID] = tblComplaintsDetails.ComplaintRecordID SET tblComplaintDetails.Respondent.[Value] = [Complaint Details table]![Respondant];

Does this help?

J

Daryl S,

Yes, the target_table Respondent field looks to a table tblRespondents as a lookup. All of the incoming data is also contained within tblRespondents, and so are valid options.

J

I see "Respondant" in the SQL - should this be "Respondent"? Maybe it is just a typo...

That's a typo. It is correct in the main query.

Just adding my eyes to this and wonder if it might be the exclamation point. should that be a period? is that a field from a form ?

Query:
UPDATE [Complaint Details table] INNER JOIN tblComplaintDetails ON [Complaint Details table].[Complaint Record ID] = tblComplaintsDetails.ComplaintRecordID SET tblComplaintDetails.Respondent.[Value] = [Complaint Details table]![Respondant];

Re: The last exclamation point

I built the query in the Query Design mode, and then copied the SQL from the SQL View.

The source table and target table are joined by their respective primary key. Thereafter the query design consists of a single column:
Field: Respondent.Value
Table: tblComplaintDetails
Update to: [Complaint Details table]![Respondent]