Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Mar 2007
    Location
    Dexter, USA
    Posts
    90
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenate a Field (2000)

    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

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate a Field (2000)

    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.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    Star Lounger
    Join Date
    Mar 2007
    Location
    Dexter, USA
    Posts
    90
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate a Field (2000)

    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.

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate a Field (2000)

    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.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  5. #5
    Star Lounger
    Join Date
    Mar 2007
    Location
    Dexter, USA
    Posts
    90
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate a Field (2000)

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

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate a Field (2000)

    How do you want to separate the names? Would a comma suffice?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Concatenate a Field (2000)

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

    You will an example in this <post#=301,393>post 301,393</post#>
    Regards
    John



  8. #8
    Star Lounger
    Join Date
    Mar 2007
    Location
    Dexter, USA
    Posts
    90
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate a Field (2000)

    A comma would be great.

  9. #9
    Star Lounger
    Join Date
    Mar 2007
    Location
    Dexter, USA
    Posts
    90
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate a Field (2000)

    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.

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Concatenate a Field (2000)

    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.
    Attached Files Attached Files
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •