[AccessD] Query Problem

Todd Harpham toddharpham at qb3net.com
Mon Jun 24 08:03:07 CDT 2013


Hello -

I guess it's maybe a little late to add to the thread, but I thought I would
submit this anyway since it offers an alternative method of dealing with
these kinds of complex logic problems in query formulation. Sometimes using
the SWITCH statement can simplify matters.  For example:

 

ROUND(SWITCH([CAT]=3,[RCOST], [CAT]=1, [RCOST]/[SumOfBillHours], [CAT]=2 AND
[sumOfBillHours]<=40, [RCOST], 

[CAT]=2 AND [sumOfBillHours]>40,
([RCOST]*40+([SumOfBillHours]-40)*[OTCOST])/[SumOfBillHours], [CAT]=4 AND
[sumOfBillHours]<=50, [RCOST], [CAT]=4 AND
[sumOfBillHours]>50,([RCOST]*50+([SumOfBillHours]-50)*[OTCOST])/[SumOfBillHo
urs]),2) AS AvgCost

 

It looks a little clearer when formatted as you might in VBA:

 

ROUND(SWITCH([CAT]=3,[RCOST], _

                [CAT]=1, [RCOST]/[SumOfBillHours], _

                [CAT]=2 AND [sumOfBillHours]<=40, [RCOST], _

                [CAT]=2 AND [sumOfBillHours]>40,
([RCOST]*40+([SumOfBillHours]-40)*[OTCOST])/[SumOfBillHours], _

                [CAT]=4 AND [sumOfBillHours]<=50, [RCOST], _

                [CAT]=4 AND [sumOfBillHours]>50,
([RCOST]*50+([SumOfBillHours]-50)*[OTCOST])/[SumOfBillHours]),2) AS AvgCost

 

Hope this helps,

ToddHarpham

 

 



More information about the AccessD mailing list