concatenate rows into one

[PaulGoer]

Hi All - first time posting.
I apologize if this is here somewhere - I have searched and cannot seem to find it.

I have a table named "Problems" in Access 2016 that has two columns: County, Issue

Here is sample data:

County	Issue
Conner	Parks
Conner	Roads
Conner	Utilities
Conner	Water
Folk	Parks
Preston	Parks
Preston	Water

I want to run a query that will take the issues for each county and place them in a field, like this:

County	Issues
Conner	Parks, Roads, Utilities, Water
Folk	Parks
Preston	Parks, Water

Even better would be if carriage returns are possible:

County	Issues
Conner	Parks Roads Utilities Water
Folk	Parks
Preston	Parks Water

I am stumped. If anyone has done this and can share any ideas, it would be greatly appreciated.

THANKS
Paul

[ssanfu]

Maybe this by Allen Browne?
Concatenate values from related records

For the second example, you would/could use a temp table and base a report off of the records.

[PaulGoer]

I have been unable to make Allen's function work. I think I simply don't know the proper form for the query.
As for a report, that will not work because I need to export the results as a file (Excel or RTF) so that I can do a mail merge to send email with the results.

Maybe this by Allen Browne?
Concatenate values from related records

For the second example, you would/could use a temp table and base a report off of the records.

[ssanfu]

Well, another option is you could "brute force" the export of the data to WORD using VBA, depending your coding skills.

Here is one example MS Access – VBA – Export Records to MS Word
Obviously you would have to modify the code.... but if you have the Access record set already developed, it shouldn't be that difficult. (but I have never done this with Word).




BTW, I searched on "export access records to word" (no quotes).

[Bob Fitz]

I have been unable to make Allen's function work. I think I simply don't know the proper form for the query.
As for a report, that will not work because I need to export the results as a file (Excel or RTF) so that I can do a mail merge to send email with the results.

Can you post a copy of your db.

[Carouser]

Use Access Reports to display your data with the format you require from your query. Easily export as a PDF or print

[June7]

If you want each issue on a line, how is this different from a normal report design? Use textbox HideDuplicates property for the county.

Access can email the report as a PDF attachment. Or build a text string and embed in email body. Even use HTML tags to build a table in the body.

Unless you need a lot of formatted text from a Word document suggest avoiding mail merge.

[Minty]

Cross posted here http://www.utteraccess.com/forum/ind...ic=2050383&hl=