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