Trouble creating several many-to-many relationships between tables

[matt4422]

Hello,

I am trying to design a database where every relationship is of the many-to-many variety and I am having touble defining the relationships.

The database that I am creating is a collection of past research done on a single topic. There are 4 tables that define my data:
1) Regions of Interest (provinces, states, etc)
2) Organizations
3) Studies
4) Researchers

The relationships are as follows: (All are many-to-many)

Regions of Interest <--> Studies
(A region can have many studies occuring in it and a single study can encompas many regions)

Studies <--> Researchers
(A study can have many researchers and a single researcher can work on many studies)

Studies <--> Organizations
(A study can be funded by many different Organizations and a single Organization can fund many studies)

Researchers <--> Organizations
(A researcher can be a part of many organizations and a single organization has many researchers working for it)

I have tried to set up intermediate tables for the many-to-many relationships in several ways but I am not getting the relationships to work when I build a form or data access page.

If anyone has experience with handling multiple many-to-many relationships in a database and has advice on how to correct my problem, it would be great to hear from you.

Thanks

(Sorry about the length)

 

[fneily]

Let's take Studies/Researchers. You'd then have a junction table that could look like this:
StudyResTable
StudyResTableID Primary Key
StudiesID Foreign Key to Studies Table
ResearcherID Foreign Key to Researchers Table
Any other COMMON FIELDS

So a query would have the StudiesTable, StudyResTable and ResearchersTable. Your form would be based on this query.
I wouldn't jump to a data access page (DAP) until you mastered relationships just yet.