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 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 |
#2
|
|||
|
|||
Sum Query Incorrect
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! |
#3
|
|||
|
|||
Sum Query Incorrect
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 |
#4
|
|||
|
|||
Sum Query Incorrect
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 |
#5
|
|||
|
|||
Sum Query Incorrect
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 |
#6
|
|||
|
|||
Sum Query Incorrect
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 |
#7
|
|||
|
|||
Sum Query Incorrect
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 |
#8
|
|||
|
|||
Sum Query Incorrect
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 |
Thread Tools | |
Display Modes | |
|
|