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/Forums.aspx/access-reports/201001/1

0
Nancy
1/15/2010 3:22:55 PM
access.reports 4434 articles. 0 followers. Follow

5 Replies
1833 Views

Similar Articles

[PageSpeed] 15

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/Forums.aspx/access-reports/201001/1

0
Nancy
1/15/2010 5:11:30 PM
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/Forums.aspx/access-reports/201001/1
> 


0
Dale
1/15/2010 6:04:33 PM
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/Forums.aspx/access-reports/201001/1

0
nlewallen
1/15/2010 6:31:03 PM
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/Forums.aspx/access-reports/201001/1

0
Nancy
1/15/2010 6:31:13 PM
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

0
Nancy
1/15/2010 7:50:06 PM
Reply:

Similar Artilces:

export report from access to excel
I have a report in access that I've tried 2 different ways to work with in excel. I've exported it and I've also used the analyze with excel option. Both come over to excel the same way. What is happening is that the fields in the report are not in the same order in the excel workbook as they are in the access report. Does anyone have any idea what happened? Or how I can get them to be the same? Thanks. Mary ...

Sum and Count are driving me nuts!!
I am trying to make some radar charts that have more than 30 criteria. My data contains numbers between 0 and 1. Each time that I add each criteria to the data field, it automatically comes up as Count...I can switch each one to Sum (which is a pain) but then each data point is labeled Sum of something. Ideally, I would automatically have Sum results that are labeled with by my choosing. Example. I would like to have a typical data point be labeled Total Portfolio, I am stuck as excel automatically gives me Count of Total Portfolio which I then have to Switch to Sum of Total Portfol...

Budgeted Amounts Not Correct in Report
I hope y'all can help me. In my monthly budget report, I have the dates set from 6/30/03 to 6/30/04 (12 months). There are a few categories that show a higher budgeted amount than there should be. For instance, my cable bill is $59 every month. Under the "actual" spending column, the amount spent is $708 (correct-- $59 x 12 = $708). Under the "budgeted" spending column, the amount is $767 (not correct; it should be $708). There are several places in my budget report where it's calculating for 13 months instead of 12. Also, along the same lines, I hav...

outgoing email stopped working
My outgoing email stopped sending yesterday, although I can still receive email fine. I've been through text support with my ISP (prodigy/SBC) and they say it is an outlook problem. Any suggestions? Julie Fairchild What errors do you get? Julie wrote: > My outgoing email stopped sending yesterday, although I > can still receive email fine. I've been through text > support with my ISP (prodigy/SBC) and they say it is an > outlook problem. Any suggestions? > > Julie Fairchild >-----Original Message----- >My outgoing email stopped sending yesterday, a...

Assign Macro to button in Excel doesnt work Any ideas?
I have followed the instructions in help but when I get to step 3 "right click to bring up shortcut menu & enter Assign Macro" the assign macro" option does not appear!! Any Ideas Hi Mike can you see assign macro - but it is greyed out? or is it not an option at all? if it is greyed out you'll notice around the button little diagonal lines, click on these and they should go furryish .. now try right mouse clicking. if the option is not there at all, please post back letting us know how you created the button. Cheers JulieD "Mike@Becketts" <Mike@B...

Upgrade 9 to 10 only work if update instance, not if new instance
I'm testing a GP 9 to 10 upgraded on a test server. Everything installs; however, GP Utilities will bomb out if I created a new instance when installing 10. There is no error message, it just fails. If I do an update to the 9 instance, it works fine. Why should this make any difference? It's the only thing I'm changing in the two installs. Thanks Not sure I understand. What do you mean by "instance"? Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster...

how I can sum or subtract 2 or 3 field in access in any record
hi I want know that why there is NOT any relationship between access and excel and how I can sum and subtract 2 or 3 filds in access in any record thanks hadi_khodaparast@yahoo.com Hello Hadi - I'm not sure I understand exactly what you are asking, but Excel is *not* a 'front end' for an Access database. You can use Excel to query the db & copy Access data into a workbook in several ways. That copy of the data can be used for calculations, but the data *doesn't* get changed in the Access file. Although the modified results can be imported to Access it may very well be t...

add functionality to RMS calendar
the calendar is a good function to have. Expanded functionality would be great. Design it as a watered down version of Outlook - set individual appointments that can be assigned to individual customers. That way you can search the calendar to see what appointments individual customers have scheduled. Being able to set up recurring schedules would be helpful, too. If it doesn't make sense to expand the calendar functionality, then create functionality to synchronize customer list in RMS with Outlook. FRB ---------------- This post is a suggestion for Microsoft, and Microsoft resp...

