By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,449 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Finding Totals for creating percentages in reports

P: 13
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?
Mar 18 '08 #1
Share this Question
Share on Google+
7 Replies


MindBender77
100+
P: 234
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
Mar 18 '08 #2

P: 13
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW Count(OperationPlan.BlockNo) AS CountOfBlockNo, OperationPlan.District, OperationPlan.SubDate, OperationPlan.Status
  2. FROM OperationPlan
  3. GROUP BY OperationPlan.District, OperationPlan.SubDate, OperationPlan.Status
  4. HAVING (((OperationPlan.SubDate) Is Not Null));
2nd query:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW OperationPlan.BlockNo, OperationPlan.District, OperationPlan.SubDate, OperationPlan.Status, Count(*) AS [Count Of OperationPlan]
  2. FROM OperationPlan
  3. GROUP BY OperationPlan.BlockNo, OperationPlan.District, OperationPlan.SubDate, OperationPlan.Status
  4. HAVING (((OperationPlan.SubDate) Is Not Null) AND ((OperationPlan.Status)="Y"));
Thanks for taking the time to look at this.
Mar 25 '08 #3

Scott Price
Expert 100+
P: 1,384
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
Mar 27 '08 #4

P: 13
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
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.
Mar 27 '08 #5

Scott Price
Expert 100+
P: 1,384
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT OperationPlan.District, Count(OperationPlan.District) AS CountOfDistrict
  2. FROM OperationPlan
  3. GROUP BY OperationPlan.District;
  4.  
Query #2 Groups by District and Counts SubDates:
Expand|Select|Wrap|Line Numbers
  1. SELECT OperationPlan.District, Count(OperationPlan.SubDate) AS CountOfSubDate
  2. FROM OperationPlan
  3. GROUP BY OperationPlan.District;
  4.  
Query #3 Groups by District and Counts SubDates where Status = Y (or -1 using a Yes/No data type for the field)

Expand|Select|Wrap|Line Numbers
  1. SELECT OperationPlan.District, Count(OperationPlan.SubDate) AS CountOfSubDateStatusY
  2. FROM OperationPlan
  3. GROUP BY OperationPlan.District, OperationPlan.Status
  4. HAVING (((Count(OperationPlan.SubDate)) Is Not Null) AND ((OperationPlan.Status)=-1));
  5.  
Query #4 ties them all together and does the calculation to obtain the percentage:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW Query10.CountOfSubDate, Query11.CountOfDistrict, Format([CountOfSubDateStatusY]/[CountOfDistrict],"Percent") AS Expr1, Format([CountOfSubDate]/[CountOfDistrict],"Percent") AS Expr2, Query9.CountOfSubDateStatusY
  2. FROM (Query9 INNER JOIN Query10 ON Query9.District = Query10.District) INNER JOIN Query11 ON Query10.District = Query11.District
  3. 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
Mar 27 '08 #6

Scott Price
Expert 100+
P: 1,384
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
Mar 27 '08 #7

P: 13
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.
Expand|Select|Wrap|Line Numbers
  1. =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.
Expand|Select|Wrap|Line Numbers
  1. =DCount("[SubDate]Is Not Null","OperationPlan","[Status]='Y' ")
Mar 28 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.