New to Access many-to-many database confusion

[deb123456789]

Hi All,

I'm new to access, totally new! I'd like to make a database of all the rooms in my building. However we have users who use multiple rooms. What I would like is a system where I can search for, say room42 and get a list of all the people who are allocated space in that room, and again search for the user 'zaphod beeblebrox' and see all the rooms zaphod has been allocated space in.

I was thinking of one table with my room users: FullName, LineManager, EndDate, CommentOnUser. and one table with the room details e.g. RoomNumber, RoomType, CommentOnRoom?

I just don't know what I'm doing - can anyone help

Thank you.

 

[theDBguy]

Hi. Welcome to AWF!

Sounds like you're describing a "many to many" relationship. You do need a separate table for your rooms and users, but you also need a junction table for the room to user assignments.

 

[Pat Hartman]

Here is an example that shows how a m-m works. It shows how to work with the data from either direction and uses two different methods. Both are valid but usually one will make more sense than the other either for both directions or different directions.

The important part to remember is that the child form whether it is a subform or a popup, is bound to the junction table.

 

[MajP]

tblRooms
--RoomID  "Primary key
-- other fields unique to room

tblUsers
--UserID  " primary key
--other fields unique to a user

Create what is called a Junction Table

tblRooms_User_Assignments
--RoomID_FK   ' a foreingn key to tblRooms
--UserID_FK    ' a foreign key to tblUsers

If you have users 1,2,3,4, and Roms A, B, C, D

to assign users 1, 3 to room A then in the above table

RoomID_FK    UserID_FK
A                     1
A                     3

To add User 4 to rooms A, C, D

A                     4
C                     4
D                     4

Now in a query you join both tables to your junction table bringing in all the data from both tables.

 

[Uncle Gizmo]

I demonstrate a Many to Many relationship from both sides on my blog here, along with some YouTube videos which you might find useful:-

Many to Many Relationship - Nifty Access

Many to Many Relationship - 3 Video's In their simplest form Many to Many relationships are just two columns in a table. The first column representing one side of the Many to Many Relationship and the other column representing the other side of the many to many Relationship. In this particular...

www.niftyaccess.com