Sum Query Incorrect

Hello Everyone,

I am running a simple query that sums the values of multiple fields and it is
leading to some very wacky results. Please see below my sample table below.

FieldNameA FieldNameB FieldNameC
John 5 0

Next I create a new query, bring in the above table. I leverage a group by,
and in the total field for "SumOfFieldBandC" I select SUM rather than the
default group by.


FieldNameA SumOfFieldBandC[FieldNameB]+[FieldNameC])

This query is incorrectly returning

FieldNameA SumOfFieldBandC
John 50

Where "SumOfFieldBandC" should return "5", what gives?

Thanks for your help!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200807/1

Access is understanding the values as Text, and therefore concatenating the
text together instead of treating them as numeric values and performing
math.

The solution will be to find out why it thinks they are text, and make sure
it understands them as numbers. Here's an example:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"NeonSky via AccessMonster.com" u23580@uwe wrote in message
news:86f2b4063b8ef@uwe...
Hello Everyone,

I am running a simple query that sums the values of multiple fields and it
is
leading to some very wacky results. Please see below my sample table
below.

FieldNameA FieldNameB FieldNameC
John 5 0

Next I create a new query, bring in the above table. I leverage a group
by,
and in the total field for "SumOfFieldBandC" I select SUM rather than the
default group by.


FieldNameA SumOfFieldBandC[FieldNameB]+[FieldNameC])

This query is incorrectly returning

FieldNameA SumOfFieldBandC
John 50

Where "SumOfFieldBandC" should return "5", what gives?

Thanks for your help!

Hello Allen, Thanks for your response. Not sure what to do to make the below
work, there are functions mentioned on the link you provided though I am not
sure which is applicable. Thank you for your time and consideration.

Sum(([FieldB]+[FieldC])) AS SumOfFieldBandC

Allen Browne wrote:
Access is understanding the values as Text, and therefore concatenating the
text together instead of treating them as numeric values and performing
math.

The solution will be to find out why it thinks they are text, and make sure
it understands them as numbers. Here's an example:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Hello Everyone,

[quoted text clipped - 21 lines]

Thanks for your help!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200807/1

I attempted the below to the same results....

Sum (CInt(([FieldB]+[FieldC]))) AS SumOfFieldBandC

NeonSky wrote:
Hello Allen, Thanks for your response. Not sure what to do to make the below
work, there are functions mentioned on the link you provided though I am not
sure which is applicable. Thank you for your time and consideration.

Sum(([FieldB]+[FieldC])) AS SumOfFieldBandC

Access is understanding the values as Text, and therefore concatenating the
text together instead of treating them as numeric values and performing
[quoted text clipped - 11 lines]

Thanks for your help!

--
Message posted via http://www.accessmonster.com

It seems that your fieldNameB and FieldNameC data type are STRING rather
than values:

? " hello " + " world "
hello world

? "5" + "0"
50

? 5 + 0
5


Note that I am surprised that SUM( string expression ) does not returns an
error, though.


Vanderghast, Access MVP


"NeonSky via AccessMonster.com" u23580@uwe wrote in message
news:86f2b4063b8ef@uwe...
Hello Everyone,

I am running a simple query that sums the values of multiple fields and it
is
leading to some very wacky results. Please see below my sample table
below.

FieldNameA FieldNameB FieldNameC
John 5 0

Next I create a new query, bring in the above table. I leverage a group
by,
and in the total field for "SumOfFieldBandC" I select SUM rather than the
default group by.


FieldNameA SumOfFieldBandC[FieldNameB]+[FieldNameC])

This query is incorrectly returning

FieldNameA SumOfFieldBandC
John 50

Where "SumOfFieldBandC" should return "5", what gives?

Thanks for your help!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200807/1

Try:

SUM( val( fieldb) + val( fieldc) ) AS sumOfFieldBandC


Your last solution was trying to convert to an integer AFTER the
concatenation, CInt( "50" ) is 50. You could have use CInt(fieldb) +
CInt(fieldC), though.


Vanderghast, Access MVP


"NeonSky via AccessMonster.com" u23580@uwe wrote in message
news:86f2f048c22d6@uwe...
I attempted the below to the same results....

Sum (CInt(([FieldB]+[FieldC]))) AS SumOfFieldBandC

NeonSky wrote:
Hello Allen, Thanks for your response. Not sure what to do to make the
below
work, there are functions mentioned on the link you provided though I am
not
sure which is applicable. Thank you for your time and consideration.

Sum(([FieldB]+[FieldC])) AS SumOfFieldBandC

Access is understanding the values as Text, and therefore concatenating
the
text together instead of treating them as numeric values and performing
[quoted text clipped - 11 lines]

Thanks for your help!

--
Message posted via http://www.accessmonster.com

Hello Michel, Not sure if you remember me though I remember you, you helped
me with a rather lengthy process I was working on last December. As always
thanks for being so helpful/informative. I appreciate it.

Michel Walsh wrote:
It seems that your fieldNameB and FieldNameC data type are STRING rather
than values:

? " hello " + " world "
hello world

? "5" + "0"
50

? 5 + 0
5

Note that I am surprised that SUM( string expression ) does not returns an
error, though.

Vanderghast, Access MVP

Hello Everyone,

[quoted text clipped - 21 lines]

Thanks for your help!

--
Message posted via http://www.accessmonster.com

What is FieldB?

Is it a field from a table?If so, open the table in design view and change
it from a Text field to a Number field.

Is it a calculated field in a query? If so, post the expression. Something
in the expression is causing Access to treat it as text. You can probably
verify that by opening that query, and seeing if JET left-aligns the column
(like text) or right-aligns it (as a number.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"NeonSky via AccessMonster.com" u23580@uwe wrote in message
news:86f2e9657e293@uwe...
Hello Allen, Thanks for your response. Not sure what to do to make the
below
work, there are functions mentioned on the link you provided though I am
not
sure which is applicable. Thank you for your time and consideration.

Sum(([FieldB]+[FieldC])) AS SumOfFieldBandC

Allen Browne wrote:
Access is understanding the values as Text, and therefore concatenating
the
text together instead of treating them as numeric values and performing
math.

The solution will be to find out why it thinks they are text, and make
sure
it understands them as numbers. Here's an example:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html