MS Acc: Referencing one column from another in a query

[BenFinkel]

Hey All,

I'm trying to reference a named column in the formula of another column within one query.

i.e.

SELECT IIF(X=Y,"1","2") AS Column1, IIF([Column1]="1","B","C") AS Column2
FROM WHEREVER



But it ONLY works if I don't try to filter Column2. If I add a criteria to it, the WHERE clause cannot refer to Column1. This following line, for example, would not work. It would cause Access to prompt me for Column1.

WHERE IIF([Column1]="1","B","C") = "C"


My actual code is much more complicated and I do not want to nest too many IIFs or repeat my code too often, any way to accomplish what I'm after?

Thanks,

Ben

[si_the_geek]

The only workaround I can think of is to put the calculated value into a table (possibly a temp table), and query using that (along with apt joins).

[BenFinkel]

Yea,

I ended up, for the time being and probably permanently, making another query based off of this one that does the filtering.

Ah well,

Thanks anyways.

-Ben

[anguswalker]

It would seem that you could do the same thing using another table instead of the iifs.
Supposing you had a table (tblLookup) with
C1 C2 C3
Y 1 B
Z 2 C

in it, then your query could become

SELECT C1 AS Column1, C2 AS Column2
FROM WHEREVER INNER JOIN tblLookup on Wherever.x = tblLookup.C1