Adding Columns in Access Database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two columns that I want to make into one column of answers.
I have seen this in accounting programs like quickbooks, Microsoft
accounting and also examples of accounting programs but I have no access to
open them up to find out what the code is behind it.
Example:
Column 1 Column 2 conversion
Answer Column 3
Row 1 8 -5 8-5= 3
Row 2 7 12 3+7+12= 22
Final answer 22
Column 1 adds to column 2 column 3 has the answer. For row 1 the answer is 3.
Row two adds the answer of row 1 “or column 3 row 1†to column 1 row 2 and
column 2 row 2
Which gives the answer in row 2 column 3 as 22
I guess it will take a VBA code maybe there is a function. I have not been
able to find code that calculates this.
In Excel it is very easy to put it up but I want to use it in Access 2007.
In Excel all I do is add A1 with A2 in A3 the second row I add A3 "the
answer of A1 and A2" with B1 and B2 in B3
Example:
Column 1 Column 2 Column 3
A A1 A2 A3
B B1 B2 B3
 
Karl,

If you copy and paste the following into a new module and then replace the
table and column names with your own table and column names and then run it
you should find that it updates the third column with the sum of values from
column 1 and column 2 of the current row and column 3 of the previous row.
Note: you may need to add a reference to DAO in your VBA editor. - Sparker
'________________________________________________________________________
'________________________________________________________________________
Option Compare Database
Option Explicit

Sub CalcColumn3()

Dim daoDbs As DAO.Database
Dim daoRec As DAO.Recordset
Dim strSql As String
Dim curValOld As Currency
Dim curValNew As Currency

Set daoDbs = CodeDb
strSql = "SELECT Table1.tblID, Table1.[Column 1], Table1.[Column 2] FROM
Table1;"
Set daoRec = daoDbs.OpenRecordset(strSql)
curValOld = 0
curValNew = 0

If Not (daoRec.BOF And daoRec.EOF) Then
daoRec.MoveFirst
Do While Not daoRec.EOF
curValNew = ((daoRec("[Column 1]").Value + daoRec("[Column
2]").Value) + curValOld)
Call UpdateColumn3(daoRec("tblID").Value, curValNew)
curValOld = curValNew
daoRec.MoveNext
Loop
End If

strSql = ""
daoRec.Close
daoDbs.Close
Set daoRec = Nothing
Set daoDbs = Nothing

End Sub
'________________________________________________________________________
'________________________________________________________________________
Function UpdateColumn3(lngRow As Long, curVal As Currency)

Dim daoDbs As DAO.Database
Dim strSql As String

Set daoDbs = CodeDb
strSql = _
"UPDATE Table1 SET Table1.[Column 3] = " & _
curVal & " WHERE (((Table1.tblID) = " & lngRow & "));"
daoDbs.Execute strSql, dbSeeChanges

strSql = ""
daoDbs.Close
Set daoDbs = Nothing

End Function
'________________________________________________________________________
'________________________________________________________________________
 
Back
Top