Rolling Sum

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

Guest

I'm putting together a report that will display between 7 and 30 detail
records, showing employee activity as measured in hours. What I need to do
is show, for each record on the report, the total hours for both 7 and 8 days
previous.

For example:
date on duty 7 day 8 day
3/27 5.75 50.25 60.75
3/28 7.75 46.75 58.00
3/29 9.75 47.75 56.50
3/30 11.50 52.50 55.00
3/31 2.50 55.00 55.00
4/01 11.25 56.25 66.25
4/02 6.50 55.00 62.75
4/03 11.75 61.00 66.75

I looked at DSum but that looks like you need static criteria, or a way to
group. Any suggestions?
 
To get a better idea, look at the end of the table...04/02 and 04/03. The
first six rows contain information from the days prior to 3/27, so you can't
get a full picture.

I don't want to display details for Mar 21-26, but I need the hours to add
into the 7 day figure for the 27th. Unless it's a new employee, or they've
been on vacation or something, the report won't start at zero.

If you look at 4/2, you'll see (5.75+7.75+9.75+11.5+2.5+11.25+6.5) = 55.00
And on 4/3, you've got (7.75+9.75+11.5+2.5+11.25+6.5 + 11.75) = 61.00

Hopefully this makes a little more sense?
 
Aha! Well, I see what you are after now, but don't really know a way to
accomplish that in Access, because you have to have some thing to group by
to achieve a running sum. If you are only dealing with 7 to 30 rows at any
given point in time, you might consider exporting it to Excel where you can
do it rather easily.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html
 
For anybody who is interested, I figured out how to do this using aliases,
and breaking it out into a couple of queries.
 
Back
Top