#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Location
    America (But which one?)
    Posts
    43
    Rep Power
    1

    Merge Multiple Rows from One Column (MS Access)


    Hello all!

    It's been a while since I've been here, and I find myself in desperate need of help again. Hopefully I am posting this in the right spot!

    This situation deals with two tables, a query, and then the second query I am trying to build (all in Access 2010).

    The first table (Table1) Consists of Two Columns with 24 rows.

    Line Number | Description (each is close to 255 character)
    1 | 1 Desc
    2 | 2 Desc
    3 | 3 Desc
    Etc...

    The second table (Table2) consists of two columns, with the second column being a multi-selection List from Table1.Line Number. There is currently only one record/row.

    Last Name | Line-Number
    Zeigler | 1, 3, 5 (multiples selected from the list of 24)

    Next is Query1 which has a relationship between Table1.Line Number and Table2.Line Number. The two columns are as follows:

    Last Name | Description
    Zeigler | 1 Desc
    Zeigler | 3 Desc
    Zeigler | 5 Desc

    For the selected line numbers on Table 2, there may be as little as 1, or as many as all 24. I am trying to create a query that combines all of the rows that Query1.Description produces, separated by commas, so that I would have a query that produces:

    Description
    1 Desc, 3 Desc, 5 Desc

    I have searched and I've searched and I can't find a thing on combing all the rows of one column into one row in a query!

    Any Help would be greatly appreciated!

    -DZ
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,031
    Rep Power
    4210
    can't be done in access with sql, might be possible with a macro and extensive coding

    multi-selection lists violate first normal form so the best long-term soution is to redesign your table
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Location
    America (But which one?)
    Posts
    43
    Rep Power
    1
    Originally Posted by r937
    can't be done in access with sql, might be possible with a macro and extensive coding

    multi-selection lists violate first normal form so the best long-term soution is to redesign your table
    Thank you for your reply. I feared that was the case, but thankfully redesigning this one aspect will only take 10 - 15 minutes.

    Thank you again!

    -DZ

IMN logo majestic logo threadwatch logo seochat tools logo