Group by & Sum
Good afternoon, I’m trying to sum and group data in a query. Right now my query looks like this (example below) multiple PERL entries Account JobType Volume Count Average Page Total Postage PERL Bill 12345 1.69 134.55 22845 193 325 PERL Bill 78963 96.1 554.31 54822 391 523 PERL Letters 67890 4.54 98.25 26475 257 446 PERL Fin State 123435 7.39 61.95 30105 321 567 PERL PostC 178980 10.24 25.65 33735 385 688 PERL NCO 234525 13.09 10.65 37365 449 809 PERL CAN 290070 15.94 46.95 40995 513 930 PERL Mec 345615 18.79 83.25 44625 577 1051 PERL Fld Ply 401160 21.64 119.55 48255 641 1172 PER...

Aggregate Functions
Hi I'm looking for the best way to implement a Last and First function on group queries: This is an example table mytable p_id dattime xval --------------------------------------------------- 22 2010-04-12 00:00:00 20 22 2010-04-12 01:00:00 30 22 2010-04-12 03:00:00 15 22 2010-04-12 07:00:00 25 22 2010-04-12 23:00:00 22 With this query I get sum, maximum and minimum values in the period: select p_id, max(xval) as max_value, min(xval) as min_value, sum(xval) as tot_value from mytable where p_id = 22 and date...

On Create Workflow not working
I am trying to add a new workflow rule to our system. All I want to do is send an email when a new order is created, that's it. I have many other, much more complex workflows running just fine. However, it seems I can no longer add an On Create workflow rule. If I change this rule to On Assign, it runs fine. For some reason, any new On create rules are not running, the old On create rules still work. Does anyone know what this could be? Any help is appreciated. This is only affecting the Creation of workflow rules for Orders. Is there a maximum # of rules allowed for each...

Outlook 2007
We are implementing Outlook 2007 - Exchange 2010 across the company and we are noting that while we can open and save attachments directly from the email in either preview pane or full view (by right clicking and saying "Save As"), the Save Attachment button does not work in the Email Toolbar. Having put the Save Attachment button in my quick access toolbar, I note that it does not work either when I open the full Email. I am thinking this might be related to the Exchange/Outlook relationship. Our exchange server in on our LAN and I have my connection set to "Dow...

'Average' Function on 1500 lines
I'm creating a price list and need to have an average price list out of 6 prices on the end end of each line for about 1500 lines. Individually doing this would take a long time, any tips? -- Logan ------------------------------------------------------------------------ Logan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23729 View this thread: http://www.excelforum.com/showthread.php?threadid=374027 Assuming Columns A through F contain your prices, try... G1, copied down: =AVERAGE(A1:F1) Hope this helps! -- Domenic ----------------------------...

No reports in CRM
Hi all, We upgraded a client from 1.2 to 3. All worked well but only problem was that e-mail was not sending from web client. In an attempt to fix this the CRM was un-installed and re-installed. Since this time there is no reports in CRM. After further investigation I have found that http://server/reports works fine but does not show any reports. I have looked in c:\program files\Microsoft CRM\repots etc etc and the reports are in this folder. How do I get the reports into SRS and linking back to the CRM sucessfully???? Any help appreciated. Cheers, Nathan Warner Hi Nathan, Yo...

Sending a report to word then mailing it.
is there a way to send a report by email as a word doc. i have messed about with the button wizard and managed to get the report to attach to an email. the only thing is it only attached as a rtf, txt or html file. and these do not show the report as it appears on access. how can i do it, so the recipient can print it off sa it is on my screen. Cheers To get the exact same report you need to export to Snapshot Format (viewer & add-in Downloadable from microsoft) Pieter "Steve Moss" <SteveMoss@discussions.microsoft.com> wrote in message news:ED6150E8-C397-4D2F-...

Sales Batch Posting Reports
When I post a Sales Batch there is a series of 6 or 7 reports that are generated each time. The problem I have is that each report prints to the screen first. Is there a way to not have the reports print to the screen? Thanks JDR, Go to Tools >> Setup >> Posting >> Posting, from series chose the “Sales” and from origin chose “Sales Transaction Entry” a list of reports will be shown in the grid at the bottom of the form, you can easily control the reports destinations. Regards, -- Mohammad R. Daoud MCP, MCBMSP, MCTS, MCBMSS Mob: +962 - 79 -999 65 85 Great Package...

