Hello;
I'm Having trouble with Summing some columns:
Below the [BAL] Field Value should be 3400 but it is doubling the value to 6800!!
I cannot see any issue with the statement!!
What an I doing Wrong?

Any Help would be greatly appreciated

TIA

Allan

Sample Source Data
PART_NO   DESC_1   UNIT_COST   PTD_ISSUE   PTD_RECEPT   PTD_ADJST   PTD_TRANSF   QTY_START   ACTIVE
SYRI001   SYRINGE DISP  3CC LL   0.0604   7200   7200   0   0   3400   TRUE

Sample Output freom SQL
PART_NO   QTY_START   PTD_RECEPT   PTD_ISSUE   PTD_ADJST   BAL   ISSUE_1   UNIT_COST
SYRI001   3400   7200   7200   0   6800   EA   0.0604


SELECT
[DHA4_STOCKINV_NEW].[PART_NO],
[DHA4_STOCKINV_NEW].[QTY_START],
[DHA4_STOCKINV_NEW].[PTD_RECEPT],
[DHA4_STOCKINV_NEW].[PTD_ISSUE],
[DHA4_STOCKINV_NEW].[PTD_ADJST],
SUM([DHA4_STOCKINV_NEW].[QTY_START]+[DHA4_STOCKINV_NEW].[PTD_RECEPT]) - sum( [DHA4_STOCKINV_NEW].[PTD_ISSUE]+
[DHA4_STOCKINV_NEW].[PTD_ADJST]) as BAL,
[DHA4_STOCK_NEW].[ISSUE_1],
[DHA4_STOCKINV_NEW].[UNIT_COST]
FROM SAPItemMaster_20080909  
JOIN "DHA4_STOCKINV_NEW" ON [DHA4_STOCKINV_NEW].[PART_NO] = [SAPItemMaster_20080909].[ItemNo]
JOIN "DHA4_STOCK_NEW" ON [DHA4_STOCK_NEW].[PART_NO] = [SAPItemMaster_20080909].[ItemNo]
WHERE [Plant] ='DHA4'
AND [DHA4_STOCKINV_NEW].[PART_NO] = 'SYRI001'
GROUP BY [DHA4_STOCKINV_NEW].[PART_NO];   

"Allan Brocklehurst" <brock@ns.sympatico.ca> wrote in message
news:0A26389C-00D4-4A19-8265-98E2CCC17AF5@news.elevatesoft.com...
> I cannot see any issue with the statement!!
> What an I doing Wrong?
>

You should not select any field other than the fields being aggregated and
the "group by" fields. Anyway, what is probably happening here is that your
joins are generating more records that you think. Do the joins without the
SUM and the GROUP BY, and see what the plain select finds.

Also I didn't think your SUM was even legal

SUM([DHA4_STOCKINV_NEW].[QTY_START]+[DHA4_STOCKINV_NEW].[PTD_RECEPT]) -
sum( [DHA4_STOCKINV_NEW].[PTD_ISSUE]+
> [DHA4_STOCKINV_NEW].[PTD_ADJST]) as BAL,

Why not

SUM([DHA4_STOCKINV_NEW].[QTY_START]+[DHA4_STOCKINV_NEW].[PTD_RECEPT]) - (
[DHA4_STOCKINV_NEW].[PTD_ISSUE]+
> [DHA4_STOCKINV_NEW].[PTD_ADJST])) as BAL,


Robert

Robert;
Hi and thanks.
My drive table "SAPItemMaster_20080909" has 2 records for this PART_NO.

Just writing this down has helped me

Thanks again

Allan

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:


"Allan Brocklehurst" <brock@ns.sympatico.ca> wrote in message
news:0A26389C-00D4-4A19-8265-98E2CCC17AF5@news.elevatesoft.com...
> I cannot see any issue with the statement!!
> What an I doing Wrong?
>

You should not select any field other than the fields being aggregated and
the "group by" fields. Anyway, what is probably happening here is that your
joins are generating more records that you think. Do the joins without the
SUM and the GROUP BY, and see what the plain select finds.

Also I didn't think your SUM was even legal

SUM([DHA4_STOCKINV_NEW].[QTY_START]+[DHA4_STOCKINV_NEW].[PTD_RECEPT]) -
sum( [DHA4_STOCKINV_NEW].[PTD_ISSUE]+
> [DHA4_STOCKINV_NEW].[PTD_ADJST]) as BAL,

Why not

SUM([DHA4_STOCKINV_NEW].[QTY_START]+[DHA4_STOCKINV_NEW].[PTD_RECEPT]) - (
[DHA4_STOCKINV_NEW].[PTD_ISSUE]+
> [DHA4_STOCKINV_NEW].[PTD_ADJST])) as BAL,


Robert