Solved

switch statement access sql

Posted on 2010-08-16
11
438 Views
Last Modified: 2012-05-10
Below is a query I am trying to run that is not working properly. Even when the Gross Sales are less than the TIER 1 MINIMUM_Standard value, the query still returns "More" as the Normal Rebate Amount. The TIER 1 MINIMUM_Standard field is a double in access so I don't think it should have single quotes around it but if I don't use the quotes I get #Error for my results. The Gross Sales field is generated by another query that rounds the sum of a double field so I don't know why they are not comparing correctly. Below is the query I am using the switch statement in.
SELECT 
    qryTempSalesForCalculation.[Location Code],
    qryTempSalesForCalculation.[Group Code],
    qryTempSalesForCalculation.[Gross Sales],
	SWITCH(
		[Gross Sales] < [TIER 1 MINIMUM_Standard],
		    "Less",
		[Gross Sales] > '[TIER 1 MINIMUM_Standard]',
		    "More")
	AS [Normal Rebate Amount]
FROM 
    NA_Groups 
INNER JOIN 
    qryTempSalesForCalculation 
ON NA_Groups.[GROUP CODE] = qryTempSalesForCalculation.[Group Code];

Open in new window

0
Comment
Question by:wipnav
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 13

Expert Comment

by:dwkor
ID: 33448162
Something like that
SELECT 
    qryTempSalesForCalculation.[Location Code],
    qryTempSalesForCalculation.[Group Code],
    qryTempSalesForCalculation.[Gross Sales],
	(CASE 
		WHEN 
			[Gross Sales] < [TIER 1 MINIMUM_Standard]
		THEN "Less"
		ELSE 
			CASE 
				WHEN[Gross Sales] > [TIER 1 MINIMUM_Standard]
				THEN "More"
				ELSE "Equal"
			END
	END)
	AS [Normal Rebate Amount]
FROM 
    NA_Groups 
INNER JOIN 
    qryTempSalesForCalculation 
ON NA_Groups.[GROUP CODE] = qryTempSalesForCalculation.[Group Code];

Open in new window

0
 
LVL 1

Author Comment

by:wipnav
ID: 33448206
This is an access database, I don't believe case statements are allowed.
0
 
LVL 75
ID: 33448213
Why do you have single quotes around this:

[Gross Sales] > '[TIER 1 MINIMUM_Standard]',


?

mx
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 33448225
The having to use quotes around a double value doesn't seem right at all, so I would ensure by being explicit that the value from other query is a double. Additionally, what happens if they are equal ?
SELECT 
    qryTempSalesForCalculation.[Location Code],
    qryTempSalesForCalculation.[Group Code],
    qryTempSalesForCalculation.[Gross Sales],
	SWITCH(
		CDbl([Gross Sales]) < [TIER 1 MINIMUM_Standard],
		    "Less",
		CDbl([Gross Sales]) > [TIER 1 MINIMUM_Standard],
		    "More", True, "Same")
	AS [Normal Rebate Amount]
FROM 
    NA_Groups 
INNER JOIN 
    qryTempSalesForCalculation 
ON NA_Groups.[GROUP CODE] = qryTempSalesForCalculation.[Group Code];

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33448230

SELECT
    qryTempSalesForCalculation.[Location Code],
    qryTempSalesForCalculation.[Group Code],
    qryTempSalesForCalculation.[Gross Sales],
      SWITCH(
            [Gross Sales] < [TIER 1 MINIMUM_Standard],
                "Less",
            [Gross Sales] > [TIER 1 MINIMUM_Standard],
                "More")
      AS [Normal Rebate Amount]
FROM
    NA_Groups
INNER JOIN
    qryTempSalesForCalculation
ON NA_Groups.[GROUP CODE] = qryTempSalesForCalculation.[Group Code];
0
 
LVL 1

Author Comment

by:wipnav
ID: 33448239
Sorry, I missed removing those in the example. If I have single quotes around [TIER 1 MINIMUM_Standard] for both the < and > of the switch statement, the query returns "More" for all values, but if I leave out the single quotes, every record has #Error.
0
 
LVL 1

Author Closing Comment

by:wipnav
ID: 33448268
Thank you, that was exactly the problem. The [Gross Sales] field was from a seperate query that pulled from a double field BUT I had rounded it to two decimal places because I am dealing with currency which must have changed the format.

Does the value become a string after a round() function?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33448278
where do you get the values for [TIER 1 MINIMUM_Standard]

do you have the field [TIER 1 MINIMUM_Standard] in your query qryTempSalesForCalculation  or in table NA_Groups?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33448328
Not as far as I know. Round() should equate to a number. Maybe there was something else in the query like format or something, but glad the explicit conversion to double helped.
0
 
LVL 1

Author Comment

by:wipnav
ID: 33448364
[TIER 1 MINIMUM_Standard0 is in the table NA_Groups
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now