Finding Totals for creating percentages in reports

All that your asking can be done in a query. I recommend performing all of you calculation the queries and display them using reports. As for your problems, when you say "refer to TOTAL BlockNo" what do you mean.

Could you supply the sql from your query?

Bender

Hi Bernice,

I came over to this thread to get a better understanding of what you are trying to accomplish in Using Count and IIF in Report Control Box

Here's a suggestion based on two queries that I set up in my test database.

Expand|Select|Wrap|Line Numbers

1. SELECT Count(OperationPlan.SubDate) AS CountOfSubD
2. FROM OperationPlan
3. HAVING (((Count(OperationPlan.SubDate)) Is Not Null) AND ((OperationPlan.Status)=-1));
4.  

Expand|Select|Wrap|Line Numbers

1. SELECT Count(OperationPlan.District) AS CountOfDistrict, Count(OperationPlan.SubDate) AS CountOfSubDate, Format([countofSubdate]/[CountofDistrict],"Percent") AS Expr1, Query9.CountOfSubD, Format([CountOfsubd]/[CountOfDistrict],"Percent") AS Expr2
2. FROM OperationPlan, Query9
3. GROUP BY Query9.CountOfSubD
4. HAVING (((Count(OperationPlan.SubDate)) Is Not Null));

I entered 7 records as sample data, with a mix of subdates/nulls/statuses.

The second query returns this:

CountOfDistricts = 7;
CountOfSubDates = 5;
PercentOfDistrictswithSubDates = 71.43%;
CountOfSubDateswithStatusY = 3;
PercentOfDistrictswithSubdatesANDStatusY = 42.86%

Is this something like you are looking for?

Regards,
Scott

You'll notice that in the sql for the fourth query the names of the different queries are Query9, Query10, Query11... Not important, just the names that happened to default when I did this in my test db. You'll change them, of course, to the names of the queries you end up using in your db.

Regards,
Scott