aggregate functions for TEXT data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am not sure if this can be done with ms access, but I am hoping to get some
ideas. here is a crude approximation of the table, two columns, id and
text_field:

id text_field
-----------------
1 hello
1 my name is mud

I would like to be able to use access to find all rows with the same id
value, and then combine all the text_field values for those rows with the
same id value, and then write all this into a new table, with a space between
the strings that are combined. for the data shown above, the end result in
the new table would be:

id text_field
1 hello my name is mud

I cant find a way to do this in access. There seem to be no aggregate
functions for string data. (SUM function only works for numbers)

My only plausible idea so far is to somehow somewhere use an array to hold
the values, and then loop through them and then combine them, but I can find
ZERO documentation on using arrays in MS access VBA to get data from a table,
query or report. If you have any suggestions, fire away! thanks
 
Hi Ken,

set rec_trg=currentdb.openrecordset("select * from your
desttable",dbopendynaset)
' desttable is the name of the table where you wanna the data
set rec=currentdb.openrecordset("select id from yourtable group by
id",dbopendynaset)
' yourtable is the name of the table that contain the stings you wanna merge
do while not rec.eof
set rec_str=currentdb.openrecordset("select * from yourtable where id="
& rec![id],dbopendynaset)
tmp_str=""
do while not rec_str.eof
tmp_str=tmp_str & rec_str![text_field] & " "
rec_str.movenext
loop
tmp_str=left(tmp_str;len(tmp_str)-1) ' to remove the last space
rec_trg.addnew
rec_trg![id]=rec![id]
rec_trg![text_field]=tmp_str
rec_trg.update
rec.movenext
loop

With this code you do the trick, but the string will be composed in random
order 'cause I didn't see a field with the correct order of the pieces to
compose the complete string...

HTH Paolo
 
paolo's code should do it.
Be aware; however, the data type for the destination field needs to be memo.
A text data type can only hold as many characteres as defined up to a
maximum of 255.
 
Back
Top