populating textfield from multiple records

[Sidney Linkers]

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!

[Pieter Linden]

Don't think pure SQL will do it. You have to use code. There's code for it here:

http://www.mvps.org/access/modules/mdl0004.htm

"Return a concatenated list of sub-record values"

[Sidney Linkers]

Hi,

I still don't know if it can be done with pure SQL, but i got it working
without VBA code which loops through records. (Not to mention the time lost
for opening and closing the recordset.)

The trick is using two queries and a bit of VBA code. It works faster than
looping through records in VBA.

1. The base query GROUPS some Recordkey and 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).

RecID
GROUP BY

Textfield
GROUP BY

PopulatedText: fnPopulateTextfield([RecID];[Textfield])
EXPRESSION

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

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

RecID
GROUP BY

PopulatedText
LAST

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 comma and then returns
the STATIC string var.

Lets say you have a table 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 you will get:

RecID PopulatedTextfield
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*#%! Don't worry after the final query you will get:

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

Voila!

My own code was too complex to show the basic principle, sorry!

Good luck and if you believe hard enough it will happen!

Sidney.

"Pieter Linden" <pi********@hotmail.com> schreef in bericht
news:bf**************************@posting.google.c om...

Don't think pure SQL will do it. You have to use code. There's code for it here:
http://www.mvps.org/access/modules/mdl0004.htm

"Return a concatenated list of sub-record values"

[gus kernot]

G'day

The word you need is "concatenate". I hadn't had a use for that word
either until I started fiddling with this stuff.

Anyway try this for a simple concatenation of two fields that both have
values. The quotes simply put a space between the two words it is the
ampersands that do the joining.

=[Name]&" "&[Hobby]

if you have a group of possibly empty fields you might like to use a
series of immediate ifs such as

=iif(IsNull([Hobby1]),"",[Hobby1]&" ")&
iif(IsNull([Hobby2]),"",[Hobby2]).....

You will see I have also put the intermediate spaces into the iif
expression so that you do not create spaces if the fields have a null
value.

I frequently use that sort of code for concatenating (there's that word
again) town state and postcode fields so they don't look too clunky on
letters

Hope that helps

Cheers
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!