Using junction tables - output to excel

[Geo]

Having read through much advice in this newsgroup about design of table, I now
have a problem in exporting sensible information to excel.
My main table has several 1-to-many relationships and there are about 25 tables
in the backend.
For example, my main table describes a journey where there may be 0 to 3
escorts. The junction table contains the (say) 3 records pointing to the escort
type table. This all works as expected within Access.
I created a query to include all fields of the main table (except the foreign
keys) and the fields from the other tables as required as defined in the
relationships.
My problem is that the query (and the exported excel file) will contain 3 rows
for the journey because of the 3 escorts. Is there some way to concatenate the
escorts into one column for output, to eliminate the duplicate rows?
There is a similar problem with a couple of other tables (e.g. incidents
enroute) so I am interested in a general solution.
Quite happy to use vba and/or create a temporary table as required.
Apologies if this is not the correct newsgroup (but the problem seems to be
related to tables...)

Geo

 

[Duane Hookom]

I answered a similar question in these news groups within the last couple
days. The question comes up several times per week. There is a generic
concateate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

It might not run very quickly if you have a lot of records but should allow
you to concatenate the child table values.

 

[Geo]

I answered a similar question in these news groups within the last couple
days. The question comes up several times per week. There is a generic
concateate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Thanks for the quick reply Duane. Sorry I missed the previous postings. I have
downloaded the headers from 4 or 5 Access newsgroups (forms,reports etc) and
have benn ploughing through all the messages since about June last year (still
nearly 9000 to go in the reports group). I hate it when the same question (or
variations) keep cropping up in my regular groups and tried to avoid putting my
foot in it here - but obviously failed...
I have of course downloaded your sample and it looks just what I need.
Many thanks for your patience.
Geo

 

[John W. Vinson]

I have
downloaded the headers from 4 or 5 Access newsgroups (forms,reports etc) and
have benn ploughing through all the messages since about June last year (still
nearly 9000 to go in the reports group).

Save yourself some effort... http://groups.google.com, advanced search. Select
the microsoft.public.access.* groups and search by terms in the posts.