A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Report Grand Total Miscalculating Overtime



 
 
Thread Tools Display Modes
  #1  
Old April 19th, 2010, 09:21 PM posted to microsoft.public.access.reports
Nancy via AccessMonster.com
external usenet poster
 
Posts: 27
Default Report Grand Total Miscalculating Overtime

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/For...ports/201004/1

  #2  
Old April 20th, 2010, 02:46 AM posted to microsoft.public.access.reports
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default Report Grand Total Miscalculating Overtime

...."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@uwe wrote in message
news:a6c7828633083@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/For...ports/201004/1

  #3  
Old April 20th, 2010, 01:10 PM posted to microsoft.public.access.reports
Nancy via AccessMonster.com
external usenet poster
 
Posts: 27
Default Report Grand Total Miscalculating Overtime

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

  #4  
Old April 20th, 2010, 02:05 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Report Grand Total Miscalculating Overtime

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

.

  #5  
Old April 20th, 2010, 02:06 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default Report Grand Total Miscalculating Overtime

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!


  #6  
Old April 20th, 2010, 03:39 PM posted to microsoft.public.access.reports
Nancy via AccessMonster.com
external usenet poster
 
Posts: 1
Default Report Grand Total Miscalculating Overtime

Thank you both for taking the time to help me. It seems that I've run in to
the real problem and maybe one of you could help me with it. It seems that
I'm having problems because I am trying to do a Sum of a Sum in the report
footer. So, I created an expression in the query that my report is based on
to do the Sum for me. However, when I add the totals option to the query and
select sum underneath the On and Off Std Hours, it doesn't sum it up for me
in the report. It just shows the last entry. So, I tried to manually create
it by placing the following in a new field:

On Sum: Sum([On Std Hours])

When I try to run that I receive the following message:

You tried to execute a query that does not include the specified expression
'Supervisor' as part of an aggregate function.

I can select for the Supervisor field to not be shown and the error message
returns with the next field name in line. I've included my SQL code below.
Thanks in advance for any help you can offer. This is driving me insane!

SELECT [Weekly Data].Supervisor, [Weekly Data].Date, [Weekly Data].Day,
[Employee Data].[Current Pay Rate], [Employee Data].[Clock Number] AS
[Employee Data_Clock Number], [Weekly Data].[Job Change], [Weekly Data].
[Contract Number], [Employee Data].Employee AS [Employee Data_Employee],
[Operation Listing Data Field].[Unit Hour], [Operation Listing Data Field].
[Hour Unit], [Operation Listing Data Field].Rate, [Operation Listing Data
Field].Description, [Operation Listing Data Field].[Op Number], [Employee
Data].[Pay Rate], [Employee Data].Bonus AS [Employee Data_Bonus], [Employee
Data].[Current Pay Rate] AS [Employee Data_Current Pay Rate], [Weekly Data].
[Clock Number] AS [Weekly Data_Clock Number], [Weekly Data].Employee AS
[Weekly Data_Employee], [Weekly Data].[Hourly Rate], [Weekly Data].Bonus AS
[Weekly Data_Bonus], [Weekly Data].[Current Pay Rate] AS [Weekly Data_Current
Pay Rate], [Weekly Data].[Op Number], [Weekly Data].Description, [Weekly Data]
.Code, [Weekly Data].Contract, [Weekly Data].Rate AS [Weekly Data_Rate],
[Weekly Data].Quantity, [Weekly Data].[On Std Hours], [Weekly Data].[Off Std
Hours], Sum([On Std Hours]) AS [On Sum], Sum([Off Std Hours]) AS [Off Sum]
FROM [Operation Listing Data Field] INNER JOIN ([Employee Data] INNER JOIN
[Weekly Data] ON [Employee Data].[Clock Number] = [Weekly Data].[Clock Number]
) ON ([Operation Listing Data Field].[Op Number] = [Weekly Data].[Op Number])
AND ([Operation Listing Data Field].[Op Number] = [Weekly Data].[Op Number]);


John Spencer wrote:
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

Sorry, I should have explained better. I use the following formula in the
report footer

[quoted text clipped - 26 lines]
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/For...ports/201004/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.