?
Solved

SQL query syntax using Switch

Posted on 2004-08-27
10
Medium Priority
?
649 Views
Last Modified: 2009-07-29
Hi

Im having a problem with the following query, this is what i have so far...

SELECT consultant_BB.Consultant_name, Actual_BB.Primary, Actual_BB.Secondary, Actual_BB.Upgrade, Actual_BB.Business, Actual_BB.Date, Switch(((Actual_BB.Primary+Actual_BB.Secondary+Actual_BB.Upgrade+Actual_BB.Business)>=(Round((Target_Perc_BB.target/100)*(consultant_BB.target)))),"None",True,"Works") AS Expr1
FROM Incentive_BB, Target_Perc_BB AS Target_Perc_BB_1 INNER JOIN (Target_Perc_BB INNER JOIN (status INNER JOIN (region INNER JOIN (Division INNER JOIN (consultant_BB INNER JOIN Actual_BB ON consultant_BB.ID = Actual_BB.Consultant_name) ON Division.ID = consultant_BB.Div_ID) ON region.ID = consultant_BB.Region_ID) ON status.ID = consultant_BB.Emp_Ind) ON Target_Perc_BB.Month = Actual_BB.Date) ON Target_Perc_BB_1.Month = Actual_BB.Date
WHERE (format([Actual_BB].[Date],"YYMM"))= (format([Target_Perc_BB].[month],"YYMM"))
AND ((consultant_BB.ID)=[actual_bb].[Consultant_name]);

If you check this line

Switch(((Actual_BB.Primary+Actual_BB.Secondary+Actual_BB.Upgrade+Actual_BB.Business)<=(Round((Target_Perc_BB.target/100)*(consultant_BB.target)))),"None",True,"Works") AS Expr1

its my main concern, im adding up actual sales here and comparing it to the target percentage multiplied by the actual annual target.

problem is everything is showing up as none when i know some ppl exceeded their target. once i sort this line out i can carry on, and have some basics to work on.

any help is appreciated.
Thanks
0
Comment
Question by:ridi786
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 2000 total points
ID: 11913398
Whoa there, there are way too many parentheses in that expression. Let's simplify it to make it easier to read:
Switch((Actual_BB.Primary+Actual_BB.Secondary+Actual_BB.Upgrade+Actual_BB.Business)<=Round((Target_Perc_BB.target/100)*consultant_BB.target)),"None",True,"Works")

In this expression, you want to show None if the total value is LESS than the percentage target figure. Is that correct?
0
 
LVL 1

Author Comment

by:ridi786
ID: 11913409
sorry let me make it easier to read

SELECT consultant_BB.Consultant_name,
Actual_BB.Primary,
Actual_BB.Secondary,
Actual_BB.Upgrade,
Actual_BB.Business,
Actual_BB.Date, Switch(
((Actual_BB.Primary+Actual_BB.Secondary+Actual_BB.Upgrade+Actual_BB.Business)
>=
(Round((Target_Perc_BB.target/100)*(consultant_BB.target)))),"None",True,"Works") AS Expr1
FROM Incentive_BB,
Target_Perc_BB AS Target_Perc_BB_1 INNER JOIN
(Target_Perc_BB INNER JOIN
(status INNER JOIN
(region INNER JOIN
(Division INNER JOIN
(consultant_BB INNER JOIN
Actual_BB ON consultant_BB.ID = Actual_BB.Consultant_name)
ON Division.ID = consultant_BB.Div_ID)
ON region.ID = consultant_BB.Region_ID)
ON status.ID = consultant_BB.Emp_Ind)
ON Target_Perc_BB.Month = Actual_BB.Date)
ON Target_Perc_BB_1.Month = Actual_BB.Date
WHERE
(format([Actual_BB].[Date],"YYMM"))= (format([Target_Perc_BB].[month],"YYMM"))
AND ((consultant_BB.ID)=[actual_bb].[Consultant_name]);
0
 
LVL 9

Expert Comment

by:solution46
ID: 11913426
Not sure Switch works in Access (could well be wrong there, though).

Try...

Iif ((Actual_BB.Primary+Actual_BB.Secondary+Actual_BB.Upgrade+Actual_BB.Business)<=(Round((Target_Perc_BB.target/100)*(consultant_BB.target))), "None", "Works") AS Expr1

Please check the brackets and so on. Format of the Iif statement is...

Iif(comarison, value if true, value if false).

Regards,

s46.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 1

Author Comment

by:ridi786
ID: 11913427
thats correct let me try ur suggestion
0
 
LVL 1

Author Comment

by:ridi786
ID: 11913454
ok now it told me there was an extra "(" so i added it after the word switch and still shows as none
0
 
LVL 1

Author Comment

by:ridi786
ID: 11913496
sorry my bad

the points are yours. I realised they not it suppose to be 80 percent of the

Round((Target_Perc_BB.target/100)*consultant_BB.target)),

amended it to read

Switch(((Actual_BB.Primary+Actual_BB.Secondary+Actual_BB.Upgrade+Actual_BB.Business)<=.8*(Round((Target_Perc_BB.target/100)*consultant_BB.target)))

and it comes up correct

thanks for the help anyways, i think it got too complicated with the brackets, so i neva knew what was going on anymore, hehe

R
0
 
LVL 1

Author Comment

by:ridi786
ID: 11913536
Well i dont want to use IIf cos then it gets tooo messy for my liking, tried it in excel and boy was it a mess.

Thx 4 the help solution
0
 
LVL 9

Expert Comment

by:solution46
ID: 11920765
No probs,

least I learned about Switch today  :)

s46.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11921094
The other limitation of IIf is that you can only have a maximum of nine nested IIfs (and I would've lost track of the brackets at around IIf no. 5) :-)
0
 
LVL 9

Expert Comment

by:solution46
ID: 11921179
Wow - what a limitation :)...

Don't think I've ever gone over 3!

s46.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

770 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