Help With A Access Data Base
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.
Re: Help With A Access Data Base
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
Re: Help With A Access Data Base
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