Sum of added fields is incorrect?

[fiona.bremner]

I'm working on a financial report and need to get a Year to Date sum
of the values for the fiscal period's that have elapsed. The table
structure looks like this:

ACT_PER_01, ACT_PER_02, ACT_PER_03, .....ACT_PER_12

The Fiscal Period fields are the ones called ACT_PER_XX. So, the user
enters the fiscal period they want the report for and I need to sum up
ACT_PER_01 to the period they entered to get the Year to Date value.
My code runs, and I get a YTD Sum, but the value is off. I used the
loop below to create the SQL:

'loop thru fiscal periods for the year up to and including the
fiscal period entered
For i = 1 To FiscalPeriodCounter Step 1
ActualYTDSql = ActualYTDSql & " + " & "ACT_PER_" & IIf(i <
10, "0" & i, i)
Next i

The loop above gives me (after a little clean up):
" ( ACT_PER_01 + ACT_PER_02 + ACT_PER_03) as ActualYTDSql "

I run my sql and get my results. Then I sum (ActualYTDSql ) and it
doesn't quite equal the result I get if I SUM(ACT_PER_01) and
SUM(ACT_PER_02) and SUM(ACT_PER_03 ) and add those three results
together.

I can't seem to figure out why they aren't equal. Does anyone know
what might be wrong?

 

[John W. Vinson]

I'm working on a financial report and need to get a Year to Date sum
of the values for the fiscal period's that have elapsed. The table
structure looks like this:

ACT_PER_01, ACT_PER_02, ACT_PER_03, .....ACT_PER_12

That's a good spreadsheet. It's a TERRIBLE table.

"Fields are expensive, records are cheap". If you have a one (account) to many
(periods) relationship, you should have it as a *one to many relationship* -
use another table with a date/time field for the period and an amount, as well
as a foreign key to the primary key of whatever table you have now.

I can't seem to figure out why they aren't equal. Does anyone know
what might be wrong?

Not without seeing the database (which I'd rather not).

Possibly the datatype is incorrect - Integers and Longs will be truncated to
the next lowest whole number for instance.

John W. Vinson [MVP]

 

[fiona.bremner]

Hi John,

Thanks for your reply.

That's a good spreadsheet. It's a TERRIBLE table.

Yes, I agree. Unfortunately it's a legacy system and I'm stuck with
the structure.

Possibly the datatype is incorrect - Integers and Longs will be truncated to
the next lowest whole number for instance.

I thought it could be a data type error but the data types for all the
fields are Currency. Do you know if when you sum a Currency field
could the data type be changed to something else?

Thanks!

Fiona

 

[John W. Vinson]

The loop above gives me (after a little clean up):
" ( ACT_PER_01 + ACT_PER_02 + ACT_PER_03) as ActualYTDSql "

I run my sql and get my results. Then I sum (ActualYTDSql ) and it
doesn't quite equal the result I get if I SUM(ACT_PER_01) and
SUM(ACT_PER_02) and SUM(ACT_PER_03 ) and add those three results
together.

Please post the actual complete SQL, and one or two examples of the values
being summed, the desired result, and the correct result.

One POSSIBLE issue is that Currency datatype values always have four (no more,
no fewer) decimal places. If you have the fields formatted to zero or two
decimals, those pesky tenths and hundredths of cents are still there
regardless, and will add up. The usual solution is to round calculations to
two decimals (or whatever is needed) prior to storing the field.

John W. Vinson [MVP]

 

[fiona.bremner]

Hi John,

Here is the complete SQL:

In code I generate and execute this:
"INSERT INTO testFiscalYTD ( RPT_GRP, ActualYTD ) SELECT RPT_GRP,
( ACT_PER_01 + ACT_PER_02 + ACT_PER_03 ) as ActualYTD FROM
J_ACCOUNT_MSTR WHERE RPT_GRP = 'EB' AND FSCL_YR = 2007"

In Query Analyzer I get the SUM:
Select RPT_GRP, sum(ActualYTD ) as FiscalYTD from testFiscalYTD
group by RPT_GRP;

Result:
RPT_GRP FiscalYTD
EB $6,545,954.62 this value is too high by almost $500, which I show
below:


In Query Analyzer, I manually sum the three account periods
seperately::

SELECT rpt_grp, sum(ACT_PER_01) as Sum01, sum(ACT_PER_02) as Sum02,
sum(ACT_PER_03 ) as Sum03
FROM J_ACCOUNT_MSTR
WHERE rpt_grp = "EB" and FSCL_YR = 2007
GROUP BY rpt_grp;

rpt_grp Sum01 Sum02 Sum03
EB $2,234,051.70 $1,852,248.56 $2,460,150.72

$2,234,051.70 + $1,852,248.56 + $2,460,150.72 = $6,546,450.98 This
is the correct number

You mentioned a possible rounding error. The odd thing is that I'm
pulling from exactly the same table, so datatype's are all the same.
Maybe adding the numbers together in my first query causes some sort
of truncation of the values?

Thank you for your help!

Fiona

 

[Gary Walter]

$6,545,954.62 isn't ~$500 higher than
$6,546,450.98 (the correct sum)

in Query Analyzer

SELECT 234.50 + Null

gives Null

{just like in Jet, the "+" appears to
propagate Nulls}

--> it might be a field (or fields) is null
for some record (or records) causing
the sum to be *LESS*

two possible workarounds for your insert sql
(it is not clear to me in what environment this
is being applied)

1) SQL SERVER

you would wrap each "act" in one of these:

a) ISNULL(a,0.0) //if a is null, returns 0.0

b) COALESCE(a,b)
// returns first nonNULL expression among its arguments;
// any number of arguments;
// if all NULL, returns NULL

( COALESCE(ACT_PER_01, 0.0)
+ COALESCE(ACT_PER_02, 0.0)
+ COALESCE(ACT_PER_03, 0.0) ) AS ActualYTD

2) Jet
you would use null-to-zero function NZ()
(which appears to cast result as text, so may
need to also recast as currency)

( CCur(NZ(ACT_PER_01, 0.0))
+ CCur(NZ(ACT_PER_02, 0.0))
+ CCur(NZ(ACT_PER_03, 0.0)) ) AS ActualYTD

 

[Gary Walter]

yeah..that's what I meant to say....

keep reminding me and maybe one day
I won't revert back to my old thought patterns...

 

[fiona.bremner]

Thank you all for your help! I used (database in MS Access)

SUM( IIF(ACT_PER_01 IS NULL, CCur(0.0000), ACT_PER_01 ) +
IIF(ACT_PER_02 IS NULL, CCur(0.0000), ACT_PER_02 ) ....) as
ActualYTDSql

And now my result is correct! Thank you again - you've saved me a
tremendous amount of time and frustration.

Fiona :->