help with running total

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

Guest

I was wondering if anyone could give me any insight on setting up a report to
figure a running total for an employee's schedule.

Currently I have a table named tblSchedule where all employees schedules are
entered in. Whatever the employee's schedule is, is what they will work
every week, hence there is no need to enter the date, just the day number.
Currently we enter 1 for Sunday, 2 for Monday, and so on. Their start time
and end time is entered in the short date format, 22:00 = 10:00pm, etc. I
know with the time being entered in as short date, you can add up beyond the
24 hour mark. Our problem is we have staff who always work over the midnight
threshhold. In order to get a short date format to do a running sum using
DateDiff I would need to store a date in with the time correct? Is there
anyway I could automate it so that it would know that day 1 is always a
Sunday, say Jan 2nd, 2005 and so on?? I really have no need for the date
since they will always work that shift on Sunday, so if it could always
associate day 1 as a date on a Sunday that would be great.

Maybe there is a lot easier way of doing this. I really just want to know
the total hours of their shift for the day, and at the bottom of the report
have a total hours per week, but I have to keep in mind many work across the
midnight threshhold.
 
Shanin said:
I was wondering if anyone could give me any insight on setting up a report to
figure a running total for an employee's schedule.

Currently I have a table named tblSchedule where all employees schedules are
entered in. Whatever the employee's schedule is, is what they will work
every week, hence there is no need to enter the date, just the day number.
Currently we enter 1 for Sunday, 2 for Monday, and so on. Their start time
and end time is entered in the short date format, 22:00 = 10:00pm, etc. I
know with the time being entered in as short date, you can add up beyond the
24 hour mark. Our problem is we have staff who always work over the midnight
threshhold. In order to get a short date format to do a running sum using
DateDiff I would need to store a date in with the time correct? Is there
anyway I could automate it so that it would know that day 1 is always a
Sunday, say Jan 2nd, 2005 and so on?? I really have no need for the date
since they will always work that shift on Sunday, so if it could always
associate day 1 as a date on a Sunday that would be great.

Maybe there is a lot easier way of doing this. I really just want to know
the total hours of their shift for the day, and at the bottom of the report
have a total hours per week, but I have to keep in mind many work across the
midnight threshhold.
Click to expand...


How about using the expression:
DateDiff("h", start, IIf(end>start, end, end+1))
 
Is there a way to get that to also show the minutes along with the hours
since some will work to a 1/2 hour? I need to make it return minutes then
format it somehow don't I? I forget how to do that at the moment.
 
Shanin said:
Is there a way to get that to also show the minutes along with the hours
since some will work to a 1/2 hour? I need to make it return minutes then
format it somehow don't I? I forget how to do that at the moment.
Click to expand...

If you want minutes, then use:
DateDiff("n", start, IIf(end>start, end, end+1))

Then you can format the total in the footer text box by
using the expression:
=txtRunSum \ 60 & Format(txtRunSum Mod 60, "\:00")
 
Back
Top