Finding Totals for creating percentages in reports

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

Familiar Sight

187 Posts

March 18th, 2008
02:49 PM
#2

Re: 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

Bernice J

Newbie

13 Posts

March 25th, 2008
12:11 PM
#3

Re: Finding Totals for creating percentages in reports

Quote:

Originally Posted by MindBender77

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:

Code: ( text )

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:

Code: ( text )

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

Moderator

1,303 Posts

March 27th, 2008
02:18 PM
#4

Re: Finding Totals for creating percentages in reports

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.

Code: ( text )

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

Code: ( text )

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

Newbie

13 Posts

March 27th, 2008
03:59 PM
#5

Re: Finding Totals for creating percentages in reports

Quote:

Originally Posted by Scott Price

Code: ( text )

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

Code: ( text )

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));

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

Moderator

1,303 Posts

March 27th, 2008
07:01 PM
#6

Re: Finding Totals for creating percentages in reports

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:

Code: ( text )

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

Query #2 Groups by District and Counts SubDates:

Code: ( text )

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)

Code: ( text )

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:

Code: ( text )

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

Last edited by Scott Price : March 27th, 2008 at 07:09 PM. Reason: additional information

Scott Price

Moderator

1,303 Posts

March 27th, 2008
07:03 PM
#7

Re: Finding Totals for creating percentages in reports

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

Newbie

13 Posts

March 28th, 2008
03:53 PM
#8

Re: Finding Totals for creating percentages in reports

Quote:

Originally Posted by Scott Price

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.

Code: ( text )

=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.

Code: ( text )

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