Merge multiple detail records in a single control

[toehrtman]

I'm creating a report in Access 2003 where each record has multiple keywords
I have assigned to the record. The parent records and related keywords are
in separate tables combined with a query, that query serves as the source for
this report. The report lists the parent record in the header then all the
keywords as separate lines in the detail section. In view mode the keywords
are displayed as a column and causes each record to run on for multiple pages.

Is it possible to create one control box that merges all the related
keywords from the same record into a single display, separating each new
record with a coma or other separator. Example: display detail records in
this format 1, 2, 3, 4 versus
1
2
3
4

I've been wracking my brain on this one for a while now, I'm fully stumped!!
I’m convinced that it’s possible, but I can’t figure it out. Any help would
be greatly appreciated.

 

[KARL DEWEY]

One way is to use Duane Hookom's Concatenate function.

Another is like this --
- Create a Ranking in a Group query so that all related keyword records are
numbered.
- Then create a query with multiple instance of the Ranking in a Group
query, left joined on appropriate fields. Use the KeyWord and Rank fields
with criteria number for the Rank.
- One more query to concatenate those KeyWord fields that are not null.

 

[toehrtman]

Thanks for pointing me in the right direction, Duane Hookom's Concatenate
Function is exactly the answer I was looking for, HUGE HELP! I looked at
Duane's examples and structured an SQL statement in the query to concatenate
all my child records. It seems like it should work, I copied the example
exactly, but when I run the query I get an error message reading "Undefined
function 'Concatenate' in expression." What am I doing wrong?

 

[Duane Hookom]

Did you include the module in your application?