Update fields on multiple criteria

[hsingh1981]

Hi all,

Am trying to do an update query, Which updates the exam results field if the intervention id is 630
and also update the labresults if the intervention id is 6730.

I tried attempting something myself and it updates everything in the exam results and not the lab results?

UPDATE Q_DischargeSum3 INNER JOIN [DS Main] ON Q_DischargeSum3.patientId = [DS Main].PID SET [DS Main].EXAM_Results = [Q_DischargeSum3]![valueString], [DS Main].DISC_Labs_Relav_Diag_Results = [Q_DischargeSum3]![valueString]
WHERE (((Q_DischargeSum3.interventionId)=IIf([interventionId]=730,730,IIf([interventionId]=6730,6730))));

Any ideas so that it also updates the DISC_Labs_Relav_Diag_Results?

cheers

 

[hsingh1981]

Just sussed it out

UPDATE Q_DischargeSum3 INNER JOIN [DS Main] ON Q_DischargeSum3.patientId = [DS Main].PID SET [DS Main].EXAM_Results = IIf([interventionID]=730,Q_DischargeSum3!valueString), [DS Main].DISC_Labs_Relav_Diag_Results = IIf([interventionID]=6730,Q_DischargeSum3!valueString);

 

[PHV]

A safer way:

UPDATE [DS Main] AS M INNER JOIN Q_DischargeSum3 AS Q ON M.PID = Q.patientId
SET M.EXAM_Results = IIf(Q.interventionID=730,Q.valueString,M.EXAM_Results)
, M.DISC_Labs_Relav_Diag_Results = IIf(Q.interventionID=6730,Q.valueString,M.DISC_Labs_Relav_Diag_Results);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886