Running totals

  • Thread starter Thread starter Cliff
  • Start date Start date
C

Cliff

I need to create a report that will keep a running total
of all records in a database - by groups - I can use
either a query report or VBA - which ever will work.

My problem is that within each group I will have a
caryover from record to record.

For example I have records for each employee for each
month in the year.

They get a credit point for each 3 hours worked in that
month. If a employee works 41 hours in Jan 41/3 gives him
13 points with a carry over to Feb of 2 hours. These 2
hours need to be added to Feb hours before dividing by 3
for Feb points.

Thanks for any and all help

Cliff
 
Cliff said:
I need to create a report that will keep a running total
of all records in a database - by groups - I can use
either a query report or VBA - which ever will work.

My problem is that within each group I will have a
caryover from record to record.

For example I have records for each employee for each
month in the year.

They get a credit point for each 3 hours worked in that
month. If a employee works 41 hours in Jan 41/3 gives him
13 points with a carry over to Feb of 2 hours. These 2
hours need to be added to Feb hours before dividing by 3
for Feb points.
Click to expand...


Is the report grouped by employee, showing all the months
for that emplyee OR is the report grouped by month showing
all the employees that worked that month?

In the former situation, you can use a RunningSum text box
named RunHours bound to the hours field to display the
running total of hours, another text box with the expression
=RunHours \ 3 to display the points, and yet a third text
box with the expression =RunHours Mod 3 to display the
carryover to the next month.

If the report is grouped by month, then you'll have to
calculate the RunHours for each employee in a subquery in
the report's record source query. E.g.

SELECT table.employee, table.month, table.hours,
(SELECT Sum(X.hours)
FROM table As X
WHERE X.month <= table.month
AND X.emploee = table.employee
) AS RunHours
FROM table
 
Back
Top