Need an average but not for all records on report

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

Guest

I have a report that shows weekly labor data for the year 2005. Week 1, x
hours; week 2, x hours; week 3, x hours; and so on.

WeekNumHeader
[Week] [Hours]

I have the average hours for all 30 weeks in the report footer. Can I also
show the average for only the past 6 weeks?

Thanks,

Marc
 
Marc S said:
I have the average hours for all 30 weeks
in the report footer. Can I also
show the average for only the past 6 weeks?
Click to expand...

Probably using the DSum domain aggregate function to retrieve the hours and
doing your division in the expression in the Control Source would be the
easiest way... that is, set up a Text Box Control as a calculated Control.

Larry Linson
Microsoft Access MVP
 
You might be able to use something like:
=Sum(Abs( (Max(WeekNum) - WeekNum)<7)) *Hours)/ Sum(Abs( (Max(WeekNum) -
WeekNum)<7))
 
Larry,

I think you've pointed me in the right direction. It look likes the Davg
function should work but I keep getting an error. To give a little more
info, I have:

WeekNum WeekEnding Hours
1 1/7/2005 2000
2 1/14/2005 2060
3 1/21/2005 1985
etc
30 7/29/2005 3166

Seems like I could use in a calculated control:

=DAvg([Hours],[WeekNum],>24)

or

=Davg([Hours],[Weekending],>6/17/2005)

I get ERROR on the report. Any suggestions?

Thanks,

Marc
 
Marc said:
I think you've pointed me in the right direction. It look likes the Davg
function should work but I keep getting an error. To give a little more
info, I have:

WeekNum WeekEnding Hours
1 1/7/2005 2000
2 1/14/2005 2060
3 1/21/2005 1985
etc
30 7/29/2005 3166

Seems like I could use in a calculated control:

=DAvg([Hours],[WeekNum],>24)
Click to expand...


Your DAvg syntax is all wrong. Try something more like:

=DAvg("Hours", "nameofthetable", "WeekNum>24")
 
Back
Top