locked
Running Total?? RRS feed

  • Question

  • Hi, New to all this...

     

    I am trying to write a simple "Checkbook Register"

     

    Have a 'Debits' Column and a 'Credits' Colulmn

    Added a Computed Column 'Balance'

     

    Tried to use aggregate(I think you call it);  

     

    'Balance' Expression;   Sum(Credits) - Sum(Debits)

     

    which works except it updates every row every time to the same value.

     

    I want to have each row show the balance after each transaction.

    Dont think aggregate is the ticket here...

    Using the VB2005 Express editions. Thanks Rob

    Tuesday, May 15, 2007 7:27 PM

Answers

  • DECLARE @Debit int,

    @Credit int,

    @myDifference int,

    @myBalance int

    SET @myBalance = 0

     

    CREATE TABLE #temp(debit int, credit int, dif int, balance int)

     

    DECLARE myBalanceCursor CURSOR FOR

    --Just replace the following code:

    SELECT debit, credit, debit - credit

    FROM myTest

    --end replace here

    OPEN myBalanceCursor

    FETCH NEXT FROM myBalanceCursor

    INTO @Debit, @Credit, @myDifference

    WHILE @@FETCH_STATUS = 0

    BEGIN

     

    SET @myBalance = @myBalance + @myDifference

     

    INSERT INTO #temp

    SELECT @Debit, @Credit, @myDifference, @myBalance

     

    FETCH NEXT FROM myBalanceCursor

    INTO @Debit, @Credit, @myDifference

    END

    CLOSE myBalanceCursor

    DEALLOCATE myBalanceCursor

     

    SELECT * FROM #temp

     

    DROP TABLE #temp

    Tuesday, May 15, 2007 8:11 PM

All replies

  • I believe that you don't want to use any function.

     

    Simply use Credits - Debits if you want each row to be calculated as a stand alone column.

     

    Adamus

    Tuesday, May 15, 2007 7:34 PM
  • Yes, That totals the row to itself... I get that

     

    How can I get the Running total of all transactions up to each row? (as in a checkbook register)

     

     

    Tuesday, May 15, 2007 7:41 PM
  • DECLARE @Debit int,

    @Credit int,

    @myDifference int,

    @myBalance int

    SET @myBalance = 0

     

    CREATE TABLE #temp(debit int, credit int, dif int, balance int)

     

    DECLARE myBalanceCursor CURSOR FOR

    --Just replace the following code:

    SELECT debit, credit, debit - credit

    FROM myTest

    --end replace here

    OPEN myBalanceCursor

    FETCH NEXT FROM myBalanceCursor

    INTO @Debit, @Credit, @myDifference

    WHILE @@FETCH_STATUS = 0

    BEGIN

     

    SET @myBalance = @myBalance + @myDifference

     

    INSERT INTO #temp

    SELECT @Debit, @Credit, @myDifference, @myBalance

     

    FETCH NEXT FROM myBalanceCursor

    INTO @Debit, @Credit, @myDifference

    END

    CLOSE myBalanceCursor

    DEALLOCATE myBalanceCursor

     

    SELECT * FROM #temp

     

    DROP TABLE #temp

    Tuesday, May 15, 2007 8:11 PM
  • I bet you thought there was an easy way Smile

     

    Adamus

    Tuesday, May 15, 2007 8:14 PM
  •  

    Yeah...  A little over my head I am afraid...

     

    Any Way to insert a total from a textbox into a cell on selectedrow or currentrow?

     

     

     

    Private Sub TransactionsBindingSource_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TransactionsBindingSource.PositionChanged, TransactionsBindingSource.CurrentChanged, AccountsBindingNavigatorSaveItem.Click

    If Me.TransactionsBindingSource.Position > -1 Then

    Dim TtlBalance As Double = 0

    Dim TtlCredits As Double = 0

    Dim TtlDebits As Double = 0

    For Each row As DataRowView In Me.TransactionsBindingSource.List

    TtlCredits += row!Credit

    TtlDebits += row!Debit

    TtlBalance = TtlCredits - TtlDebits

    Next

    Me.TextBox1.Text = Format(TtlBalance, "c")

     

    Else

    Me.TextBox1.Text = ""

    End If

    End Sub

     

     

    --Then get either (TtlBalance) or (textbox1.text) into the Balance column of the selectedrow or currentrow?

     

     

    Tuesday, May 15, 2007 8:46 PM