Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Question Unanswered: Sumarize a column in a able

    Hi,

    i have the following table

    iID iAmount
    1 1000
    2 1500
    3 2000

    How can i get this

    iID iAmount iSum
    1 1000 1000
    2 1500 2500
    3 2000 4500

    I would like also to be able to insert new rows in the new table/query.

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: Sumarize a column in a able

    Originally posted by alekizoglou
    Hi,

    i have the following table

    iID iAmount
    1 1000
    2 1500
    3 2000

    How can i get this

    iID iAmount iSum
    1 1000 1000
    2 1500 2500
    3 2000 4500

    I would like also to be able to insert new rows in the new table/query.
    Are you wanting to store these calculated values in the table?

    Is this a temporary table or a permanent one?

    Generally, it's not recommended to store values that can be calculated in a table however, for reporting purposes it works better sometimes.

    Do you want to write programming code to do this or what?

    Not many answers I know but it helps to establish the context of the question first.

    Gregg

  3. #3
    Join Date
    Nov 2003
    Posts
    7

    Re: Sumarize a column in a able

    OK Gregg,

    sorry for not being so descriptive initially.

    This is a permanent table

    TABLE Register
    iID longint,
    iAmount longint

    I am writing a program that utilizes ADO DataSets, so I can write pure SQL queries to do whatever i want.

    What I need is display the table contents in a Grid plus an aditional value that cumulatively sumarizes the iAmount value for every row and bellow. Also I would like to be able to append the table.

    I agree with you that the new value should not be stored in the Table.

    I have no problem in writing code to do so, but initially I thought of an SQL query that could do the thing. Furthermore I would like the new value to be automatically re-calculated whenever an iAmount changes in a row.

    Hope this helps to understand the problem


    Originally posted by basicmek
    Are you wanting to store these calculated values in the table?

    Is this a temporary table or a permanent one?

    Generally, it's not recommended to store values that can be calculated in a table however, for reporting purposes it works better sometimes.

    Do you want to write programming code to do this or what?

    Not many answers I know but it helps to establish the context of the question first.

    Gregg

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Sumarize a column in a able

    Originally posted by alekizoglou
    OK Gregg,

    sorry for not being so descriptive initially.

    This is a permanent table

    TABLE Register
    iID longint,
    iAmount longint

    I am writing a program that utilizes ADO DataSets, so I can write pure SQL queries to do whatever i want.

    What I need is display the table contents in a Grid plus an aditional value that cumulatively sumarizes the iAmount value for every row and bellow. Also I would like to be able to append the table.

    I agree with you that the new value should not be stored in the Table.

    I have no problem in writing code to do so, but initially I thought of an SQL query that could do the thing. Furthermore I would like the new value to be automatically re-calculated whenever an iAmount changes in a row.

    Hope this helps to understand the problem
    Ok, this may not be exactly what you are looking for but I just wanted to see how I would do this with a recordset.

    I set up a table with an autonumber ID field to keep the records in order and the two fields that you mentioned. Then I set up a form that allowed the adding and editing of the first value (iID).
    Here's what I did to recalculate each time one was added or changed:

    Private Sub iID_AfterUpdate()
    Me.Refresh
    Dim db As Database, rs As Recordset
    Dim lngPrevTotal As Long, lngCurrentItem As Long, x As Integer
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Register", dbOpenDynaset)

    With rs
    .MoveLast
    .MoveFirst
    For x = 1 To rs.RecordCount
    If rs.EOF Then Exit Sub
    If x = 1 Then
    .Edit
    .Fields("iAmount") = .Fields("iID")
    .Update
    .MoveNext
    Else
    lngCurrentItem = .Fields("iID")
    .MovePrevious
    lngPrevTotal = .Fields("iAmount")
    .MoveNext
    .Edit
    .Fields("iAmount") = lngCurrentItem + lngPrevTotal
    .Update
    .MoveNext

    End If
    Next

    End With
    Me.Refresh
    Set rs = Nothing
    Set db = Nothing

    End Sub

    Tell me what you think.

    Gregg

  5. #5
    Join Date
    Nov 2003
    Posts
    7

    Re: Sumarize a column in a able

    Originally posted by basicmek
    Ok, this may not be exactly what you are looking for but I just wanted to see how I would do this with a recordset.

    I set up a table with an autonumber ID field to keep the records in order and the two fields that you mentioned. Then I set up a form that allowed the adding and editing of the first value (iID).
    Here's what I did to recalculate each time one was added or changed:

    Private Sub iID_AfterUpdate()
    Me.Refresh
    Dim db As Database, rs As Recordset
    Dim lngPrevTotal As Long, lngCurrentItem As Long, x As Integer
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Register", dbOpenDynaset)

    With rs
    .MoveLast
    .MoveFirst
    For x = 1 To rs.RecordCount
    If rs.EOF Then Exit Sub
    If x = 1 Then
    .Edit
    .Fields("iAmount") = .Fields("iID")
    .Update
    .MoveNext
    Else
    lngCurrentItem = .Fields("iID")
    .MovePrevious
    lngPrevTotal = .Fields("iAmount")
    .MoveNext
    .Edit
    .Fields("iAmount") = lngCurrentItem + lngPrevTotal
    .Update
    .MoveNext

    End If
    Next

    End With
    Me.Refresh
    Set rs = Nothing
    Set db = Nothing

    End Sub

    Tell me what you think.

    Gregg
    Thank you,

    I will try this even though I think on a large recordset this would be time consuming. Furthermore when someone changes an iAmount filed then the whole process will have to take place.
    I think I will enhance it a litle bit more with an extra field, lets call it iSumAmount, that will keep the sum, then if the field is not Null calculate the sum for the point down to the EOF. Then when an update takes place, recalculate each iSumAmount from that point down to the EOF.

    I will tell you what's the outcome,

    Thank you

    Nasos

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •