Running Sum

M

Melinda

I am trying to calculate a running sum for the following table with the 3
fields listed below:

tblFullDataTable
ID
InvcDate
NetTotal

I have a query written, but I keep getting an #Error message when I run the
query. The ID, InvcDate, and NetTotal appear, but in the "Running Sum"
field, it's all errors. Can someone please help me?

Thanks.

Melinda


SELECT tblFullDataTable.ID, tblFullDataTable.InvcDate,
tblFullDataTable.NetTotal, DSUM("NetTotal","tblFullDataTable","ID='" & [ID] &
"' AND InvcDate<=#" & [InvcDate] & "#") AS RunningTotal
FROM tblFullDataTable;
 
O

Ofer

Is the ID is string type or number, if its a number loose the single quote
before and after
SELECT tblFullDataTable.ID, tblFullDataTable.InvcDate,
tblFullDataTable.NetTotal, DSUM("NetTotal","tblFullDataTable","ID=" & [ID] &
" AND InvcDate<=#" & [InvcDate] & "#") AS RunningTotal
FROM tblFullDataTable;
 
J

John M via AccessMonster.com

Create a Report. To create a running sum, you first add a text box to the
report in Design view and set its ControlSource property to the field or
expression that you want to sum (NetTotal, for example). You then set the
RunningSum property of the text box, which determines the range of records
over which the text box values are accumulated.
 
M

Melinda

Thank you. That got rid of the errors, but it is only repeating the same
number as the NetTotal, not calculating a running sum. I think maybe the
problem is that I am trying to calculate the running sum in order of the
dates of the invoice, and the ID is only the access generated primary key.
For example:

ID InvcDate NetTotal RunningSum
1 1/1/01 $ 50.00 $ 50.00
2 1/2/01 $ 50.00 $ 100.00
3 1/3/01 $ 50.00 $ 150.00
4 1/4/01 $ 50.00 $ 200.00

Thanks.

Melinda

Ofer said:
Is the ID is string type or number, if its a number loose the single quote
before and after
SELECT tblFullDataTable.ID, tblFullDataTable.InvcDate,
tblFullDataTable.NetTotal, DSUM("NetTotal","tblFullDataTable","ID=" & [ID] &
" AND InvcDate<=#" & [InvcDate] & "#") AS RunningTotal
FROM tblFullDataTable;



Melinda said:
I am trying to calculate a running sum for the following table with the 3
fields listed below:

tblFullDataTable
ID
InvcDate
NetTotal

I have a query written, but I keep getting an #Error message when I run the
query. The ID, InvcDate, and NetTotal appear, but in the "Running Sum"
field, it's all errors. Can someone please help me?

Thanks.

Melinda


SELECT tblFullDataTable.ID, tblFullDataTable.InvcDate,
tblFullDataTable.NetTotal, DSUM("NetTotal","tblFullDataTable","ID='" & [ID] &
"' AND InvcDate<=#" & [InvcDate] & "#") AS RunningTotal
FROM tblFullDataTable;
Click to expand...
Click to expand...
 
M

Melinda

Thank you. I know how to do this in a report, but I need it in a query. Can
you help me with this?

John M via AccessMonster.com said:
Create a Report. To create a running sum, you first add a text box to the
report in Design view and set its ControlSource property to the field or
expression that you want to sum (NetTotal, for example). You then set the
RunningSum property of the text box, which determines the range of records
over which the text box values are accumulated.
I am trying to calculate a running sum for the following table with the 3
fields listed below:

tblFullDataTable
ID
InvcDate
NetTotal

I have a query written, but I keep getting an #Error message when I run the
query. The ID, InvcDate, and NetTotal appear, but in the "Running Sum"
field, it's all errors. Can someone please help me?

Thanks.

Melinda

SELECT tblFullDataTable.ID, tblFullDataTable.InvcDate,
tblFullDataTable.NetTotal, DSUM("NetTotal","tblFullDataTable","ID='" & [ID] &
"' AND InvcDate<=#" & [InvcDate] & "#") AS RunningTotal
FROM tblFullDataTable;
Click to expand...
Click to expand...
 
S

Sandro

Melinda said:
I am trying to calculate a running sum for the following table with the 3
fields listed below:

