Dsum causes #Error 2

[pduncan]

I have been trying all morning to calculate a running sum in a query (the query is to obtain as invoice total). My tblInvoice has several rows - each row has (amoung other things) InvoiceID(PK), InvoiceNo, RegionalAmountDue - this is a total cost for that line in the invoice. Each row in tblInvoice represents a row on an invoice. i.e. if there are 3 rows on an invoice, there are 3 rows in tblInvoice. I am trying to sum the RegionalAmountDue (price) for each row - by InvoiceNo...

here is my SQL:

select tblInvoice.InvoiceID, tblInvoice.InvoiceNo, tblInvoice.RegionalAmountDue, Format (DSUm("RegionalAmountDue", "tblInvoice", "InvoiceNo = " & [InvoiceNo] & " And InvoiceID <=" & [InvoiceID]), "Currency") as RunningSum
from tblInvoice;

In the query, the first three colummns are as expected - the last gives me "#Error" - in tblInvoice, there are no Nulls. Any ideas what I am doing wrong?

Thanks, PDUNCAN

 

[PHV]

You want a running sum ?
SELECT I.InvoiceID, I.InvoiceNo, I.RegionalAmountDue,
(SELECT Sum(J.RegionalAmountDue) FROM tblInvoice J WHERE J.InvoiceNo=I.InvoiceNo And J.InvoiceID<=I.InvoiceID) AS RunningSum
from tblInvoice I;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pduncan]

Thanks PHV - here's a star (I got a correct reponse from you in less than a minute after I posted -wow!)

Can you please tell me what you are doing with the I and J's - I am trying to figure out what you did.
Thanks again

Thanks, PDUNCAN

 

[PHV]

I and J are alias for tblInvoice to distinguish which field is invoked.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pduncan]

OK - thanks
Now if I want to save the running sum total (the highest value of RegionalAmountDue) for each invoice to another table, how should I do that?
I have a table called tblInvoiceTotals - there are columns named tblInvoice and RegionalSubtotal in that table.

I am thinking an update query but not clear on how to only pass the max value.

Thanks, PDUNCAN

 

[PHV]

It's often a bad idea to store derived/calculated values.
You get (always accurate) invoice totals with a simple aggregate query:
SELECT InvoiceNo, Sum(RegionalAmountDue) AS RegionalSubtotal
FROM tblInvoice
GROUP BY InvoiceNo;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pduncan]

understood - thanks again for the help!

Thanks, PDUNCAN

 

[milcman]

Have another star PHV...your simple aggregate query was exactly what I was looking for!

Clint Galliano
Halliburton Energy Services
BAROID PSL
&quot;Done ONCE, Done RIGHT!&quot;