Sum function in report not working properly

[Nancy via AccessMonster.com]

I have a report that is separated alphabetically by employee. This report
totals the number of hours an employee worked during the entire week and
places the sum in the employee footer on the report. The control source is
=Sum([On Std Hours]). For one employee his total for the week should have
been 34.5 hours but it is showing up as 139.75 hours. Another shows 32 hours
but it should have been 25.5 hours. The data that the report is pulling from
is correct and there is obviously no error in the control source formula. Why
is my report totaling the wrong sum and what can I do to fix it?

 

[Nancy via AccessMonster.com]

Ok, to update this, I have discovered something very bizarre. I had the hours
text box hidden so I made it visible. This brought up a BUNCH of numbers that
are not located anywhere inside my database at all. For example: One employee
had 8.5, 9.5, 9.5 and 7 hours for this week. However, the data that is being
pulled up for his hours is 8.5, 9.5, 16.5, 26, 28, 38.5, 43.5, 44.5, 54, 65,
66, 68, 75, 80, 87.5, 98.5, 108.5, 119.5, 121.5, 122, 130.75, and 139.75. I
can't figure out any connection between these numbers. My report is based on
a query and there are no calculations or criteria at all in that query. What
could possibly be causing this problem? Any input at all will be appreciated!

I have a report that is separated alphabetically by employee. This report
totals the number of hours an employee worked during the entire week and
places the sum in the employee footer on the report. The control source is
=Sum([On Std Hours]). For one employee his total for the week should have
been 34.5 hours but it is showing up as 139.75 hours. Another shows 32 hours
but it should have been 25.5 hours. The data that the report is pulling from
is correct and there is obviously no error in the control source formula. Why
is my report totaling the wrong sum and what can I do to fix it?

 

[Dale Fye]

Nancy,

What does the SQL query look like?

Dale

Ok, to update this, I have discovered something very bizarre. I had the
hours
text box hidden so I made it visible. This brought up a BUNCH of numbers
that
are not located anywhere inside my database at all. For example: One
employee
had 8.5, 9.5, 9.5 and 7 hours for this week. However, the data that is
being
pulled up for his hours is 8.5, 9.5, 16.5, 26, 28, 38.5, 43.5, 44.5, 54,
65,
66, 68, 75, 80, 87.5, 98.5, 108.5, 119.5, 121.5, 122, 130.75, and 139.75.
I
can't figure out any connection between these numbers. My report is based
on
a query and there are no calculations or criteria at all in that query.
What
could possibly be causing this problem? Any input at all will be
appreciated!

I have a report that is separated alphabetically by employee. This report
totals the number of hours an employee worked during the entire week and
places the sum in the employee footer on the report. The control source is
=Sum([On Std Hours]). For one employee his total for the week should have
been 34.5 hours but it is showing up as 139.75 hours. Another shows 32
hours
but it should have been 25.5 hours. The data that the report is pulling
from
is correct and there is obviously no error in the control source formula.
Why
is my report totaling the wrong sum and what can I do to fix it?

 

[nlewallen via AccessMonster.com]

Thanks for responding! I've pasted my SQL query below. The field I am trying
to sum is called On Std Hours.

SELECT [Employee Data].[Clock Number] AS [Employee Data_Clock Number],
[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].Rate AS [Employee Data_Rate], [Employee Data].
Bonus AS [Employee Data_Bonus], [Employee Data].[Current Pay Rate] AS
[Employee Data_Current Pay Rate], [Weekly Data].Supervisor, [Weekly Data].
Date, [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], [Weekly Data].Day
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])
ORDER BY [Employee Data].Employee, [Operation Listing Data Field].[Op Number],
[Weekly Data].Supervisor, [Weekly Data].[Op Number] DESC;

Nancy,

What does the SQL query look like?

Dale

Ok, to update this, I have discovered something very bizarre. I had the
hours

[quoted text clipped - 26 lines]

 

[Nancy via AccessMonster.com]

Thanks for responding! I've pasted my SQL query below. The field I am trying
to sum is called On Std Hours.

SELECT [Employee Data].[Clock Number] AS [Employee Data_Clock Number],
[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].Rate AS [Employee Data_Rate], [Employee Data].
Bonus AS [Employee Data_Bonus], [Employee Data].[Current Pay Rate] AS
[Employee Data_Current Pay Rate], [Weekly Data].Supervisor, [Weekly Data].
Date, [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], [Weekly Data].Day
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])
ORDER BY [Employee Data].Employee, [Operation Listing Data Field].[Op Number],
[Weekly Data].Supervisor, [Weekly Data].[Op Number] DESC;

Nancy,

What does the SQL query look like?

Dale

Ok, to update this, I have discovered something very bizarre. I had the
hours

[quoted text clipped - 26 lines]

 

[Nancy via AccessMonster.com]

Well, I'm not sure what the problem was but I just created a whole new report
and that fixed it. Thanks for trying to help!

Thanks for responding! I've pasted my SQL query below. The field I am trying
to sum is called On Std Hours.

SELECT [Employee Data].[Clock Number] AS [Employee Data_Clock Number],
[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].Rate AS [Employee Data_Rate], [Employee Data].
Bonus AS [Employee Data_Bonus], [Employee Data].[Current Pay Rate] AS
[Employee Data_Current Pay Rate], [Weekly Data].Supervisor, [Weekly Data].
Date, [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], [Weekly Data].Day
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])
ORDER BY [Employee Data].Employee, [Operation Listing Data Field].[Op Number],
[Weekly Data].Supervisor, [Weekly Data].[Op Number] DESC;

Nancy,

[quoted text clipped - 7 lines]