Need an average but not for all records on report

M

Marc S

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
 
L

Larry Linson

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
 
D

Duane Hookom

You might be able to use something like:
=Sum(Abs( (Max(WeekNum) - WeekNum)<7)) *Hours)/ Sum(Abs( (Max(WeekNum) -
WeekNum)<7))
 
M

Marc S

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
 
M

Marshall Barton

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")
 
M

Marc S

Whoo - hooo. That did the trick.

Thanks,

Marc
Marshall Barton said:
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")
Click to expand...
 
Top