StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can concatenate the records with a User Defined Function (UDF).</p> <p>The code below can be pasted 'as is' into a standard module. The SQL for you example would be:</p> <pre><code>SELECT tbl.A, Concatenate("SELECT B FROM tbl WHERE A = " & [A]) AS ConcA FROM tbl GROUP BY tbl.A </code></pre> <p>This code is by DHookom, Access MVP, and is taken from <a href="http://www.tek-tips.com/faqs.cfm?fid=4233" rel="noreferrer">http://www.tek-tips.com/faqs.cfm?fid=4233</a></p> <pre><code>Function Concatenate(pstrSQL As String, _ Optional pstrDelim As String = ", ") _ As String 'example 'tblFamily with FamID as numeric primary key 'tblFamMem with FamID, FirstName, DOB,... 'return a comma separated list of FirstNames 'for a FamID ' John, Mary, Susan 'in a Query '(This SQL statement assumes FamID is numeric) '=================================== 'SELECT FamID, 'Concatenate("SELECT FirstName FROM tblFamMem ' WHERE FamID =" & [FamID]) as FirstNames 'FROM tblFamily '=================================== ' 'If the FamID is a string then the SQL would be '=================================== 'SELECT FamID, 'Concatenate("SELECT FirstName FROM tblFamMem ' WHERE FamID =""" & [FamID] & """") as FirstNames 'FROM tblFamily '=================================== '======For DAO uncomment next 4 lines======= '====== comment out ADO below ======= 'Dim db As DAO.Database 'Dim rs As DAO.Recordset 'Set db = CurrentDb 'Set rs = db.OpenRecordset(pstrSQL) '======For ADO uncomment next two lines===== '====== comment out DAO above ====== Dim rs As New ADODB.Recordset rs.Open pstrSQL, CurrentProject.Connection, _ adOpenKeyset, adLockOptimistic Dim strConcat As String 'build return string With rs If Not .EOF Then .MoveFirst Do While Not .EOF strConcat = strConcat & _ .Fields(0) & pstrDelim .MoveNext Loop End If .Close End With Set rs = Nothing '====== uncomment next line for DAO ======== 'Set db = Nothing If Len(strConcat) > 0 Then strConcat = Left(strConcat, _ Len(strConcat) - Len(pstrDelim)) End If Concatenate = strConcat End Function </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload