How to add a total from three other sums in a report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert - 29 Dec 2006 01:55 GMT

I have three fields in the details of a report. Hrs Wked, Lv Hrs, Extra Hrs.
Each field represents an employee's work schedule for a particular pay
period. At the Employee Footer of the report, I have a total of Hrs Wked, Lv
Hrs and Extra Hrs. I now wish to create a text box that sums up ALL three
sums and to title that TOTAL HRS.
However, i tried =sum([Hrs Wked])+sum([Lv Hrs])+sum([Extra Hrs]). If ONE
of the summations has no entry, it does not add at all. I tried Group but
still no joy.
Microsoft help says you can not do this sum as : para 207733 (I think)
I understand that ACCESS does not hold these report footer sums and this is
why i can not add them.....but i have a hard time believing that.
How may i calculate ALL 3 sums?
Robert

Reply to this Message

Jeff C - 29 Dec 2006 02:07 GMT

If one of the summations has no entry you must accoun for it by using the NZ
function to account for null values:

Try this

Sum(NZ([Hrs Worked],0))+Sum(NZ([Lv Hrs],0))+Sum(NZ([Extra Hrs],0))

Signature

Jeff C
Live Well .. Be Happy In All You Do

> I have three fields in the details of a report. Hrs Wked, Lv Hrs, Extra Hrs.
> Each field represents an employee's work schedule for a particular pay
[quoted text clipped - 9 lines]
> How may i calculate ALL 3 sums?
> Robert

Reply to this Message

Robert - 29 Dec 2006 02:54 GMT

JEFF,
Great stuff.....thanks....however, just placed formula in the report.
First employee record, block 1 = 64 block 2 = 0 block 3 = 8 and now thank
you sir, the total is 72.0.
However,
Second employee did not add correctly.
block 1 = 48 block 2 = 0 block 3 = 30.25 total = 70.25
My math is not the greatest, but it know this is not correct. What do you
suggest?

Robert

> If one of the summations has no entry you must accoun for it by using the NZ
> function to account for null values:
[quoted text clipped - 16 lines]
> > How may i calculate ALL 3 sums?
> > Robert

Reply to this Message

Jeff C - 29 Dec 2006 03:18 GMT

All I can think of is to make sure all your numbers are formatted corectly,
ie, integer or fixed decimal or double

Without more detail I can't think of what might be, you do have to be
careful with calculated controls though.

Allen Browne's website has many references and one of them deals
specifically with calculated controls.

http://allenbrowne.com/casu-14.html

Good Luck

Jeff C
Live Well .. Be Happy In All You Do

> JEFF,
> Great stuff.....thanks....however, just placed formula in the report.
[quoted text clipped - 28 lines]
> > > How may i calculate ALL 3 sums?
> > > Robert

Reply to this Message

Robert - 29 Dec 2006 03:28 GMT

Jeff,
I did review the number format. Also, re-entered the formula as a new text
box....for [Lv Used] used =sum([Lv Used] - it worked. Then ditto for
ExtraHrs.
=sum([ExtraHrs])
All fields set to fixed integer / 2 decimal.
Oddly enough NOW depending on the employee page, sometime the data adds
correctly down the column into the sum text box sometimes it does not (it
actually seems to be holding the value to the last employee's record).
However the NZ sum of all three text boxes is adding correctly.
Maybe i should leave well enough alone, but this is truly strange.
Sometimes the three sum text boxes work like perfect, then change to the next
employee page and it may or may not read correctly. Sometimes yes, sometimes
no.
But i DO appreciate your help this evening. I wrote a buddy of mine earlier
asking for help so I will ensure that I tell him you fixed it.

Have a great day.
R

> All I can think of is to make sure all your numbers are formatted corectly,
> ie, integer or fixed decimal or double
[quoted text clipped - 45 lines]
> > > > How may i calculate ALL 3 sums?
> > > > Robert

Reply to this Message

Jeff C - 29 Dec 2006 03:48 GMT

Integers are whole numbers not decimals, try double

Signature

Jeff C
Live Well .. Be Happy In All You Do

> Jeff,
> I did review the number format. Also, re-entered the formula as a new text
[quoted text clipped - 65 lines]
> > > > > How may i calculate ALL 3 sums?
> > > > > Robert

Reply to this Message