Create a running Balance

[Guest]

good day all,

i'm trying to create a running balance in my query for each of my
invoices, I have the following I believe to be relevant fields:

frminvoicedetailssubform based on tblinvoicedetails

invoiceid
itemtotal

Can anyone help...

I searched the forum but couldn't find any helpful suggestions to workin my
situation..

Thanks,
Brook

 

[Tom Ellison]

Dear Brook:

In addition to the columns you gave, there needs to be one more column which
puts the rows in chronological sequence. I'm going to assume you have such
a data/time column, which I'll call TransactionDate. The query would then
be:

SELECT invoiceid, TransactionDate, itemtotal,
(SELECT SUM(itemtotal)
FROM tblinvoicedetails T1
WHERE T1.invoiceid = T.invoiceid
AND T1.TransactionDate <= T.TransactionDate)
AS RunningSum
FROM tblinvoicedetails T
ORDER BY invoiceid, TransactionDate

You will need to substitute the actual name of your TransactionDate column
in the above (assuming there is one).

If you have two or more transactions on the same date/time, then they will
add into the running sum simultaneously. That is because, according to your
database, they are simultaneous.

Now, if you assign TransactionNumber to each of these making them uniquely
serial, then substitute that TransactionNumber for TransactionDate and
you'll really have something.

Tom Ellison

 

[Guest]

Hello tom,

I'm just posting a quick response, but here is my SQL code for my query,
if this helps in answering your questions..

i'm going to read through your response now..

Brook