Solved

SQL query syntax using Switch

Posted on 2004-08-27
10
647 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
Industry Leaders: 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!

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

730 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