Access Problem with Sum()

[Tanvir]

hi
I am working with a database in MSAccess. but i have got a
problem by the sum(). I have two fields in a table of the
database. The fields are [Amount] and [Total_Amount]. I
want to show the sum of all the records of [Amount] in
[Total_Amount]. Would you please help me
Tanvir

 

[Naresh Nichani MVP]

Hi:

Is this like a Running Total? You could get this to work in Access Reports
without writing to a field -- check out Reporting options and options for
Running Total there. The Data Tab has an option for Running Sum.

Other option is to do it by code --

Dim db as DAO.database
Dim rs as DAO.RecordSet, rs1 as DAO.RecordSet
Dim dblTotal as Double

Set db = CurrentDb
Set rs = db.OpenRecordSet("Select * from table1",dbOpenDynaset)
Do while not rs.eof
Set rs1 = db.OpenRecordset("Select Sum([Amount]) from table1 Where [ID]
<=" & Rs.fields("ID"),dbOpenSnapshot) 'assume you have a ID autoincrement
field
if not rs1.Eof then
dblTotal = Nz(rs1.fields(0))
rs.Edit
rs.fields("Total Amount") = dblTotal
rs.Update
end if
rs1.close
set rs1 = nothing
rs.Movenext
loop
rs.close
set rs = nothing
db.Close
Set db = Nothing


Regards,

Naresh Nichani
Microsoft Access MVP

 

[John Vinson]

hi
I am working with a database in MSAccess. but i have got a
problem by the sum(). I have two fields in a table of the
database. The fields are [Amount] and [Total_Amount]. I
want to show the sum of all the records of [Amount] in
[Total_Amount]. Would you please help me
Tanvir

The [Total_Amount] field should not exist in your table, at all.

Instead, it should be calculated as needed using a Totals query.