[AccessD] Query Problem

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