Same records go on the same line

[zrazzaq]

Hi:
I have a table that looks like this:
Acct Name Balance AcctType
33 John $45 P
33 John $65 K
22 Mar $25 P
14 Honey $35 P
14 Honey $60 P
and so on....
Any way, I need it to look like this:
Acct Name Balance AcctType
33 John $45 P 33 John $65 K
22 Mar $25 P
14 Honey $35 P 14 Honey $60 P

So you see I need the same acct number to be on the same line. I am doing this in html vb...I do not know where I am going wrong.It always misses one of the duplicate accts..My code goes as follows:

    Set db = CurrentDb
    
    Set rs = db.OpenRecordset(strSQL)
    Set rsA = db.OpenRecordset(strsql2)
    
    Print #1, "<Table cellpadding=0 cellspacing=0 border=1>"
    Print #1, "<TR class=ColHead>"
            Print #1, "<TD>Account Number</TD>"
            Print #1, "<TD>Loan Code</TD>"
            Print #1, "<TD>Current Guarantor or Program Code</TD>"
            Print #1, "<TD>First/Only Disbursement Date</TD>"
            Print #1, "<TD>Interest Rate</TD>"
            Print #1, "<TD>Borrower Status Code</TD>"
            Print #1, "<TD>Grace End Date</TD>"
            Print #1, "<TD>Current Principal Balance</TD>"
            Print #1, "<TD>Payoff Amount as of Anticipated Loan Payoff Date</TD>"
            Print #1, "<TD>Daily Interest Accrual</TD>"
            Print #1, "<TD>LN_ORIG_LOANED_AMT</TD>"
    Print #1, "</TR>"
    
    rs.MoveFirst
    SSN1 = rs("[Account Number]")
    rs.MoveNext
    SSN2 = rs("[Account Number]")
    rs.MoveFirst
    acct = rs("[Account Number]")
    rsA.MoveFirst

    Do Until rs.EOF
        Print #1, "<TR class=RowsHead>"
        Do Until SSN1 <> SSN2
            Debug.Print "Doing" & acct
            If rsA("Field3") = acct Then
                Print #1, "<TD>**" & acct & "**</TD>"
                rsA.MoveNext
            Else
                Print #1, "<TD>""" & acct & """</TD>"
            End If
            Print #1, "<TD>" & rs("[Loan Code]") & "</TD>"
            Print #1, "<TD>" & rs("[Current Guarantor or Program Code]") & "</TD>"
            Print #1, "<TD>" & rs("[First/Only Disbursement Date]") & "</TD>"
            Print #1, "<TD>" & rs("[Interest Rate]") & "</TD>"
            Print #1, "<TD>" & rs("[Borrower Status Code]") & "</TD>"
            Print #1, "<TD>" & rs("[Grace End Date]") & "</TD>"
            Print #1, "<TD>" & rs("[Current Principal Balance]") & "</TD>"
            Print #1, "<TD>" & rs("[Payoff Amount as of Anticipated Loan Payoff Date]") & "</TD>"
            Print #1, "<TD>" & rs("[Daily Interest Accrual]") & "</TD>"
            Print #1, "<TD>" & rs("[LN_ORIG_LOANED_AMT]") & "</TD>"
            rs.MoveNext
            'Debug.Print "Next" & rs("[Account Number]")
            If rs.EOF Then
                Print #1, "</TABLE>"
                Print #1, "</BODY>"
                Print #1, "</HTML>"
                Close #1
                Exit Function
            End If
            SSN2 = rs("[Account Number]")
            acct = SSN2
            Print #1, "<TD bgcolor=""red"">&nbsp;&nbsp;&nbsp</TD>"
        Loop
            'rs.MoveNext
            'SSN2 = rs("Account Number")
            If SSN2 = acct Then
            Else
            If SSN2 <> SSN1 Then
            Print #1, "<TR class=RowsHead>"
            If rsA("Field3") = SSN2 Then
                Print #1, "<TD>**" & SSN2 & "**</TD>"
                rsA.MoveNext
            Else
                Print #1, "<TD>""" & SSN2 & """</TD>"
            End If
            Print #1, "<TD>" & rs("[Loan Code]") & "</TD>"
            Print #1, "<TD>" & rs("[Current Guarantor or Program Code]") & "</TD>"
            Print #1, "<TD>" & rs("[First/Only Disbursement Date]") & "</TD>"
            Print #1, "<TD>" & rs("[Interest Rate]") & "</TD>"
            Print #1, "<TD>" & rs("[Borrower Status Code]") & "</TD>"
            Print #1, "<TD>" & rs("[Grace End Date]") & "</TD>"
            Print #1, "<TD>" & rs("[Current Principal Balance]") & "</TD>"
            Print #1, "<TD>" & rs("[Payoff Amount as of Anticipated Loan Payoff Date]") & "</TD>"
            Print #1, "<TD>" & rs("[Daily Interest Accrual]") & "</TD>"
            Print #1, "<TD>" & rs("[LN_ORIG_LOANED_AMT]") & "</TD>"
            Print #1, "</TR>"
            End If
            End If
            rs.MoveNext
            SSN1 = rs("Account Number")
            rs.MoveNext
            SSN2 = rs("Account Number")
            rs.MovePrevious
            acct = SSN1
            
        Loop
        
    Print #1, "</TABLE>"
    Print #1, "</BODY>"
    Print #1, "</HTML>"

Please I need help!!!
Thanks
Z

 

[SkipVought]

Hi,

Why not use a PivotTable where the results would look like this.
[tt]
Sum of Balance AcctType
Acct Name K P
14 Honey 95
22 Mar 25
33 John 65 45
Grand Total 65 165
[/tt]
BTW, it took me about 5 SECONDS to make this report using the PivotTable Wizard.

It took me about 2 MINUTES to copy/paste/reformat so it would look OK in this post.


Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[zrazzaq]

Yeah I suggested that but u know how work is they always want things harder. So They want this report in html format...for some reason...But I do thank you for helping me out...So can someone just check my code to tell me where I am going wrong How come it skips on duplicate and goes to the other...basically before the new acct starts it skips over the first one and goes to the second duplicate value. For example if you have 1,1,1,2,3,3...it would do 1,1,2,3,...see it skips....
Thankz
Z

 

[SkipVought]

...and the BUSINESS CASE for that requirement is?????

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[zrazzaq]

It is easier to process every account is different..,.These are student loans we are dealing with..I hope that helps.Thanks
Z

 

[SkipVought]

EASIER?

How do you figure that it's easier?

Please explain.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}