Consolidate and concatenate text

  • Thread starter Thread starter K Kindle
  • Start date Start date
K

K Kindle

I have a table that has notes related to records in
another table

Key OtherTableKey Note
1 2 "Some sort of note"
2 2 "Another note"
3 1 "A note on a different record"

I would like a query to return all notes concatenated
together by the OtherTableKey. E.g.,

OtherTableKey ConcatenatedNote
1 "A note on a different record"
2 "Some sort of note, Another note"

Can anyone provide any suggestions? (There is no limit to
the number of Notes associated with the OtherTableKey, but
it is not likely to be more than 6-10).

Thanks in advance!
KK
 
Thanks, I have done this using a function that processed a
record set similar to the solution you provided, but
performance is poor. I was hoping that there was a
solution that did not require use of a function such as
this.
-KK
-----Original Message-----
Here is the source code for a function to do what you need:

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

This code uses a semi-colon instead of a comma, but you
Click to expand...
can change that easily enough. You will need to create a
Module and paste this code into it in order for your query
to be able to use the function.
 
I can think of a few oddball approaches like hiding the detail section of a report, having some code accumulate the string as the report progresses and then showing the data in a dummy "detail" section that is actually a Footer, but you would be adding a lot of unnecessary complexity to something and it would be problematic to maintain. The function is definitely the better (and more easily reusable) approach

If performance is a problem, perhaps you could pull a subset of your data into a temp table and run a second query (containing the concatenate function) against that. You would have some overhead with running a delete and an append query before running the report (which could be automated either through code or a Macro). That would at least cut the volume of records the function had to deal with

Ro


----- K Kindle wrote: ----

Thanks, I have done this using a function that processed a
record set similar to the solution you provided, but
performance is poor. I was hoping that there was a
solution that did not require use of a function such as
this
-K
-----Original Message----
Here is the source code for a function to do what you need
Click to expand...
can change that easily enough. You will need to create a
Module and paste this code into it in order for your query
to be able to use the function
 
Back
Top