Combine UPDATE Table Queries

  • Thread starter Thread starter Basil via AccessMonster.com
  • Start date Start date
B

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');
 
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.
 
Thank you! That's exactly what was needed to streamline my set proctype
process.

John said:
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
Click to expand...
[quoted text clipped - 8 lines]
UPDATE TempTbl SET Proctype = 'NewVisit'
WHERE Proc IN ('g','h','i');
Click to expand...
Click to expand...
 
Back
Top