Solved

SQL query syntax using Switch

Posted on 2004-08-27
10
643 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

744 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