Hi,
I'm trying to make a calculated text field in a query where the textvalue is
being populated from multiple records.
I already use a VBA function to loop through records and concatenate the
text. It works but the performance is really bad. I'm looking for a solution
in plane SQL, so the looping is done by joining tables. I can't get it to
work....
tblPersons
Persid name
------ ------
1 Dck
2 Peter
3 John
4 Paul
5 Oliver
tblHobbies
Persid Hobby
------ -------
1 Cars
1 Fishing
1 Movies
1 Horses
2 Cars
2 Music
3 Golf
3 Cars
3 Hunting
4 Cooking
5 Cars
5 Music
in the query i like to have the following data presented in two fields.
Name Hobbies
------ ---------
Dick Cars, Fishing, Movies, Horses
Peter Cars, Music
John Golf, Cars, Hunting
Paul Cooking
Oliver Cars, Music
I almost thought i had the solution by using GROUP BY and EXPRESSION in a
total query where a VBA function is called and populates the strings through
a STATIC string variable and STATIC long recordid. But unfortunatly it
didn't work well, got problems with initializing STATIC vars (Also in
genereal it seems that during debugging a function called from a running
query doesnot always return the expected results, while in normal mode the
query will run fine).
Any help would be gratefull
Thanks!