Percentage of Group

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following report:
Region
Win
Loss
Pending Decision
Total

The report is grouped by region (as there are 11) and then counts each win,
loss, and pending decision in that group.
At the end of my report I have a grand count of all Wins, Losses, and
Pending Decisions (separately).
What I am trying to accomplish is to get a percentage - for example, Wins
are X% of the grand count and then Losses are X% of the grand count.
Does this make sense and can you help me figure out how to get this to work?
Thanks in advance.
 
Can you give us a sample of your data and expected display? Maybe just a
couple regions of records. We have no idea what you data structure is except
that you probably have a field named Region.
 
I have a field name REGION and a field named STATUS. I have the report pull
in all records and sort by Region. Then it pulls in and sorts by Status.
For example, it pulls in Canada and then all the wins and losses. So it will
group the wins and count how many we have for Canada. Then it will group the
losses and count how many we have for Canada. Then it repeats for the next
Region and continues to do this for all the regions. So say we had an
overall total of 50 (Wins + Losses for all regions). Say Canada accounted
for 5 wins out of that grand total. I want to say that 10% of Wins were from
Canada. But I would want to continue to have it figure out the percentages
for all regions. Does this help explain what I am trying to accomplish?
Thanks!
 
Have you tried something in the report header like:
Name: txtTotalWins
Control Source:=Sum(Abs(Status="win"))

Then in a group footer, you could use:
Control Source: =Sum(Abs(status="win"))/txtTotalWins
 
Thanks that worked - but now they want to see it a different way as well.
Now they want to say okay say Canada had 16 customers total and only 4 were
wins - that equals 25%. I was trying to use the same equation but I keep
getting an error message. And this would happen for all Regions and wins,
losses, etc. Thanks for you continued patience.
 
Did you create a text box in the Region header like the text box in the
report header?
 
I truly appreciate your continued patience. It works in the report header
but the group footer is not working.

Here is the formula I currently have in the Region footer: ="Total
Customers for" & " " & [Region] & " : " & Count(*) & " " & IIf(Count(*)=1,"")
----- this adds all the losses, wins, pending decisions, for that region.

Because I am tracking Losses, Wins, Pending Decision, etc. I want it to
count the wins for that region and divide it amoung the total count for that
region. Then I want it to do the same for losses in that region, etc. The
way it seems to work now is that it takes the wins and only divdes into the
wins. I apologize if I am misunderstanding any part of your answer.
 
To count the number of records in a group where Status = "Win", use an
expression in the group header or footer like:
=Sum(Abs(Status="Win"))
To get the count of all records in a group:
=Count(*)
You should be able to figure out expression for Percent Wins.

--
Duane Hookom
MS Access MVP


_ said:
I truly appreciate your continued patience. It works in the report header
but the group footer is not working.

Here is the formula I currently have in the Region footer: ="Total
Customers for" & " " & [Region] & " : " & Count(*) & " " &
IIf(Count(*)=1,"")
----- this adds all the losses, wins, pending decisions, for that region.

Because I am tracking Losses, Wins, Pending Decision, etc. I want it to
count the wins for that region and divide it amoung the total count for
that
region. Then I want it to do the same for losses in that region, etc.
The
way it seems to work now is that it takes the wins and only divdes into
the
wins. I apologize if I am misunderstanding any part of your answer.

Duane Hookom said:
Did you create a text box in the Region header like the text box in the
report header?
Click to expand...
Click to expand...
 
Back
Top