Results 1 to 6 of 6
  1. #1
    Lanwe is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    3

    MS Access 2010 query help

    I have a Microsoft Access query question.


    There are three tables:
    table rooms contain fields: room_number | room_ref
    table occupant contain fields: room_ref | personid
    table person contain fields: personid | firstname | lastname

    The issue is there could be 0,1,2 or multiple persons in a room, which means there could have multiple personid for the same room_ref in the occupant table, and I want to get a list of room_number with all the persons' name. Is there a query I can use to do that?

    The result content needs to be:
    room_number | person(s)
    101 firstname1 lastname1
    102 firstname2 lastname2, firstname3 lastname3,firstname4 lastname4
    103 firstname5 lastname5

    Thanks!
    Lanwe

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum.....

    Yes, it can be done.

    The query would normally return the data like

    101 firstname1 lastname1
    102 firstname2 lastname2
    102 firstname3 lastname3
    102 firstname4 lastname4

    103 firstname5 lastname5

    To get the names for a room on one line,

    102 firstname2 lastname2, firstname3 lastname3,firstname4 lastname4,

    you will need to concatenate the names. Allen Browne wrote a concatenate function to do this.
    See http://www.allenbrowne.com/func-concat.html

  3. #3
    Lanwe is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    3
    Steve,
    Thanks for your reply.
    I did use Allen Browne's ConcateRelated() function, my query is:
    SELECT ROOMS.ROOM_NUM, ConcatRelated("PERSONID", "FIS_ROOM_OCCUPANT", "ROOMID = " & [ROOMREF]) AS PERSONS
    FROM ROOMS
    WHERE ROOMS.BLDG_NUM='----'
    GROUP BY ROOMS.ROOM_NUM, ROOMS.ROOMREF;

    but only get to the result:

    room_number | personid
    101 pid1
    102 pid2, pid3, pid4
    103 pid5

    I still need to replace the pid1,2,3,4,5 with concate the fisrtname and lastname from person table, which I stuck here. Since there are two relations with these three table - room_ref used in rooms and cooupant, personid used in occupant and person, but there is no direct relation exists between rooms and person. I don't know how to use the ConcateRelated() function for the situation that I have.

    Lan

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I really struggled with this... the field names changed from what you originally posted, fields and tables were added/changed....?

    The table structures in the attached example dB is wrong - I know it.
    But I had to try and create tables/fields/field types that would work....

    I created a query "qryOccuptPerson" (a "virtual table") that combined the room ref from table "Occupant" and concatenated the first and last names from table "Persons"..

    "Query1" has the "ConcateRelated()" function. In the concatenate function, I used "FullName" and the field name and "qryOccuptPerson" as the table name.
    Form1 has the record source "Query1".


    Hopefully you can follow and modify this enough to get what you want.
    Attached Files Attached Files

  5. #5
    Lanwe is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    3
    Hi Steve,

    Thank you so much for providing such a big help! Your sample works perfectly with my customized query and it's a good start for me to learn more Access.
    Lan

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 01-07-2016, 12:49 PM
  2. Replies: 1
    Last Post: 12-14-2015, 04:26 PM
  3. Use Varibales in a query with VBA in Access 2010
    By Matze0710 in forum Programming
    Replies: 3
    Last Post: 07-13-2015, 12:09 PM
  4. Replies: 6
    Last Post: 07-07-2014, 09:13 AM
  5. Replies: 0
    Last Post: 07-31-2012, 12:25 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums