New to Access many-to-many database confusion (1 Viewer)

deb123456789

New member
Local time
Today, 01:58
Joined
Mar 24, 2022
Messages
1
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

I’m here to help
Staff member
Local time
Yesterday, 18:58
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Yesterday, 21:58
Joined
Feb 19, 2002
Messages
42,981
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.
 

Attachments

  • ManyToMany20210414.zip
    1.5 MB · Views: 228

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:58
Joined
May 21, 2018
Messages
8,463
Code:
tblRooms
--RoomID  "Primary key
-- other fields unique to room

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

Create what is called a Junction Table
Code:
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
Code:
RoomID_FK    UserID_FK
A                     1
A                     3

To add User 4 to rooms A, C, D
Code:
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

Nifty Access Guy
Staff member
Local time
Today, 01:58
Joined
Jul 9, 2003
Messages
16,245
I demonstrate a Many to Many relationship from both sides on my blog here, along with some YouTube videos which you might find useful:-

 

Users who are viewing this thread

Top Bottom