Use a look up table in IIF statement 1

[hsingh1981]

Hi Everyone,

Is it possible to use a look up table in the Select IIF statement.

I have reference table which contains the field OPLines. In that i have the following data K572,K618, K636 etc.

look up table is like this:

OPCodes OPLines
K491 C27
K572 C07
K582 C07
K618 C19
K636 C24


What i'm trying to do when codes are updated in the reference table my query looks at that and takes that into account.

Am not sure if this is possible? Currently am doing this below if the codes change. I have to go into the query and change the iif statement.



TRANSFORM Count(waitxx.PAT_NO) AS CountOfPAT_NO
SELECT IIf([EXP_OP_CD] Like "K572" Or [EXP_OP_CD] Like "K582","C07",IIf([EXP_OP_CD] Like "K618","C19",IIf([EXP_OP_CD] Like "K636","C24",IIf([EXP_OP_CD] Like "K491","C27")))) AS line, waitxx.ADMIT_METH
FROM waitxx, T_QuarterDates
WHERE (((waitxx.EXP_OP_CD) In (SELECT opcodes FROM R_OPCodes_Cardiology) AND ((waitxx.TCI_DT)>=[QuarterStart] Or (waitxx.TCI_DT) Is Null))
GROUP BY IIf([EXP_OP_CD] Like "K572" Or [EXP_OP_CD] Like "K582","C07",IIf([EXP_OP_CD] Like "K618","C19",IIf([EXP_OP_CD] Like "K636","C24",IIf([EXP_OP_CD] Like "K491","C27")))), waitxx.ADMIT_METH
PIVOT IIf([LOW]>=42 And [LOW]<=91,"6-13",IIf([LOW]>=92,"13+","<6")) In ("<6","6-13","13+");

 

[lespaul]

I can't tell exactly what your query is trying to do, but it sounds like you have a value in one table and you need to count the OPLines values in another table.

Let's say you have:
[tt]
originalTable
Field1 Field2 Codes
blah blah K491
blah blah K618
blah blah K572
blah blah K582
[/tt]

and you want to count the OPLines from your LookupTable:

SELECT OPLines, COUNT(*) FROM
OriginalTable O
INNER JOIN LookupTable L ON O.Codes = L.OPCodes
GROUP BY OPLines

Will return:
[tt]
C27 1
C19 1
C07 2[/tt]

Maybe you should read Understanding SQL Joins

Leslie

Have you met Hardy Heron?