Hide Sub Report(s) when records are null
I am running Access 2007 sp2 MSO. I have a report that has three (3) sub reports. There are times when not all three sub reports have values to display. Is there a way to code the given report to not be visible if there are no records to report? The second part of this question would be if there is a way to make the report invisible is it also possible to code so that the space where the report would normally reside would not be taken with a blank space (the foot print of where the report normally resides)? -- Bruce ...

SUM for logical values
Using Excel 2002 SP3... If I type logical values into the formula, like this: =SUM(TRUE, TRUE, TRUE), my formula returns the value 3. If I reference cells containing logical values, like this =SUM(A1:A3), my formula returns 0. Does anybody know why there is a difference? -- tj Try: =A1+A2+A3 -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tjtjjtjt" <tjtjjtjt@discussions.microsoft.c...

Money Import Handler doesn't work anymore in Money Deluxe 2006
It used to, for years, but now whenever I download an account statement, whether from my bank or from a credit card account, the statement gets downloaded, apparently, but Money doesn't do anything with it. If I save the downloaded .OFX file, instead of choosing the Money Import Handler, I can then go to File - Import - Download Statement and Money uses the data correctly. Under Tools - Settings - Online Service Settings, the following options are chosen: Overwrite transaction dates... Replace downloaded payee name... Automatically mark accepted transactions... Enter electronic pay...

Making Taskpad work for me
I just started using the Calendar and Taskpad functions for my Outlook 2000. When I do a printout of my oneday view, I'm frustrated because it lists all of the tasks, not just the ones that are due that day. I can't figure out how to either change the view so it only shows the tasks due that day, or to create a view that doesn't show the tasks at all. I would also appreciate anyone's opinion on what benefits I would gain by upgrading to a new version of Outlook. Thanks, Dave ...

Why doesn't the tools/options work in Excel?
About a month ago I was not able to get to the "Options" screen under tools. I still can't even after reloading and repairing Office. I can get to the "options" under Word but not Excel. I am using Office 2003. Help me get it so that I can get to Tools/Options. Is it gone or what happens when you try to open options? If it's gone, do view>toolbars>customize. select commands, scroll to toolbar and select in the left pane, in the right pane scroll to options (at end) then drag it to its place. You could try to rename *xlb to something like *.bak res...

IMAP not working for old users
Hi, I am trying to use IMAP to log in to Exchange 2003. IMAP is running, as I can connect. However, I get "unknown user name or bad password", even though I'm certain that the login/password is correct. I am able to connect using the same login/password via OWA. I've tried the following combinations for logins: LOGIN DOMAIN/LOGIN DOMAIN/LOGIN/LOGIN DOMAIN/LOGIN/Inbox I get the same failed authentication in all cases (for all tested old users). I'm sure IMAP is active for this user. But, When I create a NEW user login is succefful ! (or r...

Weird characters and missing Calculated Fields
Could you please advice me how to fix the 2 problems in GP10 Report Writer. I have 2 modified reports -- one of them the Calculated Fields are missing, and the other modified report where the Calculated fields' name have weird characters. Please find the below links show the 2 screenshots of the 2 problem issues. http://img34.imageshack.us/img34/4149/20091005missingcalculat.png http://img34.imageshack.us/img34/6564/20091002specialcharacte.png Did this happen as part of an update? If you export the report(s) to a package file and then open it up in Notepad, are the fields still missing...

Best way to webcam PC to MAC? Does WLM 2009 work?
Hi. I'm a pc user, and have been using WLM for years and like it (although the video quality could be a little better). Anyway..a friend may be getting a macbook pro soon and I was hoping to webcam with him. What is the best way to webcam (video and audio) between my pc and his mac? Can we use WLM? thebigdintexas wrote: > Hi. I'm a pc user, and have been using WLM for years and like it (although > the video quality could be a little better). Anyway..a friend may be getting > a macbook pro soon and I was hoping to webcam with him. What is the best way >...

Copied graphs not working
I had a workbook of data with a number of graphs in one worksheet; each graph series was populated by a named range (A_, B_, C_, D_, etc.). A_ was the only direct (actually, indirect) reference, and the other series are all offsets of A_ I had some new data in a new workbook, so I used the worksheet/copy/move functionality to make a copy of the worksheet that contains the graphs in my new workbook. I copied over the named ranges and re-set them in my new workbook to the new data areas (and verified that they accurately point to ranges of data- which they do). However, the 'new'...