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

    Unanswered: Concatenate Multiple Rows from sub and sub-sub records

    My main table has BuildingID
    Each BuildingID can have multiple Floors (sub table), each floor can have multiple Cubicles(sub-sub table), and each cubicle can have multiple desks (sub-sub-sub table).

    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?

    Is there a sample DB with such function?

    Thank you

    tblBuilding
    BuildingID
    BuildingName

    tblFloors
    FloorID
    BuildingID
    FloorName


    tblCubicle
    CubicleID
    FloorID
    CubicleName

    tblDesks
    DeskID
    CubicleID
    DeskName

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use a query such as:
    Code:
    SELECT tblBuilding.BuildingID & ' ' & 
           tblBuilding.BuildingName & ' ' & 
           tblFloors.FloorID & ' ' & 
           tblFloors.FloorName & ' ' & 
           tblCubicle.CubicleID & ' ' & 
           tblCubicle.CubicleName & ' ' & 
           tblDesks.DeskID & ' ' & 
           tblDesks.DeskName
    FROM ((tblBuilding 
           INNER JOIN tblFloors ON tblBuilding.BuildingID = tblFloors.BuildingID) 
          INNER JOIN tblCubicle ON tblFloors.FloorID = tblCubicle.FloorID) 
         INNER JOIN tblDesks ON tblCubicle.CubicleID = tblDesks.CubicleID;
    Have a nice day!

Posting Permissions

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