Combine Records

[BeanCounterCPA]

I have a database that has multiple records for a single individual.
For example, for a database that has the following fields for each
loan.

Member Loan Int
# # Rate Balance

11 1 .06 10,000
22 1 .07 10,000
22 2 .05 5,000
33 1 .04 20,000
44 1 .07 5,000
44 2 .07 10,000
44 3 .06 10,000

I want to create another database with one record for each borrower.
My problem is there can be anywhere from 1 to 10 loans per borrower. I
also want to include any savings/checking/certificate accounts in the
record and there may be up to 30 separate accounts or more. I have
started to write code, but something tells me there is a better way.
Any suggestions would be greatly appreciated.

Code

Option Compare Database

Public Function Tomtest()

Dim memno As Integer
Dim ln1, ln2, ln3, ln4, ln5, ln6, ln7, ln8, ln9, ln10 As String
Dim lt1, lt2, lt3, lt4, lt5, lt6, lt7, lt8, lt9, lt10 As String
Dim lb1, lb2, lb3, lb4, lb5, lb6, lb7, lb8, lb9, lb10 As Double
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Tbl_Combined_Share_Loan")
Set Rst1 = db.OpenRecordset("Tbl_Tomtest")

rst.MoveFirst

Do Until rst.EOF
Do While rst![Loan No] = ""
rst.MoveNext
Loop

memno = rst![Member No]
ln1 = rst![Loan No]
lt1 = rst![lnty]
lr1 = rst![LoanRate]
lb1 = rst![LnBal]

rst.MoveNext

If rst![Member No] = memno Then

ln2 = rst![Loan No]
lt2 = rst![lnty]
lr2 = rst![LoanRate]
lb2 = rst![LnBal]

rst.MoveNext

If rst![Member No] = memno Then

ln3 = rst![Loan No]
lt3 = rst![lnty]
lr3 = rst![LoanRate]
lb3 = rst![LnBal]

rst.MoveNext

End If
End If

With Rst1
.AddNew
!memno = memno
!ln1 = ln1
!lt1 = lt1
!lr1 = lr1
!lb1 = lb1
!ln2 = ln2
!lt2 = lt2
!lr2 = lr2
!lb2 = lb2
!ln3 = ln3
!lt3 = lt3
!lr3 = lr3
!lb3 = lb3

.Update
End With

ln1 = ""
ln1 = ""
lr1 = 0
lb1 = 0
ln2 = ""
ln2 = ""
lr2 = 0
lb2 = 0
ln3 = ""
ln3 = ""
lr3 = 0
lb3 = 0

rst.MoveNext

Loop

End Function

Thanks for any advice you can give.

[Salad]

BeanCounterCPA wrote:

I have a database that has multiple records for a single individual.
For example, for a database that has the following fields for each
loan.

Member Loan Int
# # Rate Balance

11 1 .06 10,000
22 1 .07 10,000
22 2 .05 5,000
33 1 .04 20,000
44 1 .07 5,000
44 2 .07 10,000
44 3 .06 10,000

I want to create another database with one record for each borrower.
My problem is there can be anywhere from 1 to 10 loans per borrower. I
also want to include any savings/checking/certificate accounts in the
record and there may be up to 30 separate accounts or more. I have
started to write code, but something tells me there is a better way.
Any suggestions would be greatly appreciated.

Code

Option Compare Database

Public Function Tomtest()

Dim memno As Integer
Dim ln1, ln2, ln3, ln4, ln5, ln6, ln7, ln8, ln9, ln10 As String
Dim lt1, lt2, lt3, lt4, lt5, lt6, lt7, lt8, lt9, lt10 As String
Dim lb1, lb2, lb3, lb4, lb5, lb6, lb7, lb8, lb9, lb10 As Double
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Tbl_Combined_Share_Loan")
Set Rst1 = db.OpenRecordset("Tbl_Tomtest")

rst.MoveFirst

Do Until rst.EOF
Do While rst![Loan No] = ""
rst.MoveNext
Loop

memno = rst![Member No]
ln1 = rst![Loan No]
lt1 = rst![lnty]
lr1 = rst![LoanRate]
lb1 = rst![LnBal]

rst.MoveNext

If rst![Member No] = memno Then

ln2 = rst![Loan No]
lt2 = rst![lnty]
lr2 = rst![LoanRate]
lb2 = rst![LnBal]

rst.MoveNext

If rst![Member No] = memno Then

ln3 = rst![Loan No]
lt3 = rst![lnty]
lr3 = rst![LoanRate]
lb3 = rst![LnBal]

rst.MoveNext

End If
End If

With Rst1
.AddNew
!memno = memno
!ln1 = ln1
!lt1 = lt1
!lr1 = lr1
!lb1 = lb1
!ln2 = ln2
!lt2 = lt2
!lr2 = lr2
!lb2 = lb2
!ln3 = ln3
!lt3 = lt3
!lr3 = lr3
!lb3 = lb3

.Update
End With

ln1 = ""
ln1 = ""
lr1 = 0
lb1 = 0
ln2 = ""
ln2 = ""
lr2 = 0
lb2 = 0
ln3 = ""
ln3 = ""
lr3 = 0
lb3 = 0

rst.MoveNext

Loop

End Function

Thanks for any advice you can give.

Maybe you can do a SQL Select that uses a Pivot table and groups on the
fields you want. You might have to UNION the data prior to the Pivot
query to handle multiple acounts.

[Bob Quintal]

"BeanCounterCPA" <tn*******@snavely.us> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

I have a database that has multiple records for a single
individual. For example, for a database that has the following
fields for each loan.

Member Loan Int
# # Rate Balance

11 1 .06 10,000
22 1 .07 10,000
22 2 .05 5,000
33 1 .04 20,000
44 1 .07 5,000
44 2 .07 10,000
44 3 .06 10,000

I want to create another database with one record for each
borrower. My problem is there can be anywhere from 1 to 10
loans per borrower. I also want to include any
savings/checking/certificate accounts in the record and there
may be up to 30 separate accounts or more. I have started to
write code, but something tells me there is a better way. Any
suggestions would be greatly appreciated.
[code snipped]

Thanks for any advice you can give.

The best advice is not to do it. Doing so would violate
normalisation rules and give you headaches down the road.

You can handle the situation easily with subforms that show the
loans and accounts for a member.

For reporting purposes, you can build a CrossTab query. The
wizard is pretty good at this.

--
Bob Quintal

PA is y I've altered my email address.