Running Balance in a query

[miguel vasquez]

I have a query that have three columns invoice number, tran number and amount. What I would like to do is to sum the invoice number if they are equal. example:

new column
Invoice #: 1345652 50.00 50.00
Invoice #: 1345652 65.00 115.00

I have the same invoice multiple times, but the transaction number is different. so I need to SUM all the invoices that are the same.


Please help!!!!!!!

 

[Vassago]

If you want to sum the amount without grouping the tran number, do a query like this:

SELECT yourtable.invoicenumber, Sum(yourtable.amount) AS SumOfamount
FROM Yourtable
GROUP BY yourtable.invoicenumber

This will completely ignore the tran number.

 

[miguel vasquez]

I don't want to have distinct values, I don't want to group them. If the invoice number is the same I need to see it multiple times and just sum the invoice that are the same by lines.

example:

Invoice# amount Running balance
1256545 50.00 50.00
1256545 15.00 65.00
1256545 10.00 75.00 (this balance is the sum of all the values)

 

[RuralGuy]

PMFJI but I think you will find that queries *can't* keep a running total but Reports can.

 

[miguel vasquez]

Running Balance

Can you please tell me how to do it in a report?


thanks

 

[RuralGuy]

Simply place a bound TextBox on the report and set the Running Sum property on the data tab to OverAll or OverGroup.