IF Clause

GreenWizard

Board Regular
Joined
Dec 8, 2013
Messages
106
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

DateColorNumber1Number2Number3
1/1Red259
1/2Blue753
1/3Brown614

<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

DateColorColumn3
1/2Blue7
1/3Brown4
1/4BlackN/A
1/1Red5

<tbody>
</tbody>


SQL QUERY CODE: ???

THANKS SO MUCH!!!!!

 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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!
 
Last edited:
Upvote 0
Close, but not quite.

Try:
Code:
[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]
 
Upvote 0
Note: Access's JET/ACE SQL does not support the CASE statement. T-SQL in MS SQL Server does.
 
Upvote 0
I was going based on what was said in the original question, in which they specifically said "SQL Query" twice.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!!!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top