Help with running balance...

[Brook]

good day...

can anyone help me with a running balance within my query...

I have the following relevant fields:

inoiceid
inoicenumber
serialnumber
itemcost
itemshippingcost
itemdiscount
itemtotalcost

I'm trying to create balance for each invoice... for all invoice items...

any suggestions are apprecitated...

thansk

Brook

 

[Tom Ellison]

Dear Brook:

I am presuming that invoicenumber may be used to define the rows that belong
to an invoice separate from any other invoice, that serialnumber places the
rows within each invoice into the proper sequence, and that itemtotalcost is
the column to be summed. The code might be:

SELECT invoicenumber, serialnumber, itemtotalcost,
(SELECT SUM(itemtotalcost)
FROM YourTable T1
WHERE T1.invoicenumber = T.invoicenumber
AND T1.serialnumber <= T.serialnumber)
AS RunningSum
FROM YourTable T
ORDER BY invoicenumber, serialnumber

You must replace YourTable above with the actual name of the table or query
from which the information is derived.

It concerns me somewhat that you have a column itemtotalcost. Is this just
the derived value of cost + shippingcost - discount? If so, maintaining
such a value accurately when the 3 components are likely subject to change
(corrections) is an unnecessary difficulty. It is very simple to derive it
whenever it is needed, and much safer.

This is one of the well known and accepted standards for table designs. On
the other hand, if it is being derived in a query (which you do not say)
then it may be perfectly good as it is.

Tom Ellison

 

[Brook]

Good Day...

I wanted to first say thanks for the resposne, but also I tried the code
you showed me and changed the tbl names and here is my sql, the problem is,
is that the code is totaling for all the invoices, not each individual
invoice?

SELECT invoicenumber, serialnumber, itemtotalcost,
(SELECT SUM(itemtotalcost)
FROM tblinvoicedetails
WHERE tblinvoicedetails.invoicenumber = tblinvoicedetails.invoicenumber
AND tblinvoicedetails.serialnumber <= tblinvoicedetails.serialnumber)
AS RunningSum
FROM tblinvoicedetails
ORDER BY invoicenumber, serialnumber


Any ideas on what I did wrong?

the query is based on the following tables:

tblinvoices
tlbinvoicedetails

Thanks,

Brook

 

[Tom Ellison]

Dear Brook:

It is not working because you changed things other than just the table name.
By changing only the table name I used, "YourTable", to be
"tblinvoicedetails" it would then read:

SELECT invoicenumber, serialnumber, itemtotalcost,
(SELECT SUM(itemtotalcost)
FROM tblinvoicedetails T1
WHERE T1.invoicenumber = T.invoicenumber
AND T1.serialnumber <= T.serialnumber)
AS RunningSum
FROM tblinvoicedetails T
ORDER BY invoicenumber, serialnumber

I believe this will work for you. Please try this first making no changes
at all.

Tom Ellison