[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

Query with IF's (SQL)

I have a table with 5 fields.

FriendYN
EnemyYN
SellingPrice1
SellingPrice2
SellingPrice3

I want a query with 1 output field only.

The logic is as follows;

If EnemyYN="Y" and FRIENDYN<>"Y" select SellingPrice1
If FriendYN="Y" then select SellingPrice2
Else
Select sellingprice 3

How do I write this SQL?

(As a tangental question!  As a general rule would the experts prefer to receive a sample database attached with queries like this??  Is it more or less hassle to receive an attached database?)
0
Patrick O'Dea
Asked:
Patrick O'Dea
  • 2
2 Solutions
 
Patrick MatthewsCommented:
I like using Switch for this, as explained in http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3556-Using-the-Switch-Function-in-Microsoft-Access.html


SELECT Switch(EnemyYN = "Y" And FriendYN <> "Y, SellingPrice1, EnemyYN = "Y", SellingPrice2, True, SellingPrice3) AS Result
FROM SomeTable

Open in new window



Getting a sample database is very often helpful, especially if there is any complexity to the request :)
0
 
borkiCommented:
A sample database helps a lot - because in a scenario like yours, the best way to give you advise is by tweaking with a bit of data...

You could write a query field with IIF or SWITCH statements. However, i the logic gets complicated I prefer to write your own user defined function in VBA and call it from a query.

So the function would look like this

Public Function SellPrice(FriendYN As Variant, EnemyYN As Variant, Price1 As Variant, Price2 As Variant Price3 As Variant) As Variant
<insert your query code logic, as in the original question>
End Function

Then write a query such as:

SELECT SellPrice([FriendYN], [EnemyYN], [SellingPrice1], [SellingPrice2], [SellingPrice3]) AS Price
FROM <table>
WHERE <criteria>

HTH
0
 
Patrick O'DeaAuthor Commented:
Thanks!

The switch is new to me!

Very useful too.
0
 
Patrick MatthewsCommented:
21Dewsbury,

Glad to help!  If you have not already done so, I would really appreciate it if you could please return to my article
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3556-Using-the-Switch-Function-in-Microsoft-Access.html
and click 'Yes' for the 'Was this helpful?' voting.

Patrick
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now