Solved

SQL query syntax using Switch

Posted on 2004-08-27
10
645 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
  • 5
  • 3
  • 2
10 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

813 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