Relationship Design??

[fenhow]

Hi, I have two tables. Atbl is project, Btbl are people.
I want to have a sub-form on Atbl where I can select one or more people for project. Each project can have many people and people can be assigned to many projects.
I for the life of me cannot figure out how to make this happen. What I would like to do is have a master table Btbl where all the people and their details are added, when I start a new project I want to be able to select from the people in the Btbl as needed.
Can someone tell me or send me an example on how to do this?
Thanks so much..
Fen How

 

[jzwp22]

You are describing a many-to-many relationship which requires 3 tables. The third table you need is a junction table.

tblProjects
-pkProjectID primary key, autonumber
-txtProjectName
other fields pertinent to the project

tblPeople
-pkPeopleID primary key,autonumber
-txtFirstName
-txtLastName
other fields pertinent to the people

tblProjectPeople (junction table)
-pkProjPeopleID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkPeopleID foreign key to tblPeople

To represent this in forms, your main form would be based on tblProjects. On that form you would have a subform based on the junction table. Within that subform, you would use a combo box based on tblPeople. I would recommend using the combo box wizard to step you through the process of creating the combo box.

 

[plog]

You need a junction table (http://en.wikipedia.org/wiki/Junction_table). This type of table allows you to create many-many relationships.

Let's assume (and pray) you have an auto-number primary key for projects (Atbl.Project_ID) and you have an auto-number primary key for people (Btbl.Person_ID). You would create a 3rd table (Ctbl) with at least 2 fields. Those 2 fields would hold the id values from the other two tables. It would look like this:

Ctbl
ID_Person, ID_Project
17, 9
17, 13
22, 1
19, 9

 

[fenhow]

Most grateful, many thanks! DB set up as indicated. Do I need to create any relationships between the tables before I add my subform?
Fen

 

[fenhow]

Pending any relationships created with the tables, when I go to add the subform based on the JunctionTable what do I add to my main form? Do I add the JunctionTable or the PeopleTable? Please forgive me it has been a while since I have done something like this.

 

[plog]

when I go to add the subform based on the JunctionTable what do I add to my main form? Do I add the JunctionTable or the PeopleTable?

That's a really confusing question. Are you asking what the main form is based on or what the sub-form is based on? In either case, this is how it should be:

Your subform is based on the Junction Table. The main form can be based on either the People table or the Project table--it really is up to you how you want to assign Projects to People.

My hunch is that the main form would be based on Projects because each project would have additional data to be added to it (Project Name, Starte Date, Budget, etc) that you could fill in on the main form. The People table will be more or less static--its not a table that will require a lot of adding and editing. At least not as much as the project table.

 

[fenhow]

Hi sorry, I am clear on the Mainform etc.. I am adding the subform (People) to my main form no problem however I want all of the data to show in the subform. Currently it is only showing the People ID and I can select them as needed but it does not show all of the other data such as name etc..
Thanks again.

 

[plog]

I would either use a Dlookup to show the other values (if it is more than just one field). If you want to be able to select the Name in the combo box instead of the ID, have a look at this:

http://stackoverflow.com/questions/...lay-text-values-from-table-query-and-save-the

 

[fenhow]

Thanks, do I need to create any relationships between the tables, A, B, C before I start to add the subform?

 

[jzwp22]

It would be best to set up your relationships first, then Access will automatically link the subform to the main form.

In the structure I presented earlier, the primary key (pk...) will join to the similarly named foreign key (fk...) field

i.e. pkPeopleID-->fkPeopleID

Just to clarify, your subform must be based on the junction table.