Counting in a Report

[CEV]

I have the following entered for the Control Source in a group header text
box on my report:

=Abs(Sum([CountOfGoal]))

Now in the footer of my report I want to add the result of those text boxes
in another text box. What should I enter for the Control Source of that text
box?

Thanks,

CEV

 

[RoyVidar]

I have the following entered for the Control Source in a group header
text box on my report:

=Abs(Sum([CountOfGoal]))

Now in the footer of my report I want to add the result of those text
boxes in another text box. What should I enter for the Control
Source of that text box?

Thanks,

CEV

Try with exactly the same

=Abs(Sum([CountOfGoal]))

 

[CEV]

Well I got that part figured out. I just entered this in the Control Source
of the text box in the Report Footer:

=Sum([CountOfGoal])

Now what I would like to do is have a grand total of all the GoalProgress
numbers. In the Detail section there are 2 text boxes. One has the
GoalProgress as the control source. There are 10 different GoalProgress
options. The second text box has the following as the control source:

CountOfGoal

and counts how many of each GoalProgress option.

If someone could give me some insight, I would greatly appreciate it.

Thanks,

CEV

 

[Peter Yang [MSFT]]

Hello Chad,

I'm not quite sure about what Goalprogress means. Do you mean that
Goalprogress has 10 options such as 1 to 10, and countofgoal is to count of
the aggregation of each status. For example:


Goalprogress countofgoal

1 2
1 2
2 3
2 3
2 3

There are 2 items of Goalprogress having status 1, so countofgoal for the
Goalprogress 1 is "2". If my understanding is not correct, please provide
an more detailed example.

If this is the case, it seems that you want to count the rows of the
report. You may want to use =Count(Goalprogress) to test if this is what
you want.

Plesae feel free to let's know if you have any comments. Thank you!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[CEV]

I have already tried that and that is not what I am looking for. My report
shows each "Goal" and then under that it lists each "GoalProgress" option
and the count for each one. For example, the "Relationship" Goal has an "In
Progress" count of 15 and a "Yes" count of 6. The "Residence" Goal has a "In
Progress" count of 2 and a "Yes" count of 3. What I want in the footer is to
show "In Progress" with a total count of 17 for all Goals and a "Yes" total
count of 9 for all Goals.

Thanks,

CEV

 

[Peter Yang [MSFT]]

Hello Chad,

If I understand the issue correctly, you may want to do the following:

goal goalprogress countofgoal

g1 s1 2
g1 s2 3

g2 s1 5
g2 s2 7

You 'd like to get following in report footer:

s1 total: 7(2+5)
s1 total: 10(3+7)

However, you actually have group on goal in the report and it's not
possible to get total of all rows with S1 or S2 status easily.

As far as I know, there is no simple method to achieve this goal. I think
there are 2 options:

1. Use a different report that is based on a query such as

select goalprogress sum(countgoal) from sourcetbl group by goalprogress

2. Use a query to get the information you want in report_open and save them
in global variables. Then you could show them in ReportFooter_Format by
using Me.textbox= var1

For example:

Private Sub Report_Open(Cancel As Integer)

Set db = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
Dim rec As Recordset
Dim sString As String

sString = "select employeeid, sum(Freight) as fee from Orders group by
employeeid "
Set rec = db.OpenRecordset(sString)
i = rec.Fields(1).Value

End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Me.Text18 = i

End Sub

You may want to refer to the following articles for more related
information:

Q216311 - ACC2000: How to Create Page Totals on a Report
http://support.microsoft.com/support/kb/articles/q216/3/11.asp

841779 How to reset the page number and the total page count for each group
in a Microsoft Access report
http://support.microsoft.com/default.aspx?scid=kb;EN-US;841779

If you have any further questions or comments, please feel free to let's
know. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.