sum in queries

[ralph1009]

why do when i use addition in queries:

example: i want the sum of there 3 fields. 1(as record in field1) + 1(as record in field 2) + 1(as record in field 3) the sum gives me 111 instead of 3..

what should i do?

 

[Brianwarnock]

Looks like they are text fields and thus the + is acting as a concatenation try sum

Brian

 

[Paine]

Hi Ralph,

Just trying to understand your set up here.

Presumably in the query you have:

[Field 1], [Field 2], [Field 3], [Total_Name: [Field 1]+[Field 2]+[Field 3]]




Steve

 

[ralph1009]

Looks like they are text fields and thus the + is acting as a concatenation try sum

Brian

thank you sir.. i tried to change the data type of the field to number and I already get the sum..

 

[ralph1009]

Hi Ralph,

Just trying to understand your set up here.

Presumably in the query you have:

[Field 1], [Field 2], [Field 3], [Total_Name: [Field 1]+[Field 2]+[Field 3]]




Steve

yes. that is the calculation. the data type of the fields are in text, then I change it to numeric and got the right answer..

 

[CBrighton]

If you were unable to alter the field type in the table you could also use:

SumField: CInt([Field1]) + CInt([Field2]) + CInt([Field3])

CInt should convert the text "1" into the number 1 for the purposes of the SumField field only.

 

[loopyl00]

Hi I'm not sure if I'm having a similar problem

I have a table where I have a drop down for the user to select one of two options

'Prospect'
'Marketing'

elsewhere i need to convert Prospect to 1 and Marketing (and non entries) to 0 for reporting purposes

do i need to Cint

At the moment I have created in a query
Points Origines: IIf([Origines]='Prospect',1,0)

but when i sum this it is suming 0 values too so I'm not getting accurate results at all

 

[Galaxiom]

You would be better off starting a new thread for this.

However I would suggest you store the integers to represent your values rather than the text. It is much more efficient. Change the FieldType to Number and use Integer or Byte.

For display you can convert to text using the Format Property of the control.

For example if you use 1 to represent "Prospect", Zero to represent "Marketing" and Null as no entry you would enter the following in the Format Property.

"Prospect";;"Marketing"

The general form of this is:
"Positive";"Negative";"Zero";"Null"

 

[loopyl00]

Thanks I can see that this will work

however, when entering data in the table, the drop down is showing 1 and 2

Also when i put this into a query to SUM it is returning a value of 2 for marketing,

basically, I need to report how many 'prospect' for each ID over a date period, so want marketing and null to return 0 in the SUM

 

[Galaxiom]

You need to change the properties of the combo to:

RowSource: 0;Marketing;1;Prospect
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0cm;2cm (change the second value to suit)

 

[loopyl00]

Could you please clarify how it handles NULL or how I get it to assign 0 for Null

I have another query that pulls several queries together to add up the poitns, at the moment if this field is NULL it is not totalling that line

SELECT qrySumofAc.FKVendeurID, qryRankAc.Points, qryRankMarge.Points, qryRankSales.Points, ([qryRankAc].[Points]+[qryrankmarge].[Points]+[qryRankSales].[points]+[QrySumOriginesVendeur].[SumOfOrigines]) AS SumPoints, QrySumOriginesVendeur.SumOfOrigines
FROM QrySumOriginesVendeur INNER JOIN ([Weekly Machines Sales data] INNER JOIN (((qryRankMarge INNER JOIN qryRankAc ON qryRankMarge.FKVendeurID = qryRankAc.FKVendeurID) INNER JOIN ((qrySumofAc INNER JOIN QrySumOfMarge ON qrySumofAc.FKVendeurID = QrySumOfMarge.FKVendeurID) INNER JOIN qrySumofSales ON QrySumOfMarge.FKVendeurID = qrySumofSales.FKVendeurID) ON qryRankAc.FKVendeurID = qrySumofSales.FKVendeurID) INNER JOIN qryRankSales ON qryRankMarge.FKVendeurID = qryRankSales.FKVendeurID) ON [Weekly Machines Sales data].FKVendeurID = qryRankSales.FKVendeurID) ON QrySumOriginesVendeur.FKVendeurID = [Weekly Machines Sales data].FKVendeurID
WHERE ((([Weekly Machines Sales data].[Date de Vente]) Between [Forms]![Dashboard]![Startdate] And [Forms]![Dashboard]![Enddate]))
GROUP BY qrySumofAc.FKVendeurID, qryRankAc.Points, qryRankMarge.Points, qryRankSales.Points, QrySumOriginesVendeur.SumOfOrigines;

 

[CBrighton]

Nz() should deal with nulls:

'Nz([Field Name],[Null Replacement])
'e.g.
Nz([BlahBlah],0)

 

[loopyl00]

Actually I thnk i've solved it with NZ

SumPoints: ([qryRankAc].[Points]+[qryrankmarge].[Points]+[qryRankSales].[points]+Nz([QrySumOriginesVendeur].[SumOfOrigines]))

 

[loopyl00]

I think i've solved it with NZ

SumPoints: ([qryRankAc].[Points]+[qryrankmarge].[Points]+[qryRankSales].[points]+Nz([QrySumOriginesVendeur].[SumOfOrigines]))