Finding Totals for creating percentages in reports

[Bernice J]

I have a table with four fields: BlockNo, District, SubDate, Status. I would like to create a report that 1)shows the percent of BlockNo that has a SubDate(IsNotNull) grouped by District and 2) shows the percent of BlockNo with SubDate(IsNotNull) and with Status=Y grouped by District.
I was able to create queries for both of these and count the number of blocks in the query. What I am unable to figure out is how to refer to the TOTAL BlockNo in the table so I can get the percent.
I'm guessing I have to do this in the report. I can get the Totals and Percent from the Queries but that's not what I want. Am I going about this wrong? Should I be doing all the calculations in the report by referring directly to the table and ignoring the queries altogether?

[MindBender77]

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

[Bernice J]

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,
I was able to create two queries. One that showed all blocks with a subdate one that showed all blocks with a subdate and a status=y. However when I create the report based on the queries and ask it to show me the percent of blocks with a subdate OR the percent of blocks with a subdate and status=y it bases the percent on the total of the query not on the total of all the blocks in the database.
Understand? Anyway here is the query code for the first query:

SELECT DISTINCTROW Count(OperationPlan.BlockNo) AS CountOfBlockNo, OperationPlan.District, OperationPlan.SubDate, OperationPlan.Status
FROM OperationPlan
GROUP BY OperationPlan.District, OperationPlan.SubDate, OperationPlan.Status
HAVING (((OperationPlan.SubDate) Is Not Null));

2nd query:

SELECT DISTINCTROW OperationPlan.BlockNo, OperationPlan.District, OperationPlan.SubDate, OperationPlan.Status, Count(*) AS [Count Of OperationPlan]
FROM OperationPlan
GROUP BY OperationPlan.BlockNo, OperationPlan.District, OperationPlan.SubDate, OperationPlan.Status
HAVING (((OperationPlan.SubDate) Is Not Null) AND ((OperationPlan.Status)="Y"));

Thanks for taking the time to look at this.

[Scott Price]

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.

SELECT Count(OperationPlan.SubDate) AS CountOfSubD
FROM OperationPlan
HAVING (((Count(OperationPlan.SubDate)) Is Not Null) AND ((OperationPlan.Status)=-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
FROM OperationPlan, Query9
GROUP BY Query9.CountOfSubD
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

[Bernice J]

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.

SELECT Count(OperationPlan.SubDate) AS CountOfSubD
FROM OperationPlan
HAVING (((Count(OperationPlan.SubDate)) Is Not Null) AND ((OperationPlan.Status)=-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
FROM OperationPlan, Query9
GROUP BY Query9.CountOfSubD
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

This is getting there. However I'm not counting districts.I want to Group by District and have for each District the Percent of all records with a Subdate and the Percent of records with a SubDate and Status=Y
I will try to modify your code and see what happens. Thanks.

[Scott Price]

Let me know if you come up with a more elegant solution, but the one I came to uses 4 queries:

Query #1 Groups by and Counts the Districts:

SELECT OperationPlan.District, Count(OperationPlan.District) AS CountOfDistrict
FROM OperationPlan
GROUP BY OperationPlan.District;
 

Query #2 Groups by District and Counts SubDates:

SELECT OperationPlan.District, Count(OperationPlan.SubDate) AS CountOfSubDate
FROM OperationPlan
GROUP BY OperationPlan.District;
 

Query #3 Groups by District and Counts SubDates where Status = Y (or -1 using a Yes/No data type for the field)

SELECT OperationPlan.District, Count(OperationPlan.SubDate) AS CountOfSubDateStatusY
FROM OperationPlan
GROUP BY OperationPlan.District, OperationPlan.Status
HAVING (((Count(OperationPlan.SubDate)) Is Not Null) AND ((OperationPlan.Status)=-1));
 

Query #4 ties them all together and does the calculation to obtain the percentage:

SELECT DISTINCTROW Query10.CountOfSubDate, Query11.CountOfDistrict, Format([CountOfSubDateStatusY]/[CountOfDistrict],"Percent") AS Expr1, Format([CountOfSubDate]/[CountOfDistrict],"Percent") AS Expr2, Query9.CountOfSubDateStatusY
FROM (Query9 INNER JOIN Query10 ON Query9.District = Query10.District) INNER JOIN Query11 ON Query10.District = Query11.District
GROUP BY Query10.CountOfSubDate, Query11.CountOfDistrict, Format([CountOfSubDateStatusY]/[CountOfDistrict],"Percent"), Format([CountOfSubDate]/[CountOfDistrict],"Percent"), Query9.CountOfSubDateStatusY;

The results that come from the fourth query:

CountOfDistrict: 3, 4
CountOfSubDate: 2, 3
CountOfSubDateStatusY: 1, 2
%1: 66.67%, 75.00%
%2: 33.33%, 50.00%

Regards,
Scott

[Scott Price]

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

[Bernice J]

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

I've been able to get all items calculating correctly except for one. In the District footer for the following example if I use Count or DCount I get a number that does mean anything. If I use Sum I get the correct number but it is a negative value. Both the Status and Subdate fields are text so I don't know how it is summing and giving the correct number. Any ideas on how I can get rid of the negative?

This is the code that gives a different(but correct) number for each District but is negative.

=Sum(([STATUS]='Y') And ([SUBDATE] Is Not Null))

This code gives me a total count of all subdates with status=y but it gives the same result for every district.

=DCount("[SubDate]Is Not Null","OperationPlan","[Status]='Y' ")