IF Clause

[GreenWizard]

Based on the table below how can I write a SQL Query to Select the Date, Color, AND Corresponding Number (Based on the rules below)

EXAMPLE

Date	Color	Number1	Number2	Number3
1/1	Red	2	5	9
1/2	Blue	7	5	3
1/3	Brown	6	1	4

<tbody>
</tbody>

IF Color = Red
Then Query Column3 = Number2

If Color = Blue
Then Query Column3 = Number1

If Color = Brown
Then Query Column3 = Number3

If Color = <Anything Other Color>
Then Query Column3 = N/A


EXAMPLE OF DESIRED SQL OUTPUT

Date	Color	Column3
1/2	Blue	7
1/3	Brown	4
1/4	Black	N/A
1/1	Red	5

<tbody>
</tbody>

SQL QUERY CODE: ???

THANKS SO MUCH!!!!!

 

[GreenWizard]

Thanks! I think I'm getting close, but I still can't figure it out.

Select Date, Color, IIf([Color]="Red",[Number2],IIF([Color]="Blue",[Number1],IIF([Color]="Brown",[Number3],"N/A") AS Column3
FROM ExampleTable

Doesn't seem to work...


Based On This Logic

IF Color = Red
Then Query Column3 = Number2

If Color = Blue
Then Query Column3 = Number1

If Color = Brown
Then Query Column3 = Number3

If Color = Any Other Color<anything other="" color="">
Then Query Column3 = N/A</anything>

 

[Joe4]

You are missing a few parentheses. For every "(", you must have a matching ")".

[COLOR=#333333]Select Date, Color, IIf([Color]="Red",[Number2],IIF([Color]="Blue",[Number1],IIF([Color]="Brown",[Number3],"N/A"))) AS Column3[/COLOR]
[COLOR=#333333]FROM ExampleTable[/COLOR]

By the way, are "Number1", "Number2", and "Number3" field names, or hard=coded values you want to return?

 

[HiTechCoach]

TIP The database way: Avoid hard coding anything values when you can store it in a table.

A much dimplier way that i like to use is a Look up table. This also allows you to add, delete or edit colors without any changes required to your query.

The look up table would have a column for color and then one for the desired value.

This will allow you to do a simple query with a join on the color fields.


BONUS: This would also allo you to use a combo box to select the color at data entry time.