Update Query - Key Violation.

[grande]

When I run an Update query (code to follow), I'm being told that I can't update my tables to do a Key Violation. However, the Key is an AutoNumber. All the Required fields are being filled by the query. The only field set to No Duplicates is the PK.

Does anyone have any idea what might be causing this?

UPDATE SAMPLE LEFT JOIN SAMPLE_MASTER_TRACKING ON SAMPLE.SAMPLE_ID = SAMPLE_MASTER_TRACKING.SAMPLE_ID SET SAMPLE_MASTER_TRACKING.SAMPLE_ID = [SAMPLE].[SAMPLE_ID], SAMPLE_MASTER_TRACKING.PREVIOUS_MASTER_TRACKING_ID = 0, SAMPLE_MASTER_TRACKING.TRACK_STATE_ID = 2, SAMPLE_MASTER_TRACKING.TRACK_DATE = [SAMPLE].[DATE_STAMP], SAMPLE_MASTER_TRACKING.HOLDING_PERSON_ID = 15, SAMPLE_MASTER_TRACKING.HOLDING_LAB_ID = 27, SAMPLE_MASTER_TRACKING.NEXT_LAB_ID = 27, SAMPLE_MASTER_TRACKING.IS_CURRENT_STATE = -1, SAMPLE_MASTER_TRACKING.USER_STAMP = [SAMPLE].[USER_STAMP], SAMPLE_MASTER_TRACKING.DATE_STAMP = [SAMPLE].[DATE_STAMP], SAMPLE_MASTER_TRACKING.ORIGINATING_ORG_ID = [SAMPLE].[ORIGINATING_ORG_ID]
WHERE (((SAMPLE_MASTER_TRACKING.SAMPLE_ID) Is Null));

-------------------------
Just call me Captain Awesome.

 

[PHV]

You want an APPEND query, not an UPDATE one:
INSERT INTO SAMPLE_MASTER_TRACKING (SAMPLE_ID, PREVIOUS_MASTER_TRACKING_ID, TRACK_STATE_ID, TRACK_DATE, HOLDING_PERSON_ID, HOLDING_LAB_ID, NEXT_LAB_ID, IS_CURRENT_STATE, USER_STAMP, DATE_STAMP, ORIGINATING_ORG_ID)
SELECT SAMPLE_ID, 0, 2, DATE_STAMP, 15, 27, 27, -1, USER_STAMP, DATE_STAMP, ORIGINATING_ORG_ID
FROM SAMPLE
WHERE SAMPLE_ID Not In (SELECT DISTINCT SAMPLE_ID FROM SAMPLE_MASTER_TRACKING);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grande]

Thanks for the suggestion, but That didn't do it, I'm still getting the same error. Any other suggestions?

-------------------------
Just call me Captain Awesome.

 

[PHV]

What is the PK of SAMPLE_MASTER_TRACKING ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grande]

SAMPLE_MASTER_TRACKING_ID, an Autonumber.

-------------------------
Just call me Captain Awesome.

 

[PHV]

And no Foreign Key (except SAMPLE_ID) ?
Even PREVIOUS_MASTER_TRACKING_ID by chance ?
My guess (shoot in the darkness ...)
INSERT INTO SAMPLE_MASTER_TRACKING (SAMPLE_ID, TRACK_STATE_ID, TRACK_DATE, HOLDING_PERSON_ID, HOLDING_LAB_ID, NEXT_LAB_ID, IS_CURRENT_STATE, USER_STAMP, DATE_STAMP, ORIGINATING_ORG_ID)
SELECT SAMPLE_ID, 2, DATE_STAMP, 15, 27, 27, -1, USER_STAMP, DATE_STAMP, ORIGINATING_ORG_ID
FROM SAMPLE
WHERE SAMPLE_ID Not In (SELECT DISTINCT SAMPLE_ID FROM SAMPLE_MASTER_TRACKING);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grande]

I figured it out. HOLDING_PERSON_ID is supposed to link to Dr. Lien Mei, #15. Except her number changed to 29.

Sorry for wasting your time :-(

At least it's fixed now though

-------------------------
Just call me Captain Awesome.

 

[PHV]

I figured it out
Well, Update or Append query ?

 

[grande]

Since you asked, I tried both, and they both work, actually. The problem was the foreign key wasn't linking to anything.

-------------------------
Just call me Captain Awesome.

 

[PHV]

Thanks for the feedback.