Report Footer Calculations

[Jaycee]

I have a db that stores manhours for each team/project
and calculates the Average Number of Personnel (ANP) for
each team (not each project). Here's the report I'm
having trouble with:

GROUP FOOTER (team totals):
(A) Usage: Total of project hours for each team
(B) ANP: (A)/2080
(C) Available Hours: Number of members per team*2080
(this expression is in the query, and the result is
therefore attached to each record)
(D) Available ANP: (C)/2080
Net Hours: ((D)*2080))-(A)
Net ANP: (B)-((A)/2080)

Everything calculates correctly in the Group Footer.

In the Report Footer, (A) and (B) calculate correctly,
but when I sum (C), it adds all hours from all records,
which is seriously distorted. I only need it to sum the
values in the Group Footer, not in all of the records.
This naturally throws off the remaining calculations.

How do I get the Report Footer calculation to look only
at the values (C) in the Group Footer and ignore the same
value (C) that's associated with each record? Or is that
even the right question??

Any help is greatly appreciated!

Jaycee

 

[Duane Hookom]

You may have to duplicate your C text box and make it invisible, set its
name to "txtRunSumAvailHrs", and set its Running Sum to Over All. Then add a
text box to the report footer with a control source of =txtRunSUmAvailHrs.

 

[Jaycee]

Getting closer! The total available hours should be
61,568; using your suggestion, it's 62,400. I duplicated
the strategy for the Available ANP and it was also
close ... 29.60 vs. 30.00.

Actually, if I could just get one or the other (hours or
ANP) to total correctly in the report footer, I can use
that value * or / 2080 to get the other total.

Thanks for your help!

Jaycee

 

[Duane Hookom]

Can't you add a field that describes the percent full time or similar. Then
rather than count the number of people, sum this new field. I always choose
the "data-driven" method over attempting to hard-code values.

 

[Jaycee]

I added the field as you suggested (named
AvailEmployeeANP) and replaced CountOfLastName in the
query. Then I went into the report and replaced
CountOfLastName with AvailEmployeeANP in all relevant
calculations. But when I try to preview the report,
the "Enter Parameter Value" box comes up looking for
CountOfLastName.

I've been through every box on the report to verify that
that field name was not referenced in any of them.
Where'd I go wrong?

 

[Duane Hookom]

Do you have this field in either the query behind the report or in the
Sorting and Grouping dialog?

 

[Jaycee]

Double-checked both query and sorting/grouping -- the
field isn't there.

Don't give up on me, please -- the solution is SO close!!

 

[Duane Hookom]

Does the parameter come up when you open only the report's record source?

 

[Jaycee]

No, I can open the record source (query) with no problem.

Is it possible for me to attach a screenshot of the
query, or any other portion of the db, to a posted
message? I've explored dbForums.com and it allows
attachments, but I don't see that option on this site.

Thanks much!

 

[Duane Hookom]

Most of us don't appreciate attachments. If after you have looked thoroughly
through all your control sources, you haven't found the cause, you can email
me directly for instructions on how to send me a file. I normally delete any
emails with attachments that I am not expecting.

 

[Duane Hookom]

Thought I would reply here since your email in box is full and won't accept
more messages. Don't you just love emails with large virus attachments?

You have a bunch of text boxes in your report that still reference the
removed fields. One is behind another text box in the Team Footer. This is
under "Available ANP". "Usage Hours" is referenced under the Net Hours and
Net ANP columns in the Team Footer and ANP and Net ANP in the report footer.

I found this by double-clicking a text box and viewing the data properties.
Then click the control and press tab to review the Control Source property
of each control.

A few other notes. Your repeating similar queries and reports should be
paired down to one copy. You can place a combo box on a form that can
provide a "dynamic" filter for a query/report. Maintaining multiple copies
requires much more work.

Also, I would add a field to the Category:All table to identify whether it
is administrative or not. This field could then be used for filtering in
your "Admin" queries.

 

[Jaycee]

Sorry -- for days my mailbox has been flooded with
attempts to pass along the Swen virus. Seems to be under
control now.

Thank you so much for your help. After I removed the
offending references, there were still a couple of bugs
to work out, but with God's help and a couple hours
labor, I was able to resolve them without pestering you
further. Everything works now!!

I really appreciate your time and attention to this.
Thanks for not giving up on me!

Jaycee