how To Concatenate Child Records?

[Jon]

Hi,

I try Duane Hookom example of Concatenate Child Records but I could not
apply it very well

My example like this:

A table with records like:
ABC Co. Bob
ABC Co. John
ABC Co. Sarah
ABC Co. Aaron
can be converted to:
ABC Co. Bob, John, Sarah, Aaron
or
ABC Co. Bob - John - Sarah – Aaron

I want to apply this example on report. In this report, I have more than
these child records?

Any help please??

 

[Ken Sheridan]

Here's a little function of mine which lists all addressees at an address as
a string along the lines of 'Bob White, John Green, Sarah Brown and Aaron
Black':

Public Function GetAddressees(strAddress As String) As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strAddressees As String

strSQL = "SELECT FirstName & "" "" & LastName " & _
"AS FullName FROM Addresses " & _
"WHERE Address = """ & strAddress & _
""" ORDER BY LastName, FirstName"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

With rst
Do While Not .EOF
strAddressees = strAddressees & ", " & _
.Fields("FullName")
.MoveNext
Loop
.Close
' remove leading comma and space
strAddressees = Mid$(strAddressees, 3)
End With

' replace final comma, if exists, with 'and'
If InStr(strAddressees, ",") > 0 Then
strAddressees = Left(strAddressees, InStrRev(strAddressees, ",") -
1) & _
" and" & Mid(strAddressees, InStrRev(strAddressees, ",") + 1)
End If

GetAddressees = strAddressees

End Function

But as you are doing this in a report then an 'across then down'
multi-column subreport would be an easy solution. You'll find an example at:


http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24271


The file which you can download from that link also includes a means of
doing it as a single report by manipulating the report's layout at runtime by
means of code in its module. That however was only produced to show that it
could be done, in answer to a reader's request to a contact of mine who
writes a databases column for a magazine. It produces exactly the same end
result as the subreport solution, but a subreport is much easier, requiring
no code whatsoever.

Ken Sheridan
Stafford, England

 

[Duane Hookom]

What did you try? Did you import the module into your mdb? What expression
did you use? Assuming your table and field names are tblCompEmps, Comp, and
Emp:
Concatenate("SELECT Emp FROM tblCompEmps where Comp=""" & [Comp] & """")

If this doesn't work, come back with actual table and field names and data
types.

 

[Jon]

hi,

Duane, I imported the module and put the expression below in the field
source but it does not work.

my table is called "Left atrium tbl" , fields are Left atrium & PatientFile.

I have also abut 10 tables with Different name but PatientFile is in every
table.

I try the expression but it asks me about “Concatenateâ€. Please advice?

Thank you all

What did you try? Did you import the module into your mdb? What expression
did you use? Assuming your table and field names are tblCompEmps, Comp, and
Emp:
Concatenate("SELECT Emp FROM tblCompEmps where Comp=""" & [Comp] & """")

If this doesn't work, come back with actual table and field names and data
types.
--
Duane Hookom
Microsoft Access MVP

Hi,

I try Duane Hookom example of Concatenate Child Records but I could not
apply it very well

My example like this:

A table with records like:
ABC Co. Bob
ABC Co. John
ABC Co. Sarah
ABC Co. Aaron
can be converted to:
ABC Co. Bob, John, Sarah, Aaron
or
ABC Co. Bob - John - Sarah – Aaron

I want to apply this example on report. In this report, I have more than
these child records?

Any help please??

 

[Jon]

by the way, PatientFile is text field and Left atrium is text also.

What did you try? Did you import the module into your mdb? What expression
did you use? Assuming your table and field names are tblCompEmps, Comp, and
Emp:
Concatenate("SELECT Emp FROM tblCompEmps where Comp=""" & [Comp] & """")

If this doesn't work, come back with actual table and field names and data
types.
--
Duane Hookom
Microsoft Access MVP

Hi,

I try Duane Hookom example of Concatenate Child Records but I could not
apply it very well

My example like this:

A table with records like:
ABC Co. Bob
ABC Co. John
ABC Co. Sarah
ABC Co. Aaron
can be converted to:
ABC Co. Bob, John, Sarah, Aaron
or
ABC Co. Bob - John - Sarah – Aaron

I want to apply this example on report. In this report, I have more than
these child records?

Any help please??