SQL query syntax using Switch
Posted on 2004-08-27
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"))
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.