query assistance- need to combine text

[smiles]

I'm trying to combine an unknown amount of text together in a query. Basically the opposite of parsing a field.

As an example, let say I have the following table with 2 columns (code and message) with the following records.

code message
----- ------
AA neat
AA great
BB exciting
BB dull
BB neat
CC great

This is the recordset I like to present.

code message
----- -----------
AA neat, great
BB exciting, dull, neat
CC great

Does anyone have a suggestion for accomplishing this?

I'm familiar with crosstabs and know can I do the following:

TRANSFORM Min(TestTable.message) AS MinOfmessage
SELECT TestTable.code
FROM TestTable
GROUP BY TestTable.code
PIVOT "message" & DCount("code","TestTable","message <='" & [message] & "' AND code = '" &

 & "'");

And get this recordset:

code	message1	message2	message3
----      -----------        -----------         ------------
AA	great	             neat	
BB	dull	               exciting	          neat
CC	great		

Which I can then concatenate by using message1 & iif(message2 <> "",) ", " & message2, "") & iif(message3 <> "",) ", " & message3, "")

The only problem with this crosstab approach is that I won't know how many messsage there are.  There many be 1, 2, 3 or 10.  Is there a more flexible/dynamic method?  Is the JOIN/SPLIT function a possibility?

I appreciate any thoughts!  Thanks in advance for the assistance.

 

[CT Witter]

Ok, this based on the solution at MS KB Article #322813-ACC2000: How to Combine Multiple Child Records into One Record
http://support.microsoft.com/kb/322813

First, create the code-using Alt+F11

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("")

   If IsMissing(strDelimiter) Then strDelimiter = "; "
   strSQL = "SELECT [" & strFieldNameIn & "] FROM [" & strTblQryIn & "]"
   qd.SQL = strSQL & " WHERE [" & strLinkChildFieldNameIn & "] = [ParamIn]"
   qd.Parameters("ParamIn").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

Now create a query, you will need to change the field and table names to match your application.

SELECT DISTINCT Code, CombineChildRecords("tblNAME","Message","Code",[code*],", ") AS FieldList
FROM tblTest
ORDER BY tblTest.Code;

where code* is actually code but the board won't recognize properly
HTH,
CT

 

[Richard Schollar]

CT

That is sweet code - I learn something new every day! I didn't realise you could devise custom functions for use in Access SQL...

Best regards

Richard

 

[smiles]

It works perfectly. CT Witter, can't thank you enough! I'll be using this every where.

 

[CT Witter]

Your welcome!

 

[rturanc]

Receiving an error message when running code

CT Witter,

I tried using the code you provided and keep receiving the error message "undefined function 'CombineChildRecords' in expression." Not sure why the SQL does not find the global module provided. Can you help?

Thanks!
rturanc

 

[CT Witter]

Make sure your module isn't named the same thing 'CombineChildRecords'.

Post back if that doesn't work.

CT

 

[PanzerAttack]

I thought I would reply here rather than my post. Thanks CT Writer, it does look good but I'm not that great at understanding what it's doing. This is my query:

SELECT DISTINCT Code, CombineRecords([TBL00_Rebate Agreements],"Message","Code",[code*],", ") AS FieldList
FROM [TBL00_Rebate Agreements]

When I run some input boxes come up. I'm not sure what needs to go in to them, but I have 76k rows in my table and 9k of them appear to have duplicates.

What does "Message" refer to?
What does "Code" refer to?
What does "Code*" refer to?

 

[CT Witter]

Message = strFieldNameIn As String in the function = The name of the field that contains the data that you want.

Code = strLinkChildFieldNameIn As String = The name of the field on which the child records link.

Code* is the same as above, but the forum board didn't like the word code so I changed to code*

If you create the table like smiles listed as the example, you will be able to see how the function works.

HTH,
CT