[AccessD] Query Problem

Thanks for sharing this Todd. I've not used the switch function before but
it will be something I make use of in the future.

GK


On Mon, Jun 24, 2013 at 8:03 AM, Todd Harpham <toddharpham at qb3net.com>wrote:

> 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
>
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



-- 
Gary Kjos
garykjos at gmail.com