Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    115

    Unanswered: Concatenate Multiple Rows Into One Field on display only

    I have a table that each header record can have multiple detail records.

    I would like to display all details (for 1 header record) in one field, on a report or form. (not to save it into a table forever).

    What is the SQL for this?

    Here are the 2 tables
    FloorID manager
    1 Jack
    1 Leslie

    FloorID Cubicle Size
    1 A1 5ft
    1 A2 5ft
    1 B1 5ft
    1 B2 5ft
    2 A1 5ft
    2 A2 5ft
    2 B1 5ft
    2 B2 5ft


    I would like to display it as this
    Floor 1: A1, A2, B1, B2

    Thanks

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Typically done with a function like this one:

    Modules: Return a concatenated list of sub-record values
    Paul

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    if you make the FloorID a sub Header in the Reports and sort by the FloorID that will group all the FloorID together

    But when you look at in query view All the Floorid will be repeated.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Jan 2004
    Posts
    115
    The design has changed,

    My main record has BuildingID
    Each BuildingID can have multiple Floors, each floor can have multiple Cubicles, and each cubicle can have multiple desks.

    I would like to display all details (for 1 header record) in one field, on a report or form.

    What is the SQL for this?
    Thanks

Posting Permissions

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