Sum problem!

[tak786]

Hello..

I have got 2 queries both of which have got count functions which count different customers, these are based against a month. I have created a relationship against them two tables and put the two count fields together matching the fields using the month.

This all works fine and the data is visible n example is shown below.

Month -----Cust-------Cust Misc
Jan----------10----------1
Feb--------- 2
March-------------------3

As you can see some of them fields have no number against them. Which is were the problem occurs becuase im tryin to sum up both columns so the total of 'Jan' would be '11' and 'Feb' would be '2'. This works fine becuase Jan as values in both fields problem occurs when one of the fields dont hav a value in place.

I have tried using Nz function which places zeros in the relevant places but for lets say 'March' the answer would be '0' rather than 3.

Any help will be much appreciated.

Kind regards

Tak

[cori25]

Hello,

You can not use the sum function for this.

1.) For Cust and CustMisc(the fields that need to be added)must be renamed as follows:

Cust': IIf(IsNull([Cust]),"0 ",[Cust])

You must do this for both fields and this will change the null value to a "0"

2.) Add the following field to get the total

Total: Nz([Cust'],0)+Nz([CustMisc'],0)

You are simply adding the 2 new fields you just created together. This will populate a total of 3 when adding 0 + 3.

Let me know if this helps

Thanks

[tak786]

hello,

Instead of adding the two fields its joining them for example

0 + 3 becomes 03

rather than just '3'

any ideas? thanks for your help..

[tak786]

its ok its sorted had to use the following and multiply by one first

Total: (1*[Cust])+(1*[CustMisc])

thanks anyway

kind regards

tak

[Stewart Ross]

Instead of adding the two fields its joining them for example
0 + 3 becomes 03
rather than just '3'
any ideas? thanks for your help..

its ok its sorted had to use the following and multiply by one first
Total: (1*[Cust])+(1*[CustMisc])
thanks anyway

Hi Tak. It is clear from these two quotes that the values involved are actually being derived as text strings, not numbers. Evidence? The number 0 + the number 3 would obviously result in another number, 3, but the character '0' when added to the character '3' gives the string combination '03' - as per the first quote. You will likely find a visual clue in the way your query displays values. If the 'numbers' displayed are left aligned within their columns (and you have not changed the alignment yourself) they are actually text values - numbers will always be right-aligned by default.

In the second quote, multiplication by 1 is forcing type conversion from text to an integer - there is no other explanation I can think of for why you would have to multiply an already-numeric value by one in a simple Access addition. It just does not make sense to have to do so, unless you are not actually dealing with numbers as such, and although it works it is clearly masking an underlying problem.

Tak, you need to find out why your values are not numeric. There could be other problems (incorrect or potentially misleading calculations, for instance) being hidden at present by having so few test values of such a small range.

Check that the relevant fields in your underlying tables are defined as appropriate numeric types (longs or doubles, most likely). The default type for an Access field is text, which has to be changed to the correct type for non-text fields by the table designer.

If this is not the source of the problem, there is something else amiss at the query stage. You really will need to find out what is wrong or risk potentially severe errors - perhaps even completely meaningless results - when faced with a more normal range of data.

-Stewart

[tak786]

Hey,

The numbers are right aligned so they must be numeric, but I have used the count function which i assume makes it numeric since the fields im counting are in 'Text' format.

The numbers do total up and have been checked since im using a large amount of data. Not sure if problems will occur later on but for now it all seems fine.. :S

Thanks for the info it is much appreciated..