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!!!!!

 

[Joe4]

In SQL, you can use a CASE statement. See here: SQL Server: CASE Statement

 

[GreenWizard]

Would this be correct??? This will generate the desired SQL Query Example Output?


Select Date, Color, "Column3" =
CASE Color
When 'Red' THEN 'Number2'
When 'Blue' THEN 'Number1'
When 'Brown' THEN 'Number3'
ELSE "N/A"
END
FROM ExampleTable



Thanks!

 

[Joe4]

Close, but not quite.

Try:

[COLOR=#333333]Select Date, Color, [/COLOR]
[COLOR=#333333]CASE Color[/COLOR]
[COLOR=#333333]    When 'Red' THEN 'Number2'[/COLOR]
[COLOR=#333333]    When 'Blue' THEN 'Number1'[/COLOR]
[COLOR=#333333]    When 'Brown' THEN 'Number3'[/COLOR]
[COLOR=#333333]    ELSE 'N/A'[/COLOR]
[COLOR=#333333]END as Column3[/COLOR]
[COLOR=#333333]FROM ExampleTable[/COLOR]

 

[HiTechCoach]

Note: Access's JET/ACE SQL does not support the CASE statement. T-SQL in MS SQL Server does.

 

[Joe4]

I was going based on what was said in the original question, in which they specifically said "SQL Query" twice.

 

[HiTechCoach]

I was going based on what was said in the original question, in which they specifically said "SQL Query" twice.

The original post was not specific about the target database or "flavor" of SQL.

Since this is in a Access forum, I was just tring to clarify for other users that may read this post.

 

[Joe4]

Good point, it is not quite clear. I just took it at face value, figuring if that is not what they meant, they would post back if it didn't work.

 

[GreenWizard]

Thanks guys! So I've built my database using MS Access 2013. As stated before, I'm a novice, so I'm not an expert with relationships or queries. One thing I'm finding is that MS Access is allowing me to get better at SQL as it automatically generates the SQL syntax for me.

I tried the CASE syntax and it's not working (as predicted above). Is there anyway using MS Access 2013 to summon the desired output? Does MS Access have an alias for the CASE clause?

Thanks so much, I'm learning a lot from these forums.

Much Appreciated!!!

 

[Joe4]

What you are really wanting to do is create Access Queries, not really SQL Queries (the terminology "SQL View" in Access can be a bit confusing). Access queries can be built using the Query Builder, or SQL View.

Access has the Immediate If statement (IIF) which works much like IF does in Excel. You can nest the IIF statements. See: Nested IIF Statement

You can use Case statements in Access if you create your own functions (User Defined Functions). These are great if you have complex or lengthy functions, as if you ever need to modify it, there is only one place you need to go to make the changes. See: VBA Tips: Build Custom Functions for Your Access Applications