Adding to columns in query

[tb5038]

Hi,

I have a query which I want to add up to fields Score1 and Score2, im using the expression below in a column on the query

total: [Score1]+[Score2]

This is what Im getting...

RecordID,Score1,Score2,Total

1,1,2,12
2,1,1,11
3,4,4,44

what I really want is the sum, ive tried different expressions but I have had no luck. im looking for the total to be a sum of Score1 and Score2 to show the following

RecordID,Score1,Score2,Total

1,1,2,3
2,1,1,2
3,4,4,8

Can anyone tell me where im going wrong?!

Thanks

 

[CJ_London]

Your scores are a text field type - change it to number

 

[tb5038]

Thanks for that! Since posting this ive fiddled and lost the query.

I needed to add FitRmSecureScore to NoSecTagsScore and display in a column totalscore

I have added a screen shot to explain further....

The values FitRmSecureScore and NoSecTagsScore are created by the same query using the expression FitRmSecureScore: IIf([FitRmSecure]<0,"10","0")

 

[CJ_London]

Your structure as posted is correct with brackets etc - it is just the field type needs to be changed in the table.

Without seeing the rest of your data, I would say it looks like your data is not normalised - would normally expect to have a separate record for each score

 

[tb5038]

Your structure as posted is correct with brackets etc - it is just the field type needs to be changed in the table.

Without seeing the rest of your data, I would say it looks like your data is not normalised - would normally expect to have a separate record for each score

Sorry for the confusion, will start again.

I have a table detailing a number of checks performed daily, each of the checks is a number (ive changed the field type now).

There are four checks that require a value, if the value is above 0 the query puts in a number 10, using this FitRmSecureScore: IIf([FitRmSecure]<0,"10","0") expression

I then want to add up the four scores. Does that help, thanks again

Ive added a screen shot to show where im upto, im getting a prompt box now too :S

 

[CJ_London]

Your prompt box is happening because you are trying to group the data - try taking the grouping off or using select distinct - from what I can see grouping is not required

You do not need the quotation marks around the 10 and the 0 in your iif statements - this converts them to text

 

[tb5038]

Thanks CJ, Thats worked great. Realise the mistake with the "" and the grouping. Keen to learn, will make these queries so much easier!

Thanks again