Query missing out certain updates

[hsingh1981]

Hi all,

Am trying to use this query to update various fields in my ds main table. There are 3 fields.

admin plan | dr name | drug therapy
filled
filled
filled

should be like this


admin plan | dr name | drug therapy
filled filled filled


the query looks at certain ids and should update the appropriate fields in the ds main. At the moment it will only fill one of the fields and not the other 2 in a row.

I don't think i got my query to do what it does.

What i'm trying to do is if the id 2943 update adminplan
id 151 update drugtherapy
id 251 update dr name
for the whole row...instead its missing 2 fields out and update one.

my code is this

UPDATE Q_DischargeSum2 INNER JOIN [DS Main] ON Q_DischargeSum2.patientId = [DS Main].PID SET [DS Main].ADMPLAN_Plan = IIf([interventionID]=2943,Q_DischargeSum2!valueString), [DS Main].DISC_DrName = IIf([interventionID]=251,Q_DischargeSum2!valueString), [DS Main].DISC_DrugTherapy = IIf([interventionID]=151,Q_DischargeSum2!valueString);

 

[PHV]

The IIf function has 3 arguments:

UPDATE Q_DischargeSum2 INNER JOIN [DS Main] ON Q_DischargeSum2.patientId = [DS Main].PID
SET [DS Main].ADMPLAN_Plan = IIf([interventionID]=2943,Q_DischargeSum2.valueString,[DS Main].ADMPLAN_Plan)
, [DS Main].DISC_DrName = IIf([interventionID]=251,Q_DischargeSum2.valueString,[DS Main].DISC_DrName)
, [DS Main].DISC_DrugTherapy = IIf([interventionID]=151,Q_DischargeSum2.valueString,[DS Main].DISC_DrugTherapy)
WHERE interventionID In (151,251,2943);

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