Coverting vertical data to horizontal 2

[PJFry]

I keep a database of loans issued. Part of our business is to upgrade and consolidate these loans. The database has two basic parts: Loans "upgraded to" and loans "upgraded from". The "upgraded to" table has the new loan number and new loan stats. The "upgrade from" has the old number and stats as well as the "upgraded to" number. For example:

tblUpgraded From:
To: From:
1 11
1 12
1 13
2 21
2 22
3 31
3 32

What I have been asked to do is present this data in a horizontal fashion. Like so:


1 11 12 13
2 21 22
3 31 32

Any suggestions? VBA examples ok.

Thanks!

 

[ZmrAbdulla]

A crosstab query will be the simplest answer
Also check dhookom's How to concatenate multiple child records into a single value
faq701-4233


Zameer Abdulla
[sub]Jack of Visual Basic Programming, Master in Dining & Sleeping[/sub]
Visit Me

 

[PJFry]

Thank you for the answer. I do need to add a bit of clarification: I would like each of the child number end up in its own column. One of my problems has been that there are anywhere from one to eight child contracts. My first thought was to set each parent contract as a variable and do a loop while statement until I reached the end of the file, but I was not sure how to put the next child number in the next column. That about where I am.

 

[ZmrAbdulla]

I can't quite understand your requirement.. Can you please show us a sample data that required to be displayed?

Zameer Abdulla
[sub]Jack of Visual Basic Programming, Master in Dining & Sleeping[/sub]
Visit Me

 

[PJFry]

Does this clarify it? As you can see, the number of child loans is not fixed. Also, is there a way to upload an example?

New Loan|Original Loan 1|Original Loan 2 |Original Loan 3
1 | 11 | 12 | 13
2 | 21 | 22 |
3 | 31 | 32 |
4 | 41 | 42 | 43

thanks again

 

[denosaur]

I think I understand your problem. Based on my understanding, I created a table, VLoans with columns ToLoanID, FromLoanID. VLoans had the same data in it that you noted in your example above. Then, I created an empty table, HLoans with the following columns: ToLoanID,FromLoan1,FromLoan2...FromLoan8.

Then I added a module with the following code:

Public ToLoanNum As Long
Public FromLoanNum As Long
Public iLoanCounter As Integer


Sub VerticalLoans()
Dim rs As Recordset
Dim sSQL As String

sSQL = "SELECT * FROM VLoans"
Set rs = CurrentDb.OpenRecordset(sSQL)

Do While Not rs.EOF
    ToLoanNum = rs!toLoanID
    Do While rs!toLoanID = ToLoanNum
        FromLoanNum = rs!FromLoanID
        iLoanCounter = iLoanCounter + 1
        HorizontalLoans
        rs.MoveNext
        If rs.EOF Then
            Exit Do
        End If
    Loop
    iLoanCounter = 0
Loop
Set rs = Nothing
End Sub


Sub HorizontalLoans()
Dim rs As Recordset
Dim sSQL As String

Select Case iLoanCounter
    Case 1
        sSQL = "SELECT * FROM HLoans"
        Set rs = CurrentDb.OpenRecordset(sSQL)
        rs.AddNew
        rs!toLoanID = ToLoanNum
        rs!FromLoan1 = FromLoanNum
    Case 2
        sSQL = "SELECT * FROM HLoans WHERE [ToLoanID] = " & ToLoanNum
        Set rs = CurrentDb.OpenRecordset(sSQL)
        rs.Edit
        rs!FromLoan2 = FromLoanNum
    Case 3
        sSQL = "SELECT * FROM HLoans WHERE [ToLoanID] = " & ToLoanNum
        Set rs = CurrentDb.OpenRecordset(sSQL)
        rs.Edit
        rs!FromLoan3 = FromLoanNum
    Case 4
        sSQL = "SELECT * FROM HLoans WHERE [ToLoanID] = " & ToLoanNum
        Set rs = CurrentDb.OpenRecordset(sSQL)
        rs.Edit
        rs!FromLoan4 = FromLoanNum
    Case 5
        sSQL = "SELECT * FROM HLoans WHERE [ToLoanID] = " & ToLoanNum
        Set rs = CurrentDb.OpenRecordset(sSQL)
        rs.Edit
        rs!FromLoan5 = FromLoanNum
    Case 6
        sSQL = "SELECT * FROM HLoans WHERE [ToLoanID] = " & ToLoanNum
        Set rs = CurrentDb.OpenRecordset(sSQL)
        rs.Edit
        rs!FromLoan6 = FromLoanNum
    Case 7
        sSQL = "SELECT * FROM HLoans WHERE [ToLoanID] = " & ToLoanNum
        Set rs = CurrentDb.OpenRecordset(sSQL)
        rs.Edit
        rs!FromLoan7 = FromLoanNum
    Case 8
        sSQL = "SELECT * FROM HLoans WHERE [ToLoanID] = " & ToLoanNum
        Set rs = CurrentDb.OpenRecordset(sSQL)
        rs.Edit
        rs!FromLoan8 = FromLoanNum
End Select
rs.Update

Set rs = Nothing
End Sub

This populated the HLoans table with the data from VLoans. The HLoans data looks like the example that you noted above.

There are probably a dozen or more other ways to do this, but I hope this helps.