how do I sum computed fields in access

[Guest]

I have a report that I've created that is based on a query.

In my original database, I have the following information for my employees:

Employee ID, WorkDate, StartTime, EndTime, Category

In my query, I have computed the total # of hours a person works in a day
using the formula: Total = DateDiff("n",[StartTime],[EndTime])/60

Then in my report, I compute the number of regular and overtime hours an
employee works each day. Employees may work more than one "shift" in a day,
so some days may have more than one entry for an employee.

In the detail section of my report, I compute several fields. They are:

1) TotalHrs = [Total] --- summed over WorkDate in the WorkDate footer
-- This gives me a running total of the number of hours an employee has
worked on a given day.

2) OT =IIf([TotalHrs] Between 0 And 10,0,([TotalHrs]-10)) (Our company
operates on Flex Time where anything over 10 hours in a day or 40 hours in a
week is overtime)

3) Reg =[Total]-[OT]

That all works fine.

My report is set up so that I have several levels of grouping. First,
records are grouped by Employee, then by the workweek, then by the day.

In the footer section of WorkDay (when grouped by the day) I can get
accurate daily totals using the following computed fields:

1) DayTotal = Sum[Total] -- This gives me the total number of hours an
employee has worked on a given day summed over one or more entries.

2) DayOT =IIf([TotalHrs] Between 0 And 10,0,([TotalHrs]-10))

3) DayReg =[DayTotal]-[DayOT]

This all works fine too.

Here's the problem:

I also want to get weekly subtotals and grand totals for each employee for
the number of Reg and OT hours worked but can not figure out how to get them.

In my WorkDay footer section for grouping by week I can get a sum of the
total hours worked in a week (using =Sum([Total]) in the footer).

I can also get this sum for the Employee by putting the same value in the
Employee footer.

I CAN NOT get sub totals at the weekly and employee level for OT and Reg
hours, though.

What I want to do is sum DayOT and DayReg to compute WeekOT and WeekReg and
then sum these again at the employee level to get EmployeeOT and EmployeeReg.
But nothing I've tried will work.

If I try =[DayOT] for example in the Week footer, I don't get an error --
but I only get the right result if overtime only occurs in the last day of
the week. If it occurs early in the week, but there is no OT on the last
entry of the week, then I get an answer of 0.0. So basically, this isn't
summing. (And it doesn't matter if I set Running Sum to No or Sum Over
Group).

It seems that there has to be a way to get these totals, but I'm stumped.
Any suggestions of what I'm doing wrong?

Kim

 

[Marshall Barton]

I have a report that I've created that is based on a query.

In my original database, I have the following information for my employees:

Employee ID, WorkDate, StartTime, EndTime, Category

In my query, I have computed the total # of hours a person works in a day
using the formula: Total = DateDiff("n",[StartTime],[EndTime])/60

Then in my report, I compute the number of regular and overtime hours an
employee works each day. Employees may work more than one "shift" in a day,
so some days may have more than one entry for an employee.

In the detail section of my report, I compute several fields. They are:

1) TotalHrs = [Total] --- summed over WorkDate in the WorkDate footer
-- This gives me a running total of the number of hours an employee has
worked on a given day.

2) OT =IIf([TotalHrs] Between 0 And 10,0,([TotalHrs]-10)) (Our company
operates on Flex Time where anything over 10 hours in a day or 40 hours in a
week is overtime)

3) Reg =[Total]-[OT]

That all works fine.

My report is set up so that I have several levels of grouping. First,
records are grouped by Employee, then by the workweek, then by the day.

In the footer section of WorkDay (when grouped by the day) I can get
accurate daily totals using the following computed fields:

1) DayTotal = Sum[Total] -- This gives me the total number of hours an
employee has worked on a given day summed over one or more entries.

2) DayOT =IIf([TotalHrs] Between 0 And 10,0,([TotalHrs]-10))

3) DayReg =[DayTotal]-[DayOT]

This all works fine too.

Here's the problem:

I also want to get weekly subtotals and grand totals for each employee for
the number of Reg and OT hours worked but can not figure out how to get them.

In my WorkDay footer section for grouping by week I can get a sum of the
total hours worked in a week (using =Sum([Total]) in the footer).

I can also get this sum for the Employee by putting the same value in the
Employee footer.

I CAN NOT get sub totals at the weekly and employee level for OT and Reg
hours, though.

What I want to do is sum DayOT and DayReg to compute WeekOT and WeekReg and
then sum these again at the employee level to get EmployeeOT and EmployeeReg.
But nothing I've tried will work.

Create an invisible text box named txtRunOT next to the
WorkDay footer DayOT text box. Set this text box's control
source expression to =DayOT and set it's RunningSum
property to Over Group.

Then the week footer text box can display the week OT total
by using the expression =txtRunOT

You can do essentially the same thing for the weeks total
regular hours. Don't forget to check this value for over 40
and add the excess to the week's OT amount.

To get the employee total, repeat the same kind of
arrangement with additional running sum text boxes in the
week footer.

 

[Guest]

Thanks for the tip, Marsh. That seems to work okay for getting me the weekly
totals (once I figure out how to add the check for OT). But I also need a
grand total for each employee.

I tried adding a text box in the Employee footer and tried setting the
Control Source to either =DayOT or =txtRunOT and set RunningSum to Over
Group. But neither of those worked. Any suggestions?

I have a report that I've created that is based on a query.

