Need help combining related records

G

Guest

I'm trying to concatonate values from a related table for reporting purposes.
I'm on my third day and fourth different approach to getting this done. The
sample code I've tried is designed for 2 tables joined 1:M. I have 3 related
tables, but can't tweak the code to produce what I need. Here's the
background:

T1: tbl_TrustInfo (contains account info, keyed on trustinfo_id)

T2: tbl_Individual_TrustInfo_assoc (contains x-ref of tbl_TrustInfo and
tbl_Individuals)

T3: tbl_Individuals (contains IndInitials to be concatonated)

My goal is to return all selected trusts and the initials of the associated
trustees in one field, separated by semi-colons, if they exist.

Current code is below. It selects all "trustee" types but populates my
concatonated field with all initials of all trustees.

I inserted the line replacing the strSQL string with my own (to no avail!)

'************ Code Start **********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String
'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Examples:
' ?fConcatChild("Order Details", "OrderID", "Quantity", "Long", 10255)
'Where Order Details = Many side table
' OrderID = Primary Key of One side table
' Quantity = Field name to concatenate
' Long = DataType of Primary Key of One Side Table
' 10255 = Value on which return concatenated Quantity
'
Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

strSQL = "SELECT tbl_Individuals.IndInitials FROM tbl_Individuals RIGHT
JOIN (tbl_TrustInfo LEFT JOIN tbl_Individual_TrustInfo_assoc ON
tbl_TrustInfo.trustinfo_id = tbl_Individual_TrustInfo_assoc.trustinfo_id) ON
tbl_Individuals.individual_id = tbl_Individual_TrustInfo_assoc.individual_id
WHERE (((tbl_Individual_TrustInfo_assoc.Type) = 'trustee') And
((tbl_TrustInfo.trustinfo_id) =
[tbl_Individual_TrustInfo_assoc]![trustinfo_id]) And
((tbl_Individual_TrustInfo_assoc.individual_id) =
[tbl_Individuals]![individual_id]))"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & ";"
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function

'************ Code End **********

I hope I've explained things correctly.

TIA

Patty
 
G

Guest

Patty:

You only need the two tables in the recordset. Here's a less generic and
hence simpler function adapted from one of my own. I've assumed the
trustinfo_id is a long integer number data type:

Public Function GetInitials(lngtrustinfo_id As Long) As String

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strInitials As String

strSQL = "SELECT IndInitials " & _
"FROM tbl_Individuals, tbl_Individual_TrustInfo_assoc " & _
"WHERE tbl_Individuals.individual_id = " & _
"tbl_Individual_TrustInfo_assoc.individual_id " & _
" AND trustinfo_id = " & lng trustinfo_id

Set rst = New ADODB.Recordset

' iterate through recordset and build delimited
' string of initials
With rst
.ActiveConnection = CurrentProject.Connection
.Open _
Source:=strSQL, _
CursorType:=adOpenKeyset, _
Options:=adCmdText

Do While Not .EOF
strInitials = strInitials & "; " & .Fields("IndInitials")
.MoveNext
Loop
.Close
' remove leading semicolon and space
strInitials = Mid$( strInitials, 3)
End With

Set rst = Nothing
GetInitials = strInitials

End Function

You can call it as the ControlSource of an unbound text box in the report
with:

=GetInitials([trustinfo_id])

Ken Sheridan
Stafford, England

Patty said:
I'm trying to concatonate values from a related table for reporting purposes.
I'm on my third day and fourth different approach to getting this done. The
sample code I've tried is designed for 2 tables joined 1:M. I have 3 related
tables, but can't tweak the code to produce what I need. Here's the
background:

T1: tbl_TrustInfo (contains account info, keyed on trustinfo_id)

T2: tbl_Individual_TrustInfo_assoc (contains x-ref of tbl_TrustInfo and
tbl_Individuals)

T3: tbl_Individuals (contains IndInitials to be concatonated)

My goal is to return all selected trusts and the initials of the associated
trustees in one field, separated by semi-colons, if they exist.

Current code is below. It selects all "trustee" types but populates my
concatonated field with all initials of all trustees.

I inserted the line replacing the strSQL string with my own (to no avail!)

'************ Code Start **********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String
'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Examples:
' ?fConcatChild("Order Details", "OrderID", "Quantity", "Long", 10255)
'Where Order Details = Many side table
' OrderID = Primary Key of One side table
' Quantity = Field name to concatenate
' Long = DataType of Primary Key of One Side Table
' 10255 = Value on which return concatenated Quantity
'
Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

