How to create aggregate

[ausmoran]

I have a simple table from which I need to create several aggregate data sets. I am hopeful that someone might be able to help me out. Let me start by sharing the partial table layout first:
------------------------------
Call_ID (Int)
CallDate (LongDate)
Commission (Bit)
CommissionBetweenBrokers (Bit)
CommissionRipOff (bit)
LicenseLaw (Bit)
LicenseLawOwnerShip (bit)
LicenseLawAdvertising (bit)
FairHousing (bit)
FairHousingDiscrimination (bit)
FairHousingOther (bit)
---------------------------------
I need to produce several reports showing the total numbers of calls per month for each main category along with the percentage for each. I also need to show the same for the SUB-Category (shown here by-way of indentation).

Is there any easy way of doing this? The only way I know how to do this is to manually create each query over and over again for each month for each category. It seems endless to do that.

I also need to create graphs (which I assume I will end up doing in Excel, eventually).

THANKS in advance for any assistance you might be able to provide to this novice!

 

[rac2]

One trick is to use "SUM(IIf(condition, 1, 0))" instead of "COUNT(*) WHERE condition" to obtain the number of rows matching the condition.

SELECT 
       SUM(IIf(Commission, 1, 0)) AS "Commission",
       SUM(IIf(CommissionBetweenBrokers, 1, 0)) AS "CommissionBetweenBrokers",
       ...
       SUM(IIf(FairHousingOther, 1, 0)) AS "FairHousingOther"
FROM Calls
WHERE CallDate BETWEEN [Beginning Date: ] AND [Ending Date: ]

I assume that your datatype bit is Yes/No or boolean so that it evaluates directly as true or false; otherwise you might need to write Commission = 1 .

Access will prompt for [Beginning Date: ] when you run the query.

 

[ausmoran]

Thanks very much, RAC2! Your idea/advice was very helpful and saved me a great deal of time.

Sincerely,
Austin