In my original database, I have the following information for my employees:

Employee ID, WorkDate, StartTime, EndTime, Category

In my query, I have computed the total # of hours a person works in a day
using the formula: Total = DateDiff("n",[StartTime],[EndTime])/60

Then in my report, I compute the number of regular and overtime hours an
employee works each day. Employees may work more than one "shift" in a day,
so some days may have more than one entry for an employee.

In the detail section of my report, I compute several fields. They are:

1) TotalHrs = [Total] --- summed over WorkDate in the WorkDate footer
-- This gives me a running total of the number of hours an employee has
worked on a given day.

2) OT =IIf([TotalHrs] Between 0 And 10,0,([TotalHrs]-10)) (Our company
operates on Flex Time where anything over 10 hours in a day or 40 hours in a
week is overtime)

3) Reg =[Total]-[OT]

That all works fine.

My report is set up so that I have several levels of grouping. First,
records are grouped by Employee, then by the workweek, then by the day.

In the footer section of WorkDay (when grouped by the day) I can get
accurate daily totals using the following computed fields:

1) DayTotal = Sum[Total] -- This gives me the total number of hours an
employee has worked on a given day summed over one or more entries.

2) DayOT =IIf([TotalHrs] Between 0 And 10,0,([TotalHrs]-10))

3) DayReg =[DayTotal]-[DayOT]

This all works fine too.

Here's the problem:

I also want to get weekly subtotals and grand totals for each employee for
the number of Reg and OT hours worked but can not figure out how to get them.

In my WorkDay footer section for grouping by week I can get a sum of the
total hours worked in a week (using =Sum([Total]) in the footer).

I can also get this sum for the Employee by putting the same value in the
Employee footer.

I CAN NOT get sub totals at the weekly and employee level for OT and Reg
hours, though.

What I want to do is sum DayOT and DayReg to compute WeekOT and WeekReg and
then sum these again at the employee level to get EmployeeOT and EmployeeReg.
But nothing I've tried will work.

Create an invisible text box named txtRunOT next to the
WorkDay footer DayOT text box. Set this text box's control
source expression to =DayOT and set it's RunningSum
property to Over Group.

Then the week footer text box can display the week OT total
by using the expression =txtRunOT

You can do essentially the same thing for the weeks total
regular hours. Don't forget to check this value for over 40
and add the excess to the week's OT amount.

To get the employee total, repeat the same kind of
arrangement with additional running sum text boxes in the
week footer.

 

[Guest]

Duhhh... Never mind. I tried your same logic again in the Employee footer
and now it seems to be working like a charm. Thank you so much for your
help, Marsh.

Thanks for the tip, Marsh. That seems to work okay for getting me the weekly
totals (once I figure out how to add the check for OT). But I also need a
grand total for each employee.

I tried adding a text box in the Employee footer and tried setting the
Control Source to either =DayOT or =txtRunOT and set RunningSum to Over
Group. But neither of those worked. Any suggestions?

I have a report that I've created that is based on a query.

In my original database, I have the following information for my employees:

Employee ID, WorkDate, StartTime, EndTime, Category

In my query, I have computed the total # of hours a person works in a day
using the formula: Total = DateDiff("n",[StartTime],[EndTime])/60

Then in my report, I compute the number of regular and overtime hours an
employee works each day. Employees may work more than one "shift" in a day,
so some days may have more than one entry for an employee.

In the detail section of my report, I compute several fields. They are:

1) TotalHrs = [Total] --- summed over WorkDate in the WorkDate footer
-- This gives me a running total of the number of hours an employee has
worked on a given day.

2) OT =IIf([TotalHrs] Between 0 And 10,0,([TotalHrs]-10)) (Our company
operates on Flex Time where anything over 10 hours in a day or 40 hours in a
week is overtime)

3) Reg =[Total]-[OT]

That all works fine.

My report is set up so that I have several levels of grouping. First,
records are grouped by Employee, then by the workweek, then by the day.

In the footer section of WorkDay (when grouped by the day) I can get
accurate daily totals using the following computed fields:

1) DayTotal = Sum[Total] -- This gives me the total number of hours an
employee has worked on a given day summed over one or more entries.

2) DayOT =IIf([TotalHrs] Between 0 And 10,0,([TotalHrs]-10))

3) DayReg =[DayTotal]-[DayOT]

This all works fine too.

Here's the problem:

I also want to get weekly subtotals and grand totals for each employee for
the number of Reg and OT hours worked but can not figure out how to get them.

In my WorkDay footer section for grouping by week I can get a sum of the
total hours worked in a week (using =Sum([Total]) in the footer).

I can also get this sum for the Employee by putting the same value in the
Employee footer.

I CAN NOT get sub totals at the weekly and employee level for OT and Reg
hours, though.

What I want to do is sum DayOT and DayReg to compute WeekOT and WeekReg and
then sum these again at the employee level to get EmployeeOT and EmployeeReg.
But nothing I've tried will work.

Create an invisible text box named txtRunOT next to the
WorkDay footer DayOT text box. Set this text box's control
source expression to =DayOT and set it's RunningSum
property to Over Group.

Then the week footer text box can display the week OT total
by using the expression =txtRunOT

You can do essentially the same thing for the weeks total
regular hours. Don't forget to check this value for over 40
and add the excess to the week's OT amount.

To get the employee total, repeat the same kind of
arrangement with additional running sum text boxes in the
week footer.