strSQL = "SELECT tbl_Individuals.IndInitials FROM tbl_Individuals RIGHT
JOIN (tbl_TrustInfo LEFT JOIN tbl_Individual_TrustInfo_assoc ON
tbl_TrustInfo.trustinfo_id = tbl_Individual_TrustInfo_assoc.trustinfo_id) ON
tbl_Individuals.individual_id = tbl_Individual_TrustInfo_assoc.individual_id
WHERE (((tbl_Individual_TrustInfo_assoc.Type) = 'trustee') And
((tbl_TrustInfo.trustinfo_id) =
[tbl_Individual_TrustInfo_assoc]![trustinfo_id]) And
((tbl_Individual_TrustInfo_assoc.individual_id) =
[tbl_Individuals]![individual_id]))"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & ";"
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function

'************ Code End **********

I hope I've explained things correctly.

TIA

Patty
Click to expand...
 
G

Guest

Thanks Ken...the code works beautifully.

For others using it, the last line of the strSQL should be lngtrustinfo_id
instead of lng trustinfo_id



Ken Sheridan said:
Patty:

You only need the two tables in the recordset. Here's a less generic and
hence simpler function adapted from one of my own. I've assumed the
trustinfo_id is a long integer number data type:

Public Function GetInitials(lngtrustinfo_id As Long) As String

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strInitials As String

strSQL = "SELECT IndInitials " & _
"FROM tbl_Individuals, tbl_Individual_TrustInfo_assoc " & _
"WHERE tbl_Individuals.individual_id = " & _
"tbl_Individual_TrustInfo_assoc.individual_id " & _
" AND trustinfo_id = " & lng trustinfo_id

Set rst = New ADODB.Recordset

' iterate through recordset and build delimited
' string of initials
With rst
.ActiveConnection = CurrentProject.Connection
.Open _
Source:=strSQL, _
CursorType:=adOpenKeyset, _
Options:=adCmdText

Do While Not .EOF
strInitials = strInitials & "; " & .Fields("IndInitials")
.MoveNext
Loop
.Close
' remove leading semicolon and space
strInitials = Mid$( strInitials, 3)
End With

Set rst = Nothing
GetInitials = strInitials

End Function

You can call it as the ControlSource of an unbound text box in the report
with:

=GetInitials([trustinfo_id])

Ken Sheridan
Stafford, England

Patty said:
I'm trying to concatonate values from a related table for reporting purposes.
I'm on my third day and fourth different approach to getting this done. The
sample code I've tried is designed for 2 tables joined 1:M. I have 3 related
tables, but can't tweak the code to produce what I need. Here's the
background:

T1: tbl_TrustInfo (contains account info, keyed on trustinfo_id)

T2: tbl_Individual_TrustInfo_assoc (contains x-ref of tbl_TrustInfo and
tbl_Individuals)

T3: tbl_Individuals (contains IndInitials to be concatonated)

My goal is to return all selected trusts and the initials of the associated
trustees in one field, separated by semi-colons, if they exist.

Current code is below. It selects all "trustee" types but populates my
concatonated field with all initials of all trustees.

I inserted the line replacing the strSQL string with my own (to no avail!)

'************ Code Start **********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String
'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Examples:
' ?fConcatChild("Order Details", "OrderID", "Quantity", "Long", 10255)
'Where Order Details = Many side table
' OrderID = Primary Key of One side table
' Quantity = Field name to concatenate
' Long = DataType of Primary Key of One Side Table
' 10255 = Value on which return concatenated Quantity
'
Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

strSQL = "SELECT tbl_Individuals.IndInitials FROM tbl_Individuals RIGHT
JOIN (tbl_TrustInfo LEFT JOIN tbl_Individual_TrustInfo_assoc ON
tbl_TrustInfo.trustinfo_id = tbl_Individual_TrustInfo_assoc.trustinfo_id) ON
tbl_Individuals.individual_id = tbl_Individual_TrustInfo_assoc.individual_id
WHERE (((tbl_Individual_TrustInfo_assoc.Type) = 'trustee') And
((tbl_TrustInfo.trustinfo_id) =
[tbl_Individual_TrustInfo_assoc]![trustinfo_id]) And
((tbl_Individual_TrustInfo_assoc.individual_id) =
[tbl_Individuals]![individual_id]))"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & ";"
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function

'************ Code End **********

I hope I've explained things correctly.

TIA

Patty
Click to expand...
Click to expand...
 
G

Guest

Patty:

Sorry about the typo, but glad to hear the function did the trick. Its good
to find someone who actually takes the trouble to debug the code we send
them. Not many do!

Ken Sheridan
Stafford, England
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

fConcatChild issues 7
Trouble with concatenate function 6
Help with Dev Ashish's fConcatChild function 4
Need Help on Code- Attention Allen Browne Please 10
Need help to find most popular records in 2 related Tables 6

Top