many-to-many-to-many relationship (1 Viewer)

[lszuma]

Any advice? I've tried some things and no success with linkage

Rooms Table
RoomID
Room No
Type
Desc

Project Table
Project ID
Project
Desc
Faculty Assignment
Project Grant No
Sponsor
Project End Date

Student Table
Student ID
Students

Many rooms with many projects, many of those projects in many of those room, with many students working on those projects. Many rooms, many projects, many students. Argh...
I've already tried two junctions between rooms and projects // and projects and rooms

What is making this so difficult?

 

[WillEllis]

I built a Project/Resource database, joining projects/project data with Resource hours per month/employee data and 'Run the Engine' tasks (separate table from Projects). I had to do 2 Many-to-many join tables. Attached is my relationship page, if it helps any. I didn't know Access before March 05, so I had to learn this on my own as I built it...I also had an 8 year Access veteran tell me he didn't have a clue as to how I would begin to build something like this, since it required so many un-natural joins, but it is up and running as we speak! I used VBA code like duct tape to get it to do what I wanted.

 

[Pat Hartman]

Didn't we have this discussion a while back? You never defined the relationship between students and rooms. If it doesn't matter which room a student uses to work on a project, you have two separate m-m relationships. Students-Projects and Rooms-Projects. If you are trying to make a report that combines this data, you MUST use subreports. The main report would show the project data and you would have two separate subreports - one for students and one for rooms.

If a student is assigned to a project in only ONE room, the relationship is different.

 

[jfly]

many to many to many

As you said, it depends what you want to do.
The most flexible is to do three tables:
A Room-Project Student-Project and Student-Room.
Like this you can join everything with everything.
The only problem is that you have to check the integrity. It means before writing the data into the database, you go to the other tables to check that the project the student is assigned has the same Room.
Now we can also see the thing this way, the Project has'nt an assigned room. Just the student. So you will not have the Room-Porject table. And this way you don't have to check all the time. Just to be coherent in your assignations.
Depends on your needs.
Hope it helps.