Concatenate a Field (2000)

Topic

New Reply

I’m creating a report. I’ll use parentchild for my example. They want the parent or family name on the top of the report with all of the children listed on a line below it. I thought that I could create a crosstab query but I need the family members listed instead of numbers.

Jones
Theresa, Alex, Jack, John

Smith
Mark, Seth, Mary

Reply | Quote

Replies

What is your table structure? If you’ve got two fields, for instance [first_name], [last_name], in your report, just group by last name.

Reply | Quote

I think I know what you mean. I had tried creating a new report using the last name as the group by and then sorted the names. This put all of the (child) names in a vertical list though and I need to have them all fall in a horizonal line. Does that make sense?

For the table, the field names are:

GroupName
BillTo
BillToName

One GroupName may have 10-20 BillTo accounts underneath them.

Reply | Quote

I understand now; I don’t think you’re going to be able to do that, just because the crosstab query is going to want to report data horizontally until all values are shown; it won’t insert a break and return the row under (if that makes sense). If you have 10-20 names, you’re basically going to run out of space across a page.

Reply | Quote

So sad, too bad. Thank you, they aren’t going to be happy but at least I can stop spinning my wheels.

Reply | Quote

You should be able to do what you want using the Concatenate function.

You will an example in this post 301,393

Reply | Quote

Thank you for the reply. I reviewed the previous post and although I did not understand all of it, it doesn’t seem like we’re trying to do the same thing. The excel sample that he sent over noted that the results that he wanted to achieve would still have everything listed below and I need things to fall into a line.

Thank you for trying though.

Reply | Quote

The Concatenate function displays the data from a related table as a single string, horizontally.
I attach an example with a couple of queries that show it in use.

Reply | Quote

How do you want to separate the names? Would a comma suffice?

Reply | Quote

A comma would be great.

Reply | Quote