getting SUM OR AVG on report**NEED SOME FAST HELP 2

[mrwendell]

Okay, let me try to state this clearly this time.

I have a report... the report has 4 footer groups...employee,supv,mngr and director. in the mngr footer it sums all empl for a supv. one of the columns is not bound to the qry but it does calculate a % based on a txtbox that is bound to the qry.

bound to qry--[Text351]

=IIf(Sum([SumOf12MTDAbsenceOcc]) Is Null,0,Sum([SumOf12MTDAbsenceOcc]))

I take the results from above into and unbound txtbox---
[Text411]
=IIf([text351] Is Null,1,1-([Text351]/260))

what I need is to take the results of [txt411] and get an AVG place in another unbound txbox placed in the mngr footer.
THIS IS PRODUCING AN SYNTAX ERR MSG
[Text419]
avg=[Text411]
same err even trying to sum...

HELP!

 

[lamarw]

What's the error message?

 

[mrwendell]

it just a simple syntax error... fixed that... my avg txbox comes up with zeros... which i gather means its not reading the txbox sum...

 

[jsteph]

Mrwendell,
You cant use Avg(), sum(), etc on textboxes. Only on the bound fields.
--Jim

 

[mrwendell]

So... I cant add a txbox to a report, have that unbound txbox read another bound txbox and calculate on it??

if I cant add another field to the query, then I am basically screwed???

 

[jsteph]

You can't use the vertical aggregate functions, such as Sum(), Avg(), etc.

You *can* do other calcs on that, or redundantly put the source of that textbox in the next texbox...BUT, if the second textbox is not in the same group-level, then you'll get the results specific to that other group level.

It's a bit complicated at first, but once you understand the under-the-hood of the reports, you can get anything you want. I think the Report Engine in Access is Access' strongest point.
--Jim

 

[mrwendell]

okay I follow that logic... so I have a workaround in my head... since I cant AVG a unbound... if I can count the employees per group in a [txcount] use that as part of calc

***[SumOf12MTDAbsenceOcc] is the only ctrl that is bound to the query and is the starting point of this dilenma***

[text351]--cntrlsource--
=IIf(Sum([SumOf12MTDAbsenceOcc]) Is Null,0,Sum([SumOf12MTDAbsenceOcc]))

[text411]--cntrlsource--
=IIf([text351] Is Null,1,1-([Text351]/260))

then use [text426]to hold the calc result of [text411]
[text411]--cntrlsource--
=[textbox411]/[emplcount]

this will give me the % of the group

HOW CAN I COUNT THE FIELD [EMPLOYEENAME] ???

 

[jsteph]

You can use Count([employeename_field_in_query])
That will show the count at the particular group level, the group level is important to consider so be sure you're not referring to a textbox in another group level, unless that's specifically what you want.

Also, the Count() (and the sum, avg as well) will only count non-nulls. So if, for instance, the employeename comes from an outer-joined Employee table, and the report lists an employee number who for some reason is not in that table, then Count() will not count that record, even though there may be a valid emp number that comes from the 'main table' say, the Attendance or whatever.

Technically it'd be nice to have referential integrity set up, but I know in the real world that's not always the case, and outer-joins are often used to ensure that all the 'main table' records get there, and the 'lookup table' (employee in this case) is 'optional'. If this is the case, use NZ([employeename],"<Name Not Found>",[employeename]) as the source in the query itself, not in the report textbox.
--Jim

 

[mrwendell]

EXCELLENT!!!

I ran with my theory it worked...your input was a big part of that it focused me on the right solution!!! MUCH APPRECIATED YOUR EXPERTISE!!!

now I can get some brownie points from the boss..haha