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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Color[/TD]
[TD]Number1[/TD]
[TD]Number2[/TD]
[TD]Number3[/TD]
[/TR]
[TR]
[TD]1/1[/TD]
[TD]Red[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]1/2[/TD]
[TD]Blue[/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1/3[/TD]
[TD]Brown[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Color[/TD]
[TD]Column3[/TD]
[/TR]
[TR]
[TD]1/2[/TD]
[TD]Blue[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1/3[/TD]
[TD]Brown[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1/4[/TD]
[TD]Black[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]1/1[/TD]
[TD]Red[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


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.