Help With A Access Data Base

[SgiGod]

I have a access data base that has a field in it called Premium i would like to add up all of the numbers for each client in the Data Base in the Premium Field.

How can i do this.

I would like it to output to a textbox.

this is what I got Now but it don't work :)

set db = OpenDatabase("C:\Program Files\Lsi\Tracker\Data\data1.dll")
set rs = db.OpenRecordset("SELECT * FROM bond1 WHERE Premium<>'" & txt1.Text & "';")

rs.MoveFirst
'Do While Not rs.EOF
A = rs!premium
B = txt2.Text
C = txt3.Text

C = A
txt2.Text = txt2.Text + (A + B)

rs.MoveNext
'Loop





Thanks in Advance
Donny S.

[Johnny101]

i'm not sure if Access can do this, but here's something that works in SQL Server:


Dim sql as string

sql = "SELECT SUM(Premium) as 'Total', CustomerName FROM Bond1 "
sql = sql & "GROUP BY CustomerName"

set rs = cn.execute(sql)




the above sql statement will return the total amount for each customer. you can reference the fields like so:

Text1.Text = rs!Total
Text2.Text = rs!CustomerName





hope this helps,
John


John Pirkey
MCSD
www.ShallowWaterSystems.com

[d.paulson]

set db = OpenDatabase("C:\Program Files\Lsi\Tracker\Data\data1.dll")
set rs = db.OpenRecordset("SELECT * FROM bond1 WHERE Premium<>0
with rs
.movefirst
do until .eof
txt2.text = txt2.text + !premium
.movenext
loop
end with