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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sum function in report not working properly
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? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201001/1 |
#2
|
|||
|
|||
Sum function in report not working properly
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! Nancy wrote: 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? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201001/1 |
#3
|
|||
|
|||
Sum function in report not working properly
Nancy,
What does the SQL query look like? Dale "Nancy via AccessMonster.com" u57097@uwe wrote in message news:a227ffd978a1d@uwe... 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! Nancy wrote: 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? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201001/1 |
#4
|
|||
|
|||
Sum function in report not working properly
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; Dale Fye wrote: 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] Why is my report totaling the wrong sum and what can I do to fix it? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201001/1 |
#5
|
|||
|
|||
Sum function in report not working properly
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; Dale Fye wrote: 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] Why is my report totaling the wrong sum and what can I do to fix it? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201001/1 |
#6
|
|||
|
|||
Sum function in report not working properly
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! Nancy wrote: 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] Why is my report totaling the wrong sum and what can I do to fix it? -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|