Hi Mark,
Your question was posted a whileago, but maybe it still boggles your
mind. Many programmers use the concept of looping through a recordset to
build up a textfield from multiple records. I also did, but was not happy
with the result. You will see if you have to proces thousands of records. So
i took a different approach.
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 (with recordlooping in VBA you can see the
textfield being buildup in queryview...not cool, with my solution it
displays instantly!...very cool).
1. The base query "qryYourBaseQueryForPopulating" GROUPS the Recordkey
GROUPS the Textfield you want to populate. In a 3th column a call to
fnResetPopulation() ensures that you will startoff clean with the populated
text. In a 4th 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 2nd 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.
If you still have questions about this topic or even have suggestions please
let me know.
Good luck!
P.S. you can even do this with other datatypes as log as you convert them to
string before passing it to fnPopulateTextField().
Sidney.
.