Columns to List data separated by a coma

[puppy39]

I have a one to many relationship table and I would like to display the many relationship data in a report separated by a coma instead of a column. Any suggestions...

Example
This is what it displays now

LeadID CountyName
102 Orange
102 Lake

104 Orange
104 Lake
104 Polk
104 Seminole

This is what I am trying to accomplish

102 Orange, Lake
104 Orange, Lake, Polk, Seminole

 

[Remou]

This FAQ should help: FAQ701-4233

http://lessthandot.com

 

[puppy39]

I am trying to apply this to a report not a query. I am currently using ADP with SQL. Where and how do I call the module. Do I call the module in the OnOpen Event of the report?

 

[dhookom]

I'm not sure this solution will work in an ADP and don't have the time to test it. You could try a multi-column subreport that displays across then down.

There are SQL solutions that might allow you to duplicate the Concatenate() function with an SQL Server function or stored procedure. Again, this is a bit more than I have time to test. You might want to try google or post a question to the SQL Server forum.

Duane
Hook'D on Access
MS Access MVP

 

[puppy39]

I can work with the multi-column but How do I create It? I have been trying to do that all day but no matter what I do I still get only one column

ReferralID
102
Orange
Lake

104
Orange
Lake
Polk
Seminole

 

[dhookom]

You should be able to use a multiple column subreport where the columns display Across then Down.

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

This may be of interest:

http://www.sqlmag.com/Article/ArticleID/96784/sql_server_96784.html

http://lessthandot.com

 

[GShen]

Not sure where you are with all of this.

You can do a group break on the LeadID and then have the subreport in the break like Dhookom stated. You set the columns in the page setup.
1)Create the main report with the group break.
2)Then create another report (this will be your sub report).
3)In page setup (on the sub report) set the columns to whatever you can fit. Across and then down.
3)Reopen the main report and click on the subreport tab. Look for your report and add it.

Hope this helps. Ping me back if you need more info.

Remember when... everything worked and there was a reason for it?

 

[puppy39]

Yep that is how I have it set up. My problem is with the sub-report/column setup that I can't get it to display in columns. This is what I have in the PageSetup

Number of Columns: 4
Row Spacing: 0.5"
Column Spacing: 0.25
Column Size: Width 1.5 Height 0.1875
Column Layout: Across, then Down

Yet what I get in the preview is

Lead ID 1
Hills
Pinellas

Lead ID 2
Brevard
Hill
Lake
Marion

What I am doing wrong? Is it that I can't have grouped by LeadID? :-(

 

[GShen]

Your grouping should be fine. Try running the subreport stand alone and see if you get the columns coming out properly. Your have this in the header on the grouping, yes ?

Remember when... everything worked and there was a reason for it?

 

[puppy39]

Running the subreport stand alone does not give me the columns. What do you mean by "Your have this in the header on the grouping, Yes?

 

[GShen]

When you do a grouping, there is a header and a footer. I wanted to know where you had it.

Ok, we ruled out the report/subreport problem. It is something with the columnized report itself.

Just a stupid question. Do you have your report pulled out wide enough for this ? Your report should be 4 * 1.5 + .75 = 6.75 inches.



Remember when... everything worked and there was a reason for it?

 

[dhookom]

puppy39,
What are the Link Master/Child fields? How wide is the subreport control on the main report? Is it at least 7" wide?

Duane
Hook'D on Access
MS Access MVP

 

[puppy39]

OMG!!! I feel like a total....

The only way you can actually see the columns is if you view the report in the Print Preview!!!! I was always in Report View. Office 2007 has Report View, Print Preview, Layout View and Design View. The only one that truly displays the end result of the report is Print Preview. I had Office 2007 installed last month still learning.

I can't thank you all enough for hanging in there and replying back to me! I would still like to have it separated by a coma this way it will take less space on the report but for now I will work with this.

If I can bother all of you with one more thing. The reason I changed the database from mdb to adp was because every thing I read recomended to that. Can I switch back? Should I switch back? Any suggestions???

 

[dhookom]

I'm not sure where you got your advice other than from Aaron Kemp who is a troll in the public news groups.

I prefer mdb or the new accdb formats.

Duane
Hook'D on Access
MS Access MVP

 

[CosmoKramer]

puppy39,

Give this method a shot... faq703-3587

Si hoc legere scis, nimis eruditionis habes