Running sum

[Guest]

Hello friends,
I have TableailyRecords Where CummQty is Calculated Field.
DRId Date ItemNo LocationCode QtyToday CummQty
1 OCT01 1 1 500 500
2 Oct 02 1 2 200 200
3 Oct 03 2 4 700 700
4 Oct 04 2 4 300 1000
5 Oct 05 1 1 200 700
6 Oct 06 1 2 1500 1700
I Used DSUM for CummQty in Query Table which works good. But problem is
when I need Report for say Oct02 CummQty for ItemNo 1, LocationCode 2=1500
Insted of 200!!!!! How do I fix this so that Report shows me CummQty Exectly
as shown in above table for Perticular Date?
Thanks.

Andy

 

[Guest]

Andy:

You 'll need to incorporate the ItemNo, LocationCode and the date into the
criteria for the DSUM function call so it sums only rows up to and including
the date in question for the particular combination of ItemNo and
LocationCode, e.g.

SELECT DRId, [Date], ItemNo, LocationCode, QtyToday,
DSUM("QtyToday", "DailyRecords", "ItemNo = " & [ItemNo] &
" And LocationCode = " & [LocationCode] &
" And [Date] <= #10/02/2007#") AS CummQty
FROM DailyRecords
WHERE [Date] = #10/02/2007#;

Or you can use a subquery:

SELECT DRId, [Date], ItemNo, LocationCode, QtyToday,
(SELECT SUM(QtyToday)
FROM DailyRecords AS DR2
WHERE DR2.ItemNo = DR1.ItemNo
AND DR2.LocationCode = DR1.LocationCode
AND [Date] <= #10/02/2007#") AS CummQty
FROM DailyRecords AS DR1
WHERE [Date] = #10/02/2007#;

You can of course use a parameter for the date criterion:

PARAMETERS [Enter Date:] DATETIME;
SELECT DRId, [Date], ItemNo, LocationCode, QtyToday,
(SELECT SUM(QtyToday)
FROM DailyRecords AS DR2
WHERE DR2.ItemNo = DR1.ItemNo
AND DR2.LocationCode = DR1.LocationCode
AND [Date] <= [Enter Date:]) AS CummQty
FROM DailyRecords AS DR1
WHERE [Date] = [Enter Date:];

BTW I'd suggest changing the name of your date column to something like
TransactionDate as Date is the name of a built in function so could cause
confusion in some circumstances and return the wrong results. If you do use
it be sure to always wrap it in brackets [Date].

Ken Sheridan
Stafford, England

 

[Guest]

Try as the sum field in the query

DSum("[QtyToday]","DailyRecords","[LocationCode]=" & [LocationCode] & " And
[DRId] <= " & [DRId])

Assuming that the query order is by the DRid field

 

[Guest]

Hi Ken,
Sorry to reply late. I really thank you very much for helping me. I Actually
need my Date field with the perameter that you suggested. Thank you again.
If you don't mind I would like to take an opportunity to ask another Que.
I have 4 to 5 row of controls in Detail Sec. of Report. Out of that
control's field has no data which creat gap between row. to avoid this gap
what should I do?
Thank you Ken

Andy:

You 'll need to incorporate the ItemNo, LocationCode and the date into the
criteria for the DSUM function call so it sums only rows up to and including
the date in question for the particular combination of ItemNo and
LocationCode, e.g.

SELECT DRId, [Date], ItemNo, LocationCode, QtyToday,
DSUM("QtyToday", "DailyRecords", "ItemNo = " & [ItemNo] &
" And LocationCode = " & [LocationCode] &
" And [Date] <= #10/02/2007#") AS CummQty
FROM DailyRecords
WHERE [Date] = #10/02/2007#;

Or you can use a subquery:

SELECT DRId, [Date], ItemNo, LocationCode, QtyToday,
(SELECT SUM(QtyToday)
FROM DailyRecords AS DR2
WHERE DR2.ItemNo = DR1.ItemNo
AND DR2.LocationCode = DR1.LocationCode
AND [Date] <= #10/02/2007#") AS CummQty
FROM DailyRecords AS DR1
WHERE [Date] = #10/02/2007#;

You can of course use a parameter for the date criterion:

PARAMETERS [Enter Date:] DATETIME;
SELECT DRId, [Date], ItemNo, LocationCode, QtyToday,
(SELECT SUM(QtyToday)
FROM DailyRecords AS DR2
WHERE DR2.ItemNo = DR1.ItemNo
AND DR2.LocationCode = DR1.LocationCode
AND [Date] <= [Enter Date:]) AS CummQty
FROM DailyRecords AS DR1
WHERE [Date] = [Enter Date:];

BTW I'd suggest changing the name of your date column to something like
TransactionDate as Date is the name of a built in function so could cause
confusion in some circumstances and return the wrong results. If you do use
it be sure to always wrap it in brackets [Date].

Ken Sheridan
Stafford, England

Hello friends,
I have TableailyRecords Where CummQty is Calculated Field.
DRId Date ItemNo LocationCode QtyToday CummQty
1 OCT01 1 1 500 500
2 Oct 02 1 2 200 200
3 Oct 03 2 4 700 700
4 Oct 04 2 4 300 1000
5 Oct 05 1 1 200 700
6 Oct 06 1 2 1500 1700
I Used DSUM for CummQty in Query Table which works good. But problem is
when I need Report for say Oct02 CummQty for ItemNo 1, LocationCode 2=1500
Insted of 200!!!!! How do I fix this so that Report shows me CummQty Exectly
as shown in above table for Perticular Date?
Thanks.

Andy