Solved

SQL query syntax using Switch

Posted on 2004-08-27
10
644 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

910 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

22 Experts available now in Live!

Get 1:1 Help Now