realtionships - 3 different tables to be related on seperate table

[Mary]

Here is my problem. I have three tables that need to be related.
Table 1 - Project Type
idx_Ptype = Primary Key (autonumber)
Ptype (text)
Active (yes/no)

Table 2 - Activities
idx_ActType = Primary Key (autonumber)
ActType (text)
TestGroups (number) cross referenced with table Testgroups
Active (yes/no)

Table 3 - Projects
ID = Primary Key (autonumber)
ProjDesc (text)
Active (yes/no)
Complete (yes/no)

Each Project will have only one Project Type but will have many activities
associated with it. Each Project Type will have many projects and many
activities associated. Each Activitiy will have many projects and project
types associated with it. How do I get these to relate.

Then how do I get it to work on a form where if I select a project from a
combo box how can I get my second combo box of activities to show only the
activities that go with that project with out having to select the project
type as well.

 

[Jerry Whittle]

You need a 4th table to bridge between the Project and Activities tables.
Project and Activities seem to have a many-to-many relationship and it need
to be broken into two 1-to-M relationships at this 4th table.

Let's call this table ProjectActivities. It would have an autonumber primary
key; a ProID field linked to the primary key field of the Project table; an
idx_ActType field linked to the PK of the Activities field; and some other
supporting fields like PA_Date and PA_Notes. I'd also make the combination
of the Pro and idx_ActType a unique index to prevent duplicates.

The form would be based on the ProjectActivities and I usually have two
combo boxes linked to the other two tables so that I could pick a record from
each.

 

[Mary]

I forgot to mention that I did have a 4th table that bridges the Project
Types with the Activities. Looks like this
idx_ptypeacttype = primary key (autonumber)
Ptype_x-ref (number) crossreferenced to the project type table
ActType_x-ref (number) crossreferenced to the activities table

I want to associate the Project Type with the Project names. Then in my
form I want the user to be able to select a Project Name and from that
selection I want the activities assosicated with the Project Type to appear
in the combo box.