Data Report Problem

[pardeep3dec]

Dear Friends,
I have a report which columns given below.
Sno Project Name Project No Bill Raise Amount
1 abc 0213 YES 123.33
2 xyz 0232 NO 23.33
3 asd 0365 YES 125.65
4 rty 0325 254.52
5 hjkg 0254 NO 236.22
and i am using dataenvironment->command1

My requirement is Sum Amount (YES) ?
Sum Amount (NO) ?
Sum Amount ( ) ?
I can get only one at one datareport
Please tell how can i get all of above individual amount (YES and NO and blank) in one data report in a single data report

[dspnhn]

try this
'declaration section
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Private Sub Command1_Click()
'now calculate the sum you require step by step or you can create multiple recordsets and append all at once and update your table
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open ("select sum(amt)as amt from table1 where bill='YES'"), db, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
db.BeginTrans
db.Execute ("update table1 set tmpyes=" & rs!amt)
db.CommitTrans
End If

rs.Close
Set rs = Nothing

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open ("select sum(amt)as amt from table1 where bill='NO'"), db, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
db.BeginTrans
db.Execute ("update table1 set tmpno=" & rs!amt)
db.CommitTrans
End If

rs.Close
Set rs = Nothing

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open ("select sum(amt)as amt from table1 where bill is null"), db, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
db.BeginTrans
db.Execute ("update table1 set tmpblk=" & rs!amt)
db.CommitTrans
End If

'after this call report

DataEnvironment1.rsCommand1_Grouping.Properties.Refresh
DataEnvironment1.rsCommand1_Grouping.Open
DataReport1.Show vbModal
DataEnvironment1.rsCommand1_Grouping.Close

End Sub

Private Sub Form_Load()
Set db = New ADODB.Connection
db.CursorLocation = adUseClient
db.Open "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\db1.mdb;"

Me.Move (Screen.Width - Me.Width) / 2, (Screen.Height - Me.Height) / 2

End Sub

I AM ALSO ATTACHING THE SOLVED SAMPLE.