tblFullDataTable
ID
InvcDate
NetTotal

I have a query written, but I keep getting an #Error message when I run the
query. The ID, InvcDate, and NetTotal appear, but in the "Running Sum"
field, it's all errors. Can someone please help me?

Thanks.

Melinda


SELECT tblFullDataTable.ID, tblFullDataTable.InvcDate,
tblFullDataTable.NetTotal, DSUM("NetTotal","tblFullDataTable","ID='" & [ID] &
"' AND InvcDate<=#" & [InvcDate] & "#") AS RunningTotal
FROM tblFullDataTable;
Click to expand...

Hi Melinda,
try in this way by a subQuery :

SELECT r1.id, r1.invCdate, r1.NetTotal, (select sum(NetTotal) from
tblFullDataTable as r2 where r2.id=r1.) AS RunningTotal
FROM tblFullDataTable AS r1

Ciao Sandro ( from Italy) :)
 
S

Sandro

Sandro said:
Melinda said:
I am trying to calculate a running sum for the following table with the 3
fields listed below:

tblFullDataTable
ID
InvcDate
NetTotal

I have a query written, but I keep getting an #Error message when I run the
query. The ID, InvcDate, and NetTotal appear, but in the "Running Sum"
field, it's all errors. Can someone please help me?

Thanks.

Melinda


SELECT tblFullDataTable.ID, tblFullDataTable.InvcDate,
tblFullDataTable.NetTotal, DSUM("NetTotal","tblFullDataTable","ID='" & [ID] &
"' AND InvcDate<=#" & [InvcDate] & "#") AS RunningTotal
FROM tblFullDataTable;
Click to expand...

Hi Melinda,
try in this way by a subQuery :

SELECT r1.id, r1.invCdate, r1.NetTotal, (select sum(NetTotal) from
tblFullDataTable as r2 where r2.id=r1.) AS RunningTotal
FROM tblFullDataTable AS r1

Ciao Sandro ( from Italy) :)
Click to expand...

oops sorry :

SELECT r1.id, r1.invCdate, r1.NetTotal, (select sum(NetTotal) from
tblFullDataTable as r2 where r2.id=r1.id) AS RunningTotal
FROM tblFullDataTable AS r1

Ri-Ciao Sandro.
 
J

John Spencer (MVP)

SELECT ID,
InvcDate,
NetTotal,
(SELECT Sum(NetTotal) FROM YourTable as YT WHERE YT.ID <= YourTable.ID) as RunningSum
FROM YourTable
Thank you. That got rid of the errors, but it is only repeating the same
number as the NetTotal, not calculating a running sum. I think maybe the
problem is that I am trying to calculate the running sum in order of the
dates of the invoice, and the ID is only the access generated primary key.
For example:

ID InvcDate NetTotal RunningSum
1 1/1/01 $ 50.00 $ 50.00
2 1/2/01 $ 50.00 $ 100.00
3 1/3/01 $ 50.00 $ 150.00
4 1/4/01 $ 50.00 $ 200.00

Thanks.

Melinda

Ofer said:
Is the ID is string type or number, if its a number loose the single quote
before and after
SELECT tblFullDataTable.ID, tblFullDataTable.InvcDate,
tblFullDataTable.NetTotal, DSUM("NetTotal","tblFullDataTable","ID=" & [ID] &
" AND InvcDate<=#" & [InvcDate] & "#") AS RunningTotal
FROM tblFullDataTable;



Melinda said:
I am trying to calculate a running sum for the following table with the 3
fields listed below:

tblFullDataTable
ID
InvcDate
NetTotal

I have a query written, but I keep getting an #Error message when I run the
query. The ID, InvcDate, and NetTotal appear, but in the "Running Sum"
field, it's all errors. Can someone please help me?

Thanks.

Melinda


SELECT tblFullDataTable.ID, tblFullDataTable.InvcDate,
tblFullDataTable.NetTotal, DSUM("NetTotal","tblFullDataTable","ID='" & [ID] &
"' AND InvcDate<=#" & [InvcDate] & "#") AS RunningTotal
FROM tblFullDataTable;
Click to expand...
Click to expand...
Click to expand...
 
Top