Combine the contents of a field so only one row is displayed

[Jenny]

Dear all

I am trying to combine the contents of a field so that only one row is shown
for a project. I've been trying to do this for a while so if anyone can help
me I'd be really grateful.

The context is that I have a table of projects and a table of companies. A
project may have one or more companies associated with it so I also have a
company relationships table where I link the companies to the projects. I
want to be able to show each project with all it’s associated companies but
with all the companies in one cell, ie one row per project.

First I am joining two tables:

Projects table
Fields: URN (a unique reference number given to each project)

Company relationships table
Fields: Company name - Project URN

to get the following results:

Projects and their associated companies query
Fields: URN - Company name
Row 1: 123-456 - Company A
Row 2: 123-456 - Company B
Row 3: 123-789 - Company C
Row 4: 123-789 - Company D
Row 5: 123-789 - Company E


So far so normal. However, what I actually want to show is:

Projects and their associated companies query
Fields: URN - Company names
Row 1: 123-456 - Company A, Company B
Row 2: 123-789 - Company C, Company D, Company E

Can anyone tell me if this is possible and if so, how to do it?


Many thanks for your time,
Jenny

 

[Ken Snell [MVP]]

Duane Hookom has a function in a sample database that you can use for doing
this:
Generic Function To Concatenate Child Records
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane


I also have a revised version of Duane's function here:
ConcatenateFieldValues
http://www.accessmvp.com/KDSnell/VBA_Functions.htm#ConcatFlds


For either one, you put the function in a regular module, then you call the
function from the query that provides the data for your form/report.