query using more than one equation for a field calc

  • Thread starter Thread starter KimD
  • Start date Start date
K

KimD

I've asked this before but need additional help. In general terms I need to
build queries to address conditions for different values in the same field
of a table. For example, If I have a table with fields [Xx] and [Yy], and
field [Xx] has values A,B or C, can I write a query to use an Equation 1 for
values A, Equation 2 for values B and Equation 3 for values C all in the same
result field? Another way of asking this might be to say, can I put more
than one WHERE condition in an SQL statement? I don't know if it will help,
but below is an example of what I'm trying to do.

FieldXx FieldYy Example of Equation EndResult
A 1.6 [Yy]+1 2.6
B 2.5 [Yy]+2 4.5
C 3.2 [Yy]+3 6.2
B 2.8 [Yy]+2 4.8


I've seen this done in SAS, but have not written it myself. Thank you for
any help in advance.
 
You can use nested IIf functions to do what you seek:

SELECT Xx, Yy,
Yy + IIf(Xx="A",1, IIf(Xx="B", 2, IIf(Xx="C", 3, 0))) AS EndResult
FROM YourTableName;

Or you can use a Switch function:

SELECT Xx, Yy,
Yy + Switch(Xx="A",1, Xx="B", 2, Xx="C", 3,
Xx<>"A" AND Xx<>"B" AND Xx<>"C", 0) AS EndResult
FROM YourTableName;
 
Back
Top