Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Adding Values in Access

DertweillerDertweiller Member Posts: 37
Hi Guys,

I have a table that has approximately ten columns - nine of which hold numeric values. The problem I am facing is: I am trying to place the total of all the values across all nine rows in the tenth column named 'Total'. Example

Col1|Col2|Col9.|Total|
..5..|..7..|..2...|.....|
..1..|..3..|..4...|.....|
..1..|..2..|..2...|..5..| < -- Example
..1..|..1..|..1...|.....|

--Without the dots

I have tried writing a VBA procedure all to no avail so any help is welcome.

[blue]I am Invincible... ;-)[/blue]

Comments

  • dokken2dokken2 Member Posts: 532
    : Hi Guys,
    :
    : I have a table that has approximately ten columns - nine of which hold numeric values. The problem I am facing is: I am trying to place the total of all the values across all nine rows in the tenth column named 'Total'. Example
    :
    : Col1|Col2|Col9.|Total|
    : ..5..|..7..|..2...|.....|
    : ..1..|..3..|..4...|.....|
    : ..1..|..2..|..2...|..5..| < -- Example
    : ..1..|..1..|..1...|.....|
    :
    : --Without the dots
    :
    : I have tried writing a VBA procedure all to no avail so any help is welcome.
    :
    : [blue]I am Invincible... ;-)[/blue]
    :


    you can do this with a sql query or DAO code-
    [5 columns in this example]

    Query-
    UPDATE Table2 SET Table2.total = [col1]+[col2]+[col3]+[col4]+[col5];

    CODE-

    [code]
    'requires reference to
    'Microsoft DAO 3.6 object library [or DAO 2.5/3.51 or 3.51]
    '[menu- tools, references]
    Sub Update()
    Const MyTable = "Table2"

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    'Set rs = db.OpenRecordset(MyTable, dbOpenSnapshot) 'OPEN TABLE, READ
    Set rs = db.OpenRecordset(MyTable, dbOpenDynaset) 'OPEN TABLE, WRITE
    rs.MoveFirst 'MOVE 1ST RECORD

    Dim MyTotal As Double

    'LOOP IF NOT END-OF-FILE/LAST RECORD
    Do While Not rs.EOF
    MyTotal = 0 'INIT TO ZERO
    'TOTAL EACH FIELD/COLUMN
    MyTotal = MyTotal + rs!col1
    MyTotal = MyTotal + rs!col2
    MyTotal = MyTotal + rs!col3
    MyTotal = MyTotal + rs!col4
    MyTotal = MyTotal + rs!col5
    rs.Edit 'EDIT RECORD
    rs!total = MyTotal 'ASSIGN TOTAL TO FIELD
    rs.Update 'UPDATE/SAVE RECORD
    rs.MoveNext 'MOVE TO NEXT RECORD
    Loop

    'CLOSE TABLE
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    End Sub
    [/code]
Sign In or Register to comment.