Concatinating a text field from different records

  • Thread starter Thread starter Mario
  • Start date Start date
M

Mario

Lets say I have a table like this:

StudentName |Evaluator |Comments
-----------------------------------------
David |Mary |Need Improvment
David |Mitchell |Good
David |Marco |Reading needs attention
Jeena |Stefany |Excellent


Can somebody help me with a querry syntax that can
produced the following results;

StudentName |Evaluators_Comments
------------------------------------
David |Needs Improvement, Good Reading, needs
attention
Jeena |Excellent

Please help.
 
Thank you very much sir.

Your function was like a boon from god.

I gues the function you wrote is in VBA. Can u sugest me
some free online resources for VBA. Also what is the
difference between DAO and ADO.

DAO - Data Access Objects?? (For MS Access etc..,)??
ADO - ActiveX Data Objects??? (for Active Server Pages
etc.,)??

-----Original Message-----
You can use the following function in a query that contains only one record
per student. Copy the function into a new module and save it as
"basConcatenate". There is sample usage provided in the code comments.

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
'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

--
Duane Hookom
MS Access MVP


Mario said:
Lets say I have a table like this:

StudentName |Evaluator |Comments
-----------------------------------------
David |Mary |Need Improvment
David |Mitchell |Good
David |Marco |Reading needs attention
Jeena |Stefany |Excellent


Can somebody help me with a querry syntax that can
produced the following results;

StudentName |Evaluators_Comments
------------------------------------
David |Needs Improvement, Good Reading, needs
attention
Jeena |Excellent

Please help.
Click to expand...


.
Click to expand...
 
Hi Mario,

I still don't know if it can be done with pure SQL, but i got it working
without recordset looping in VBA.
The trick is using two queries and a bit of VBA code to build up a recordset
with a recordkey and a populated textfield. Just join to this query through
the recordkey to get the populated textfields. It works faster than
recordset looping in VBA (you can see the textfield being buildup in
queryview...not cool).

1. The base query "qryYourBaseQueryForPopulating" GROUPS the Recordkey
GROUPS the Textfield you want to populate. In a 3th column a calculated
textfield as EXPRESSION calls a public function fnPopulateTextfield which
takes to params (Recordkey and Textfield).

In this query put 4 columns like:

RecID
tblYourSecundairyTable
GROUP BY
Show

SomeTextfieldYouWantToPopulate
tblYourSecundairyTable
GROUP BY
Don't show

fnResetPopulation()

GROUP BY
Don't show

PopulatedText: fnPopulateTextfield([RecID];[SomeTextfieldYouWantToPopulate]
& "")

EXPRESSION
Show

The second column is needed because without it the query does not recognizes
that "SomeTextfieldYouWantToPopulate" is part of a statistic function! The
side effect is that it will group on every "SomeTextfieldYouWantToPopulate",
but the final query will correct this!

2. The finishing query "qryYourFinishingQueryForPopulating" based on the
above query GROUPS again on the Recordkey and takes the LAST record of the
calculated populated textfield.

In this query put 2 columns like:

RecID
qryYourBaseQueryForPopulating
GROUP BY
Show

PopulatedText
qryYourBaseQueryForPopulating
LAST
Show

3. The function fnPopulateTextfield uses two STATIC vars (long and string).
Every time it sees a different recordkey it reinitializes the STATIC vars
(Long var gets new recordkey and the String var gets empty). Next it adds
the Textfield to the STATIC string var seperated by a comma and then returns
the STATIC string var.

Below you'll find the VBCode needed for populating textfields

----------------------------------------------------------------------------
-----------
Option Compare Database
Option Explicit
Private strPopulatedText As String 'Hold the string to populate
Private lngPopulatingID As Long 'Rember the records to populate RecordID
----------------------------------------------------------------------------
-----------
Public Function fnResetPopulation()
lngPopulatingID = 0
strPopulatedText = ""
End Function
----------------------------------------------------------------------------
------------
Public Function fnPopulateTextField(lngRecordsToPopulateID As Long,
strTextfieldToPopulate As String) As String
'Only if a different RecordID is passed then reset the static vars
If lngRecordsToPopulateID <> lngPopulatingID Then
lngPopulatingID = lngRecordsToPopulateID
strPopulatedText = ""
End If
'Append the passed textfield to the static var
strPopulatedText = strPopulatedText & IIf(Len(strPopulatedText) > 0, ", ",
"") & strTextfieldToPopulate
'Return the populated text so far populated
fnPopulateTextField = strPopulatedText
End Function
----------------------------------------------------------------------------
------------

Now an example:

Lets say you have a secundairytable "tblYourSecundairyTable" filled like the
next one :

RecID SomeTextfieldYouWantToPopulate
1 Cat
1 Dog
2 Fish
3 Lion
3 Birth
3 Snake
4 Dog
4 Crock
4 Hyena
4 Fish

After the base query "qryYourBaseQueryForPopulating" you will get:

RecID PopulatedText
1 Cat
1 Cat, Dog
2 Fish
3 Lion
3 Lion, Birth
3 Lion, Birth, Snake
4 Dog
4 Dog, Crock
4 Dog, Crock, Hyena
4 Dog, Crock, Hyena, Fish

What the f*#% happend! Don't worry after the final query
"qryYourFinishingQueryForPopulating" you will get:

RecID PopulatedText
1 Cat, Dog
2 Fish
3 Lion, Birth, Snake
4 Dog, Crock, Hyena, Fish

Voila! Now it's just a matter of joining to this query from whereever you
want as long as you LEFT JOIN a RecID to
qryYourFinishingQueryForPopulating.RecID.

Good luck!

Sidney.
 
Back
Top