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

Several posts in these forums on this subject. Try this one:
http://www.access-programmers.co.uk/forums/showthread.php?t=64611

Thanks very much for this. I copied the module from the database and used the function on my own data. The query works but there is no data in the concatenated field.

Do you know of a reason why this may be?

Thanks,
JEnny

No idea. Do I just have to guess? :D

If you post a stripped down version of you db maybe someone will have a look.

Ok, thanks very much anyway. I spotted something in the code that I have changed and it works now but is quite slow. I will just have to keep plugging away...

Thanks for finding the thread for me, all the search terms I used just seemed to pull up things on concatenating more than one field.

JEnny

I'd expect it to be slow because it's looping through all of your records for each URN.

Pleased you got it working, though.