hi


here is my code:

Code:
'payloans and advance payment
prcconnection
If rs.State = adStateOpen Then rs.Close
rs.Open "select tblMainLoansPayment.Loans_Info_Id,tblMainLoansPayment.Id_No,tblMainLoansPayment.FullName,tblMainLoansPayment.LOAN_TYPE,tblMainLoansPayment.LOAN_AMOUNT,tblMainLoansPayment.PAYROLL_DATE,tblMainLoansPayment.AMORTIZATION,tblMainLoansPayment.AMORTIZATION2,tblMainLoansPayment.Approved_date,tblLoanAdvancePayment.loan_info_id,tblLoanAdvancePayment.adv_ORDate,tblLoanAdvancePayment.adv_Amount from tblMainLoansPayment inner join tblLoanAdvancePayment on tblMainLoansPayment.Loans_Info_Id=tblLoanAdvancePayment.loan_info_id where tblMainLoansPayment.Loans_Info_Id= " & lstNameList.ItemData(lstNameList.ListIndex) & " and tblLoanAdvancePayment.loan_info_id=" & lstNameList.ItemData(lstNameList.ListIndex) & "", con, adOpenStatic, adLockOptimistic
If rs.RecordCount >= 1 Then
Call cleanloanstatgrid
Label7.Caption = rs!Loans_Info_Id
If lstNameList.ListIndex = -1 Then Exit Sub
    rs.MoveFirst
    X = 1
    MSHFlexGrid1.Rows = rs.RecordCount + 1
        Do While Not rs.EOF
            With MSHFlexGrid1
                txtname.Text = rs!FullName
                txtID.Text = rs!ID_NO
                txttype.Text = rs!LOAN_TYPE
                txtloanamount.Text = FormatNumber(rs!LOAN_AMOUNT, 2)
                TxtAmortization.Text = FormatNumber(rs!AMORTIZATION, 2)
                txtAmortization2.Text = FormatNumber(rs!AMORTIZATION2, 2)
                TxtApprovedDate.Text = rs!APPROVED_DATE
                .TextMatrix(X, 1) = rs!PAYROLL_DATE
                If rs!AMORTIZATION2 = 0 Then
                .TextMatrix(X, 2) = FormatNumber(rs!AMORTIZATION, 2)
                ElseIf rs!AMORTIZATION2 >= 1 Then
                   .TextMatrix(X, 2) = FormatNumber(rs!AMORTIZATION2, 2)
                End If
                If rs!AMORTIZATION = 0 And rs!AMORTIZATION2 = 0 Then
                .TextMatrix(X, 2) = "0.00"
                End If
                 .TextMatrix(X, 3) = rs!adv_ORDate
                If rs!adv_Amount = 0 Then
                .TextMatrix(X, 4) = "0.00"
                 ElseIf rs!adv_Amount >= 1 Then
                .TextMatrix(X, 4) = FormatNumber(rs!adv_Amount, 2)
                End If
                
               X = X + 1
    
            End With
            rs.MoveNext
        Loop
            
            Label63.Caption = "Found " & rs.RecordCount & " records"
   con.Close
   CmdPrint.Enabled = True
   Call totalloan
   Call deductloan

Exit Sub
End If
that code is not working properly. I'm joining 2 tables with same loan_id.there are instances where table1 has 2 records while table2 has 1 record only.what happens it the table shows records 1 and 2 from table1 while table2 display record 1 twice. where it should be like this.

table 1 table2
record1 record1
record2 <empty>

i inserted an condition to no avail. pls help what is missing in my code.

thanks!