Solved

switch statement access sql

Posted on 2010-08-16
11
436 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
 
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 119

Expert Comment

by:Rey Obrero
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 119

Expert Comment

by:Rey Obrero
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

708 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

13 Experts available now in Live!

Get 1:1 Help Now