How to combine multiple child records to a parent record?

[mancusoj]

I have two tables one with Parents information and another table with children information. I want to be able to combine the parents with all there children in one record for printing labels. I have a module that is suppose to combine the information. Its not working.
Function CombineChildRecords(strTblQryIn As String, _
strFieldNameIn As String, strLinkChildFieldNameIn As String, _
varPKVvalue As Variant, Optional strDelimiter) As Variant

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
Dim varResult As Variant

Set db = CurrentDb
Set qd = db.CreateQueryDef("&quot

If IsMissing(strDelimiter) Then strDelimiter = "; "
strSQL = "SELECT " & strFieldNameIn & " FROM " & strTblQryIn & ""
qd.SQL = strSQL & " WHERE " & strLinkChildFieldNameIn & " = [ParamIn]"
qd.Parameters("ParamIn&quot.Value = varPKVvalue

Set rs = qd.OpenRecordset()

Do Until rs.EOF
varResult = varResult & rs.Fields(strFieldNameIn).Value & strDelimiter
rs.MoveNext
Loop

rs.Close

If Len(varResult) > 0 Then varResult = Left$(varResult, _
Len(varResult) - 2)

CombineChildRecords = varResult

Set rs = Nothing
Set qd = Nothing
Set db = Nothing
End Function
And a query that takes the data through the function to display it as one record.
SELECT Parents.ParentID, Parent.FName,
Parents.LName, CombineChildRecords("Children","Name","ParentID",[ParentID],",&quot AS ChildrenList
FROM Parents;
I keep getting compile errors.
Please help

 

[SkipVought]

Hi,

There must be a relationship between the parent table and the child table.

The results can be returned in a single query.

Suppose that I have a CUSTOMERS table and an ORDERS table.

The CUSTOMERS table has a bunch of stuff about each customer INCLUDING a CUST_ID.

The ORDERS table has a bunch of stuff about each order INCLUDING CUST_ID but does NOT have anything else about CUSTOMERS.

I could write a query like this...

SELECT CUS.NAME, ORD.TOTAL
FROM CUSTOMERS CUS, ORDERS ORD
WHERE CUS.CUST_ID=ORD.CUST_ID

because I defined the relationship(s) in the WHERE clause.

That's what you need to do.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mancusoj]

Thanks, I understand what you are saying. I need to clarify what I meant by combining the information. I need the mothers name in one column and her children if she has multiple children in one column. When I print a label it will print the mothers name and all her children on one label. With a query it creates seperate label for each child. Sorry it took so long to respond but the site was down. Please any suggestion would be great.

 

[SkipVought]

Then create a loop using the parent table.

Within the loop use the parent value in the WHERE clause to select the children values.

Then format and print -- get next parent.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com