Three table query

[dave]

Hi

I have three tables: GroupA, GroupB and Rooms. GroupA and GroupB
contain people. Rooms contains a list of rooms.

I want to produce a query that will show me a list of rooms with all the
people from GroupA and groupB in it. Something like

Room People

First Room GroupA Fred (from GroupA)
Joe (from GroupA)
GroupB Stan (from GroupB)

Second Room GroupA Bill (from GroupA)
GroupB Jim (From GroupB)


Any ideas?

Thanks

 

[Rick B]

Do you have a like field between the tables? Are does your "Rooms" table
have a name field? Does your GroupA table have a "rooms" field?

How does the database know who is in which room?

Why so you have two Group tables? Why not just a "Person" table with a name
field, and a group field? You really want to create new tables as you add
groups?

Sounds like you have not followed proper database structure.


Rick B

 

[dave]

Okay groupA are Staff and GroupB are Students therefore I don't really
want to put them in one table.

Each person in either groupA or GroupB has a field titled RoomID. In the
Rooms table I have the primary key as a field RoomID along with another
field with the room name itself.

I need a report to show me who is in each room both staff and students

 

[Duane Hookom]

It would be easiest if you put students and staff in the same table. It's
not like they will mingle and get in trouble. An alternative is to use a
union query that pseudo appends students to staff or staff to students.