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

[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

 

[Sergio G]

Can anybody help?
I've set up my email account and have successfully sent out test emails as
well as received.
But success rate on sending is about 50%.
What I get just as often is an error message that reads as below...
What does the below mean? What am I doing wrong?

ERROR MMESSAGE:
"An unknown error has occurred. Subject 'Read: Welcome to 53 St Martin's
Lane', Account: 'mail.sergiogiannasso.co.uk', Server:
'mail.sergiogiannasso.co.uk', Protocol: SMTP, Server Response: '554 Too many
nonmail commands', Port: 25, Secure(SSL): No, Server Error: 554, Error
Number: 0x800CCC6F

SG

 

[Beetle]

Have a look at Duane Hookom's Concatenate example application
at this link;

http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4

 

[John W. Vinson]

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?

You need a bit of VBA to do this. See
http://www.mvps.org/access/modules/mdl0004.htm
for sample code.

 

[John W. Vinson]

Can anybody help?
I've set up my email account and have successfully sent out test emails as
well as received.
But success rate on sending is about 50%.
What I get just as often is an error message that reads as below...
What does the below mean? What am I doing wrong?

ERROR MMESSAGE:
"An unknown error has occurred. Subject 'Read: Welcome to 53 St Martin's
Lane', Account: 'mail.sergiogiannasso.co.uk', Server:
'mail.sergiogiannasso.co.uk', Protocol: SMTP, Server Response: '554 Too many
nonmail commands', Port: 25, Secure(SSL): No, Server Error: 554, Error
Number: 0x800CCC6F

SG

Are you sending the mail from an Access database (the subject of this
newsgroup)? If so, please post your code. If not please post in a newsgroup
appropriate to your software.