Combine UPDATE Table Queries

[Basil via AccessMonster.com]

Is it possible to combine the following three queries into a single object in
Access, saving keystrokes of having to run three separate queries?

UPDATE TempTbl SET Proctype = 'Encounter'
WHERE Proc IN ('a','b','c');

UPDATE TempTbl SET Proctype = 'EstablishedVisit'
WHERE Proc IN ('d','e','f');

UPDATE TempTbl SET Proctype = 'NewVisit'
WHERE Proc IN ('g','h','i');

 

[John Spencer]

Yes.

UPDATE tempTbl
Set ProcType = IIF(Proc in ('a','b','c'), 'Encounter', IIF(Proc in
('d','e','f'),'Established Visit', 'New Visit'))
WHERE Proc in ('a','b','c','d','e','f','g','h','i')

Much better would be to set up a small table -ProcConvert -with two columns
ProcLetter and ProcDescription

Then you could do
Update tempTbl INNER JOIN ProcConvert
on tempTbl.Proc = ProcConvert.ProcLetter
Set ProcType = procConvert.ProcDescription

Of course, that isn't even needed, since you could always just join to the
ProcConvert table whenever you needed the ProcDescription.

 

[Basil via AccessMonster.com]

Thank you! That's exactly what was needed to streamline my set proctype
process.

Yes.

UPDATE tempTbl
Set ProcType = IIF(Proc in ('a','b','c'), 'Encounter', IIF(Proc in
('d','e','f'),'Established Visit', 'New Visit'))
WHERE Proc in ('a','b','c','d','e','f','g','h','i')

Much better would be to set up a small table -ProcConvert -with two columns
ProcLetter and ProcDescription

Then you could do
Update tempTbl INNER JOIN ProcConvert
on tempTbl.Proc = ProcConvert.ProcLetter
Set ProcType = procConvert.ProcDescription

Of course, that isn't even needed, since you could always just join to the
ProcConvert table whenever you needed the ProcDescription.

Is it possible to combine the following three queries into a single object
in

[quoted text clipped - 8 lines]

UPDATE TempTbl SET Proctype = 'NewVisit'
WHERE Proc IN ('g','h','i');