summing fields

[brh01]

I have an equation for totaling numbers in a field.


=sum([totalsap])

but what should add to 170.5, adds to 313.

am i using the right equation?


Brian

 

[Blorf]

Is the sum in a group footer?

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[brh01]

report footer

Brian

 

[Blorf]

Are there any groups at all? Any chance that you sum you want isn't a grand total, but a group total?

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[brh01]

no groups at all. 1 list and one total that's supposed to total one field.

Brian

 

[Blorf]

Bizzare.

The field you are summing on, is it the data name, or the name of the control on the report? If different, make it the control name, see if that works.

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[brh01]

it,s the control source name. is that what you mean?

Brian

 

[Blorf]

Yes.

The field is say Sales, the control can be anything.

Make the sum field sum the control name.

Probably won't make a difference, but maybe.

Also, are you sure the data type is numeric?

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[brh01]

didn,t work. general numbers. is there another equation you know that might work better?

Brian

 

[Blorf]

No. Sum should work.

You set the control source to say =sum([FieldName]), and you are not using dsum or anything else?

Your report is based on a query? Maybe there is something there, can you post the sql?




"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[TerryEA]

brh01

Can't see why Blorf's post doesn't work but you could try putting an invisible text box (named say Text1)on the detail section of the report,with it's control source set to: =[fieldname] and its running sum property set to "Over all". Then put a text box in the report footer with source set to =Text1. Works OK on some reports I use in my database. I've totalled Time, Numeric and Currency data this way.

Tel

 

[dhookom]

The easiest method of testing this is to open the report's record source in datasheet view so you can see all the records. Select them all and paste them into Excel. Then use Excel to sum the same column as you "think" you are getting wrong in the report. I have never found the Excel result different from the Access result other than some very minor rounding issues.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[brh01]

ok I tried excel. the pasted answer totaled 170.5, the correct answer. the column I think i'm getting wrong is the only column w/ #'s in the datasheet view. in access it still reads, 313.

Ive had this problem in access before and used the 'invisible box' method to make it work. thanks for the suggestion TerryEA. I'm just trying to understand why it doesn't work when it's supposed to using the "normal" method.

Has anyone had this problem before?

Brian

 

[dhookom]

Brian,
I have created hundreds of reports and have never seen one instance where
=Sum([FieldA])
did not accurately sum all values of FieldA. I assume you don't have any code acting on the sum. Are all values positive? Are you summing a value from a group heading?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[TerryEA]

Brian

You've got us stumped. The only other things I can think of are:

1) the possibility that the report is perhaps based upon a query and that there might be something we are not aware of in the query. If so you could post the details?

2) one of the records contains some odd data in the field in question. How many records are we talking about?

3) is there a conflict or duplication of field names, perhaps involving some other table or query?

4) is there any other code involved in the report, say in the On Format or On Print events?

Tel

 

[Blorf]

Also, this is a report only, not a sub report, or a report with sub reports?

Any chance that the link on data is causing ambiguity? Never heard of such a thing, but never heard of this problem either.

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[brh01]

how do i attach the file on this site? or can I email it directly?

Brian

 

[Blorf]

I don't know. I was asked by one of the admin folks on tek-tips not to post my e-mail address. I am not sure about an alternative.

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[dhookom]

You can find my email at any of my downloads at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

If you want to email me any file, you must compact and then zip it. I would prefer to get a slimmed down version that has been compacted.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[storl2]

I'm not sure if this has already been mentioned, but are you pulling data from more than one table/query for the query that the report uses as a source? You could have an instance where the two tables are creating more than one unique record for each row in your report. Say, for instance, that you have a report by month listing total department hours worked and paychecks paid. The query pulls from two tables, one that shows each person's hours, and another that shows total paychecks paid per month.

Your report would show:

Month Total Hours Paychecks Paid
1 100 1000
2 100 1000
3 100 1000

But, since you are pulling from two tables, and you enter paychecks paid as a total for the month, but add sum the hours from several rows in a table, your data in the query shows something more along the lines of:

Month Total Hours Paychecks Paid
1 20 1000
1 40 1000
1 40 1000
2 20 1000
2 40 1000
2 40 1000
3 40 1000
3 40 1000
3 20 1000

I had a very similar issue yesterday, and even though the report showed numbers that should add to, in this case, $3000, the sum/runningsum would add up to $9000. I had to split up the queries, total them seperately, and then use another query to bring them together. I don't know if this is what you have, but I hope it helps.