lookup table using a in(select..... 1

[hsingh1981]

Hi all i have this query and in the criteria i have put this

Like "M30*" Or Like "M77*" Or Like "E36*" Or Like "J43*" Or "Q189" Or Like "T43*" Or Like "W87*" Or Like "W88*" Or Like "E51*"
i've changed it to a.... look up table which uses a in (select ....statement

eg

In (SELECT opcodes FROM R_OPCodes_Cardiology


But how do i change this select statement to include something with asterik (wildcard)?

currently my look up table looks like this

OPCodes Lines
E36 E07
E51 E15
J43 E08
M30 E04
M45 E05
M77 E06
Q189 E12
T43 E13
W87 E14
W88 E14

cheers

 

[jedraw]

I think your select will look something like this

SELECT [opcode] & "*" 
FROM R_OPCodes_Cardiology;

 

[dhookom]

If I understand correctly, it would be

WHERE Left([YourMainTableField],3) In (SELECT opcodes FROM R_OPCodes_Cardiology)

Duane
Hook'D on Access
MS Access MVP

 

[hsingh1981]

Many thanks for your replies

I've tried both solutions and i probably haven't explained myself as clear enough.

Here's what my old sql code looks like i've highlighted in red what i like to change:

TRANSFORM Count(waitxx.PAT_NO) AS CountOfPAT_NO
SELECT IIf([EXP_OP_CD] Like "M30*","E04",IIf([EXP_OP_CD] Like "m45*","E05",IIf([EXP_OP_CD] Like "M77*","E06",IIf([EXP_OP_CD] Like "E36*","E07",IIf([EXP_OP_CD] Like "J43*","E08",IIf([EXP_OP_CD] Like "Q189","E12",IIf([EXP_OP_CD] Like "T43*","E13",IIf([EXP_OP_CD] Like "W87*" Or [EXP_OP_CD] Like "W88*","E14",IIf([EXP_OP_CD] Like "E51*","E15",[EXP_OP_CD]))))))))) AS line
FROM T_QuarterDates, waitxx
WHERE [COLOR=#ff0000](((waitxx.EXP_OP_CD) Like "M30*" Or (waitxx.EXP_OP_CD) Like "M77*" Or (waitxx.EXP_OP_CD) Like "E36*" Or (waitxx.EXP_OP_CD) Like "J43*" Or (waitxx.EXP_OP_CD)="Q189" Or (waitxx.EXP_OP_CD) Like "T43*" Or (waitxx.EXP_OP_CD) Like "W87*" Or (waitxx.EXP_OP_CD) Like "W88*" Or (waitxx.EXP_OP_CD) Like "E51*")[/color] AND ((waitxx.TCI_DT)>=[QuarterStart] Or (waitxx.TCI_DT) Is Null) AND ((waitxx.ADMIT_METH) Not Like "13")) OR (((waitxx.EXP_OP_CD) Like "M45*") AND ((waitxx.TCI_DT)>=[QuarterStart] Or (waitxx.TCI_DT) Is Null) AND ((waitxx.ADMIT_METH) Not Like "13") AND ((waitxx.SERVICE_CD)="URP"))
GROUP BY IIf([EXP_OP_CD] Like "M30*","E04",IIf([EXP_OP_CD] Like "m45*","E05",IIf([EXP_OP_CD] Like "M77*","E06",IIf([EXP_OP_CD] Like "E36*","E07",IIf([EXP_OP_CD] Like "J43*","E08",IIf([EXP_OP_CD] Like "Q189","E12",IIf([EXP_OP_CD] Like "T43*","E13",IIf([EXP_OP_CD] Like "W87*" Or [EXP_OP_CD] Like "W88*","E14",IIf([EXP_OP_CD] Like "E51*","E15",[EXP_OP_CD])))))))))
PIVOT IIf([LOW]>=42 And [LOW]<=91,"6-13",IIf([LOW]>=92,"13+","<6")) In ("<6","6-13","13+");

I've changed it to this highlighted in green but don't get the same results.

TRANSFORM Count(waitxx.PAT_NO) AS CountOfPAT_NO
SELECT IIf([EXP_OP_CD] Like "M30*","E04",IIf([EXP_OP_CD] Like "m45*","E05",IIf([EXP_OP_CD] Like "M77*","E06",IIf([EXP_OP_CD] Like "E36*","E07",IIf([EXP_OP_CD] Like "J43*","E08",IIf([EXP_OP_CD] Like "Q189","E12",IIf([EXP_OP_CD] Like "T43*","E13",IIf([EXP_OP_CD] Like "W87*" Or [EXP_OP_CD] Like "W88*","E14",IIf([EXP_OP_CD] Like "E51*","E15",[EXP_OP_CD]))))))))) AS line
FROM T_QuarterDates, waitxx
WHERE [COLOR=green](((waitxx.EXP_OP_CD) In (SELECT [opcodes] & "*" FROM R_OPCodes_Endoscopy;)) [/color]AND ((waitxx.TCI_DT)>=[QuarterStart] Or (waitxx.TCI_DT) Is Null) AND ((waitxx.ADMIT_METH) Not Like "13")) OR (((waitxx.EXP_OP_CD) Like "M45*") AND ((waitxx.TCI_DT)>=[QuarterStart] Or (waitxx.TCI_DT) Is Null) AND ((waitxx.ADMIT_METH) Not Like "13") AND ((waitxx.SERVICE_CD)="URP"))
GROUP BY IIf([EXP_OP_CD] Like "M30*","E04",IIf([EXP_OP_CD] Like "m45*","E05",IIf([EXP_OP_CD] Like "M77*","E06",IIf([EXP_OP_CD] Like "E36*","E07",IIf([EXP_OP_CD] Like "J43*","E08",IIf([EXP_OP_CD] Like "Q189","E12",IIf([EXP_OP_CD] Like "T43*","E13",IIf([EXP_OP_CD] Like "W87*" Or [EXP_OP_CD] Like "W88*","E14",IIf([EXP_OP_CD] Like "E51*","E15",[EXP_OP_CD])))))))))
PIVOT IIf([LOW]>=42 And [LOW]<=91,"6-13",IIf([LOW]>=92,"13+","<6")) In ("<6","6-13","13+");

 

[dhookom]

If the OpCodes in R_OPCodes_Endoscopy are three characters, try:

WHERE (((Left(waitxx.EXP_OP_CD,3)) In (SELECT [opcodes]  FROM R_OPCodes_Endoscopy))

Duane
Hook'D on Access
MS Access MVP

 

[hsingh1981]

That doesn't work either. Any other solutions?

 

[hsingh1981]

sorry was little presumptious....that did work. Many thanks for your help...this is going to really help in the next stage of my query.

 

[PHV]

Was is your actual SQL code ?
I guess you could use an INNER JOIN with R_OPCodes_Endoscopy and use its Lines field instead of all the IIf ...


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