please help with calculate percentage on the report

[khicon73]

Hello all, please help me on the percentage. Below is my query.

SELECT DISTINCTROW TblTableA.FieldA, TblTableB.FieldB, TblTableB.FieldC AS TypeC
FROM TblTableB INNER JOIN TblTableA ON TblTableB.FieldA = TblTableA.FieldA
WHERE (((TblTableA.FieldDate) Between [Enter First Date:] And [Enter Last Date:]) AND ((TblTableA.FieldD)="1" Or (TblTableA.FieldD)="2" Or (TblTableA.FieldD)="3" Or (TblTableA.FieldD)="7" Or (TblTableA.FieldD)="8" Or (TblTableA.FieldD)="9") AND ((TblTableA.FieldA) Not In (Select [FieldA] from [TblTableA] where [FieldE] =0 and [FieldDate] Between [Enter First Date:] And [Enter Last Date:])))
GROUP BY TblTableA.FieldA, TblTableB.FieldB, TblTableB.FieldC
HAVING (((TblTableB.FieldC)=10 Or (TblTableB.FieldC)=25 Or (TblTableB.FieldC)=60 Or (TblTableB.FieldC)=70 Or (TblTableB.FieldC)=80 Or (TblTableB.FieldC)=96 Or (TblTableB.FieldC)=97 Or (TblTableB.FieldC)=98))
ORDER BY TblTableA.FieldA;

The results after running the query in the specific time frame such as: 01/01/08 – 10/30/08

FieldA FieldB FieldC
1 Name as type 1 10
2 Name as type 2 10
3 Name as type 3 25
4 Name as type 4 30
…and so on…

Then I create a report sorting FieldC with group header = yes and group header = no
I have 4 text fields on the report…

On the FieldC header I placed 3 text fields: [FieldC], [FieldB] and (count[FieldA])
On the report footer I have a grand total text field: count[FieldA])

Run the report, the results like this:
--------------------------------------------------------------------
From 1/1/08 to 10/30/08

FieldC Count
10 2,212
25 229
30 151
40 7
75 313
Grand total: 2,912
I need to calculate the percentage and would like to show on the report like this:

From 1/1/08 to 10/30/08

FieldC Count Percentage:
10 2,212 75.96 (2,212 / 2,912)
25 229 7.86 (229 / 2,912)
30 151 5.19 (151 / 2,912)
40 7 0.24 (7 / 2,912)
75 313 10.75 (313 / 2,912)
Grand total: 2,912 100%

Please help, and thanks for your time...

 

[khicon73]

Need to correct on the result:
Hello all, please help me on the percentage. Below is my query.

SELECT DISTINCTROW TblTableA.FieldA, TblTableB.FieldB, TblTableB.FieldC AS TypeC
FROM TblTableB INNER JOIN TblTableA ON TblTableB.FieldA = TblTableA.FieldA
WHERE (((TblTableA.FieldDate) Between [Enter First Date:] And [Enter Last Date:]) AND ((TblTableA.FieldD)="1" Or (TblTableA.FieldD)="2" Or (TblTableA.FieldD)="3" Or (TblTableA.FieldD)="7" Or (TblTableA.FieldD)="8" Or (TblTableA.FieldD)="9") AND ((TblTableA.FieldA) Not In (Select [FieldA] from [TblTableA] where [FieldE] =0 and [FieldDate] Between [Enter First Date:] And [Enter Last Date:])))
GROUP BY TblTableA.FieldA, TblTableB.FieldB, TblTableB.FieldC
HAVING (((TblTableB.FieldC)=10 Or (TblTableB.FieldC)=25 Or (TblTableB.FieldC)=60 Or (TblTableB.FieldC)=70 Or (TblTableB.FieldC)=80 Or (TblTableB.FieldC)=96 Or (TblTableB.FieldC)=97 Or (TblTableB.FieldC)=98))
ORDER BY TblTableA.FieldA;

The results after running the query in the specific time frame such as: 01/01/08 – 10/30/08

FieldA FieldB FieldC
1 Name as type 1 10
2 Name as type 2 10
3 Name as type 3 25
4 Name as type 4 30
...and so on...

Then I create a report sorting FieldC with group header = yes and group header = no
I have 4 text fields on the report...

On the FieldC header I placed 3 text fields: [FieldC], [FieldB] and (count[FieldA])
On the report footer I have a grand total text field: count[FieldA])

Run the report, the results like this:
--------------------------------------------------------------------
From 1/1/08 to 10/30/08

FieldC Count
10 2,212
25 229
60 151
70 7
80 313
Grand total: 2,912
I need to calculate the percentage and would like to show on the report like this:

From 1/1/08 to 10/30/08

FieldC Count Percentage:
10 2,212 75.96 (2,212 / 2,912)
25 229 7.86 (229 / 2,912)
60 151 5.19 (151 / 2,912)
70 7 0.24 (7 / 2,912)
80 313 10.75 (313 / 2,912)
Grand total: 2,912 100%

Please help, and thanks for your time...

 

[dhookom]

Try put a total count in the report header.
Name: txtCountAll
Control Source: =Count(*)
Visible: No
Then in the group header use:
Control Source: =Count(*)/txtCountAll


Duane
Hook'D on Access
MS Access MVP

 

[khicon73]

Thanks dhookom, it works great...Once, thanks.