MS Access 2010 query help

[Lanwe]

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

 

[plog]

Check out this link:

http://allenbrowne.com/func-concat.html