Report Grand Total Miscalculating Overtime
in [Reports]
|
From: Nancy via AccessMonster.com on 19 Apr 2010 16:21 I have a report based on a query that uses the following calculation in a text box to calculate the total amount paid (including overtime) to an employee: =([On Std Hours]+[Off Std Hours] + IIF([On Std Hours]+[Off Std Hours]>40, .5* ([On Std Hours]+[Off Std Hours]-40), 0)) * [Employee Data_Rate] This formula works perfectly to total individual weekly totals. However, whenever I use the same calculation in the report footer to calculate the grand total which sums up every employee for the week, the grand total is a lot higher than it should be. For example, I hand computed the total amount paid for a week and got 30,667.74 and the report is showing 50,951.88. I'm at a total loss of what could be causing this. Any help would be greatly appreciated! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201004/1
From: Duane Hookom on 19 Apr 2010 21:46 ...."whenever I use the same calculation in the report footer"... Can you share the expression you tried? Was it: =Sum(([On Std Hours]+[Off Std Hours] + IIF([On Std Hours]+[Off Std Hours]>40, .5* ([On Std Hours]+[Off Std Hours]-40), 0)) * [Employee Data_Rate]) -- Duane Hookom MS Access MVP "Nancy via AccessMonster.com" <u57097(a)uwe> wrote in message news:a6c7828633083(a)uwe... > I have a report based on a query that uses the following calculation in a > text box to calculate the total amount paid (including overtime) to an > employee: > > =([On Std Hours]+[Off Std Hours] + IIF([On Std Hours]+[Off Std Hours]>40, > .5* > ([On Std Hours]+[Off Std Hours]-40), 0)) * [Employee Data_Rate] > > This formula works perfectly to total individual weekly totals. However, > whenever I use the same calculation in the report footer to calculate the > grand total which sums up every employee for the week, the grand total is > a > lot higher than it should be. For example, I hand computed the total > amount > paid for a week and got 30,667.74 and the report is showing 50,951.88. I'm > at > a total loss of what could be causing this. Any help would be greatly > appreciated! > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201004/1 >
From: Nancy via AccessMonster.com on 20 Apr 2010 08:10 Sorry, I should have explained better. I use the following formula in the report footer =Sum(([On Std Hours Totals]+[Off Std Hours Totals] + IIF([On Std Hours Totals] +[Off Std >Hours Totals]>40, .5* ([On Std Hours Totals]+[Off Std Hours Totals]-40), 0)) * [Employee >Data_Rate]) The On and Off Std Hours Totals refers to text boxes in the report footer that has the following formulas: =Sum([On Std Hours]) =Sum([Off Std Hours]) I know this is the problem because the formula is calculating the total on and off standard hours in the report footer instead of just summing up the total that was paid. But I can't do a sum of the total paid because it includes a sum. I've tried using a query as well but it won't work either. Duane Hookom wrote: >..."whenever I use the same calculation in the report footer"... >Can you share the expression you tried? Was it: >=Sum(([On Std Hours]+[Off Std Hours] + IIF([On Std Hours]+[Off Std >Hours]>40, .5* ([On Std Hours]+[Off Std Hours]-40), 0)) * [Employee >Data_Rate]) > >> I have a report based on a query that uses the following calculation in a >> text box to calculate the total amount paid (including overtime) to an >[quoted text clipped - 14 lines] >> a total loss of what could be causing this. Any help would be greatly >> appreciated! -- Message posted via http://www.accessmonster.com
From: Duane Hookom on 20 Apr 2010 09:05 The initial expression you provided is apparently a group total. If this is the case, you can probably duplicate the text box in the footer and set its Running Sum to Over All. Give it a name like "txtHoursRunSum". Then add a text box in the report footer with a control source of: =txtHoursRunSum -- Duane Hookom Microsoft Access MVP "Nancy via AccessMonster.com" wrote: > Sorry, I should have explained better. I use the following formula in the > report footer > > =Sum(([On Std Hours Totals]+[Off Std Hours Totals] + IIF([On Std Hours Totals] > +[Off Std > >Hours Totals]>40, .5* ([On Std Hours Totals]+[Off Std Hours Totals]-40), 0)) * [Employee > >Data_Rate]) > > The On and Off Std Hours Totals refers to text boxes in the report footer > that has the following formulas: > > =Sum([On Std Hours]) > =Sum([Off Std Hours]) > > I know this is the problem because the formula is calculating the total on > and off standard hours in the report footer instead of just summing up the > total that was paid. But I can't do a sum of the total paid because it > includes a sum. I've tried using a query as well but it won't work either. > > Duane Hookom wrote: > >..."whenever I use the same calculation in the report footer"... > >Can you share the expression you tried? Was it: > >=Sum(([On Std Hours]+[Off Std Hours] + IIF([On Std Hours]+[Off Std > >Hours]>40, .5* ([On Std Hours]+[Off Std Hours]-40), 0)) * [Employee > >Data_Rate]) > > > >> I have a report based on a query that uses the following calculation in a > >> text box to calculate the total amount paid (including overtime) to an > >[quoted text clipped - 14 lines] > >> a total loss of what could be causing this. Any help would be greatly > >> appreciated! > > -- > Message posted via http://www.accessmonster.com > > . >
From: John Spencer on 20 Apr 2010 09:06
You should probably use the exact same formula in the report footer as you are using elsewhere. Assuming that you are referencing fields in the report's record source, I would expect to see the following. =SUM(([On Std Hours]+[Off Std Hours] + IIF([On Std Hours]+[Off Std Hours]>40, ..5* ([On Std Hours]+[Off Std Hours]-40), 0)) * [Employee Data_Rate]) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Nancy via AccessMonster.com wrote: > Sorry, I should have explained better. I use the following formula in the > report footer > > =Sum(([On Std Hours Totals]+[Off Std Hours Totals] + IIF([On Std Hours Totals] > +[Off Std >> Hours Totals]>40, .5* ([On Std Hours Totals]+[Off Std Hours Totals]-40), 0)) * [Employee >> Data_Rate]) > > The On and Off Std Hours Totals refers to text boxes in the report footer > that has the following formulas: > > =Sum([On Std Hours]) > =Sum([Off Std Hours]) > > I know this is the problem because the formula is calculating the total on > and off standard hours in the report footer instead of just summing up the > total that was paid. But I can't do a sum of the total paid because it > includes a sum. I've tried using a query as well but it won't work either. > > Duane Hookom wrote: >> ..."whenever I use the same calculation in the report footer"... >> Can you share the expression you tried? Was it: >> =Sum(([On Std Hours]+[Off Std Hours] + IIF([On Std Hours]+[Off Std >> Hours]>40, .5* ([On Std Hours]+[Off Std Hours]-40), 0)) * [Employee >> Data_Rate]) >> >>> I have a report based on a query that uses the following calculation in a >>> text box to calculate the total amount paid (including overtime) to an >> [quoted text clipped - 14 lines] >>> a total loss of what could be causing this. Any help would be greatly >>> appreciated! > |