Getting data from two recordsets problem

[mcsmba1]

Plz help me:
I displayed data on datareport from two recordsets. Report is also attached. The problem is:
Date is display twice, i want to display date in once and data should display against the date from both recordset. I used the code

     rsFee.Open "SELECT CurrentDate, SUM(DepositedFee) AS `DepositedFee` FROM Fee WHERE month(CurrentDate)= " & cmbMonth.Text & " and year (CurrentDate)= " & txtYear.Text & " GROUP BY CurrentDate order by currentdate", myConn, adOpenKeyset, adLockOptimistic
     rsIncome.Open "SELECT CurrentDate, Sum(Amount) as `Income` FROM tbTransaction WHERE HEAD='INCOME' AND month(CurrentDate)= " & cmbMonth.Text & " and year (CurrentDate)= " & txtYear.Text & " group by currentDate order by currentdate", myConn, adOpenKeyset, adLockOptimistic

'**********************************************************************************
    theRs.Fields.Append "Currentdate", adDate
    theRs.Fields.Append "DepositedFee", adDouble
    theRs.Fields.Append "Concession", adDouble
    theRs.Fields.Append "Total", adDouble
    theRs.Fields.Append "Income", adDouble
    
    
    theRs.Open
    While Not rsFee.EOF
    theRs.AddNew
    theRs.Fields!CurrentDate = rsFee.Fields!CurrentDate
    theRs.Fields!DepositedFee = rsFee.Fields!DepositedFee
    theRs.Update
    rsFee.MoveNext
    Wend
    
    While Not rsIncome.EOF
    theRs.AddNew
    theRs.Fields!CurrentDate = rsIncome.Fields!CurrentDate
    theRs.Fields!Income = rsIncome.Fields!Income
    theRs.Update
    rsIncome.MoveNext
    Wend
    theRs.Sort = "currentdate"
    theRs.MoveFirst
    Set rptMonIncome.DataSource = theRs
    rptMonIncome.DataMember = theRs.DataMember

     rptMonIncome.Show
        Unload Me

[jggtz]

You could insert a Group by date, sum the imports and suppress the Detail

[mcsmba1]

I am sorry, sir, coulnt' understand can u give me detail please

[mcsmba1]

Can anybody else help me in this regard......

[wes4dbt]

Try something like this,

    While Not rsIncome.EOF
    theRs.Find "CurrentDate=#" & CStr(rsIncome.CurrentDate) & "#", , adSearchForward, 1
    if theRs.EOF then
        theRs.AddNew
        theRs.Fields!CurrentDate = rsIncome.Fields!CurrentDate
        theRs.Fields!Income = rsIncome.Fields!Income
    else
        theRs.Edit
        theRs.Fields!Income = rsIncome.Fields!Income
    End If
    theRs.Update
    rsIncome.MoveNext
    Wend