Stuck on Access concatenating into string

[orangeman2003]

So I'm having a hell of a time here with this issue. I've gone to Allen Browne's page and tried to use his SQL function but it did not work. Basically I have a mapping of a file name to the columns in that file. I have about 30 files and while most have 5-10 headings, one of the files has 40. Because of this I want to display the values in sequential format with a comma separator rather than rows, such as (column1, column2, column3, etc.) I want to do this because my report looks dumb having a record with 40 rows with one word each. How can I do this in access 2007?



I do not want to combine every result into one record rather where a value is >1 (in my case more than one column in a file) I want that listed sequentially instead of rows.

[June7]

Don't know what SQL function you are referring to but have you looked at Allen Browne's ConcatRelated code? http://allenbrowne.com/func-concat.html

Or you can try a multi-column report. Set this with Page Setup dialog.

[orangeman2003]

Hi,

I have attached a screen shot to give you a better understanding of what I'm trying to do. This is a screen shot from a report where for this record, there are a ton of values for the 'SAN' record. What I want to do is take all of those values and format them so they appear one after the other separated by a comma instead of using 1 row for each record. It would go (COSTCENTER, BUSINESSUNIT, HOSTNAME, MONTHLYCOST2)......

[June7]

One of the suggestions offered should accomplish that.

[orangeman2003]

One of the suggestions offered should accomplish that.

Trust me, I spent hours yesterday on the Allen Browne site, just cant get either the VBA or the SQL to do what I want.

[June7]

How have you attempted to use Allen Browne code? What exactly happens when you attempt? Error message, wrong results, nothing? We need something to analyze. Of course, it is possible the resulting concatenated list string could be too long for Access limitations but that is unlikely.

It also appears that multi-column report would be suitable.

[amrut]

June7 is already helping you out. I just want to add my bit. The ConcatRelated function by AllenBrowne is to combine multiple records from a single field.
Are you sure the data you wish to concatenate has records in a single field ?