How do I make a query that produces totals from a table with 3 categories and one condition?

I have a ms-db with the following structure...
MCID      State      class      Qty    DiscountEligible      Revenue
111111      AZ      ABC      1      1      35
111111      AZ      ABC      2      1      35
111111      AZ      ABC      3      1      35
111111      AZ      ABC      4      1      35
111111      AZ      DEF      1      0      35
111111      AZ      DEF      1      0      35
111111      CO      ABC      1      1      35
111111      CO      ABC      1      1      35
111111      CO      DEF      1      0      35
111111      CO      DEF      1      0      35
111111      CO      GHI      1      1      70
111111      CO      GHI      3      1      70
111111      CO      GHI      5      1      70
111111      CO      GHI      7      1      70
I need to create three separate queries that will evaluate whether the record is Discount Eligible(1=yes) before processing the record.

First, I need a query that will provide a total by MCID.  The query needs to total all quantities but only add the revenue to the total if the record is eligible for a discount.  Such as
111111                  32            490
Second, I need a query that produces totals by MCID and state.  The query again needs to total all quantities but only add the revenue to the total if the record is eligible for a discount.  Such as
111111      AZ            12            140
111111      CO            20            350
Third, I need a query that produces totals by MCID, state, and class.  This query again needs to total all quantities but only add the revenue to the sub-total if the record is eligible for a discount.  Such as
111111      AZ      ABC      10            140
111111      AZ      DEF      2            0
111111      CO      ABC      2            70
111111      CO      DEF      2            0
111111      CO      GHI      16            280

As always, any help would be very much appreciated.

Start Free Trial