Many to One relationship (I think...)

[jdrighter]

Trying to set up a fairly simple DB. Here is the layout:

tblRoom(roomID, roomNumber, rackOne, rackTwo, rackThree)
tblStudent(studentID, name)

Each student will be assigned to one, and only one, rack. There will be 3 students to one room. One student can only have one room but a room can have many students... I store studentID in the rackOne, Two and Three fields. My problem is when I try and querry for the information so as to display actual names rather than ID's. I'm not sure If my querry is the problem or if I've set the tables up incorrectly. Any insight is much appreciated.

 

[ScottGem]

Trying to set up a fairly simple DB. Here is the layout:

tblRoom(roomID, roomNumber, rackOne, rackTwo, rackThree)
tblStudent(studentID, name)

Each student will be assigned to one, and only one, rack. There will be 3 students to one room. One student can only have one room but a room can have many students... I store studentID in the rackOne, Two and Three fields. My problem is when I try and querry for the information so as to display actual names rather than ID's. I'm not sure If my querry is the problem or if I've set the tables up incorrectly. Any insight is much appreciated.

First your database is not properly normalized. Whenever you have feidls like Rack1, Rack2, etc. you have a repeating group. You should have another table for the Racks. So tblRoom would be:

RoomID (PK Autonumber)
RoomNumber

And tblRacks would be:
RackID (PK Autonumber)
RoomID (FK)
StudentID (FK)
RackNumber

Also you should split names into at least first and last.

To get the query results you want, join the RoomIDs and the StudentIDs together. You can then pull the name field and room number fields.

 

[jdrighter]

Thanks for the reply ScottGem. I think I got it worked out.