Total fields in a row

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Query which is calculating 10 different values in a record. I need
to then sum these 10 values within each row. When I attempt to total them, I
get a concatenation instead of a summed total. Here's my formula:
TotalCorrect:
[Score1]+[Score2]+[Score3]+[Score4]+[Score5]+[Score6]+[Score7]+[Score8]+[Score9]+[Score10]
Each "Score" field is a calculated field on the query, is this why it
concatenates instead of summing? Thanks in advance for any help you can give
me.
 
Jason said:
I have a Query which is calculating 10 different values in a record. I
need
to then sum these 10 values within each row. When I attempt to total
them,
I get a concatenation instead of a summed total. Here's my formula:
TotalCorrect:
[Score1]+[Score2]+[Score3]+[Score4]+[Score5]+[Score6]+[Score7]+[Score8]+[Score9]+[Score10]
Each "Score" field is a calculated field on the query, is this why it
concatenates instead of summing? Thanks in advance for any help you can
give me.
Click to expand...

Why not try going to the query and in a single field do all the
calculating and adding in one step.

You may be right about why your formula is not working, but I don't
know.
 
Each "Score" field is comparing two values and then showing a 1 or 0. The
total needs to be in a seperate field because it has to add up all of the 1's
that are shown from the comparisons. Becasue of this, I don't think I can do
it all in the same field.

Joseph Meehan said:
Jason said:
I have a Query which is calculating 10 different values in a record. I
need
to then sum these 10 values within each row. When I attempt to total
them,
I get a concatenation instead of a summed total. Here's my formula:
TotalCorrect:
[Score1]+[Score2]+[Score3]+[Score4]+[Score5]+[Score6]+[Score7]+[Score8]+[Score9]+[Score10]
Each "Score" field is a calculated field on the query, is this why it
concatenates instead of summing? Thanks in advance for any help you can
give me.
Click to expand...

Why not try going to the query and in a single field do all the
calculating and adding in one step.

You may be right about why your formula is not working, but I don't
know.
Click to expand...
 
It is concatenating the fields because they are of type TEXT instead of type
Number. If you can change the datatype to number you should get a different
result. If you can't change the datatype then you can use the Val()
function:

TotalCorrect: Val([Score1]) + Val([Score2]) + Val([Score3]).....
 
Lynn said:
It is concatenating the fields because they are of type TEXT instead of
type
Number. If you can change the datatype to number you should get a
different
result. If you can't change the datatype then you can use the Val()
function:

TotalCorrect: Val([Score1]) + Val([Score2]) + Val([Score3]).....
Click to expand...

I should have remembered the use of Val.
Jason said:
I have a Query which is calculating 10 different values in a record. I
need to then sum these 10 values within each row. When I attempt to
total
them, I get a concatenation instead of a summed total. Here's my
formula:
TotalCorrect:
[Score1]+[Score2]+[Score3]+[Score4]+[Score5]+[Score6]+[Score7]+[Score8]+[Sco
re9]+[Score10]
Each "Score" field is a calculated field on the query, is this why it
concatenates instead of summing? Thanks in advance for any help you can
give me.
Click to expand...
Click to expand...
 
Jason said:
Each "Score" field is comparing two values and then showing a 1 or 0. The
total needs to be in a seperate field because it has to add up all of the
1's that are shown from the comparisons. Becasue of this, I don't think I
can do it all in the same field.

Joseph Meehan said:
Jason said:
I have a Query which is calculating 10 different values in a record. I
need
to then sum these 10 values within each row. When I attempt to total
them,
I get a concatenation instead of a summed total. Here's my formula:
TotalCorrect:
[Score1]+[Score2]+[Score3]+[Score4]+[Score5]+[Score6]+[Score7]+[Score8]+[Score9]+[Score10]
Each "Score" field is a calculated field on the query, is this why it
concatenates instead of summing? Thanks in advance for any help you can
give me.
Click to expand...

Why not try going to the query and in a single field do all the
calculating and adding in one step.

You may be right about why your formula is not working, but I don't
know.
Click to expand...
Click to expand...

Sure you can. It can go something like this:

In the Filed line of a blank column of a query:

NameForResult: ([PartA1] *
[PartB1])+([PartA2]*[PartB2])+([PartA3]*[PartB3])+....+([PartA10]*[PartB10])

Assuming your calculation is multiplying fields PartA times fields PartB.
You can press Shift F2 to give yourself a larger area to type the expression
in.
 
I should have remembered the use of Val.
It was mostly a guess on my part -- adding text values will always produce a
concatenation.
 
Try converting each on to a number (int or double as needed) for example:

TotalCorrect: CDbl([Score1])+CDbl([Score2])+ etc...

--Steve Huff
 
Back
Top