Many to Many Relationships!! 1

[Richardl78]

I am in desperate need of a reality check on a database I am trying to set up.

My company bids for many projects.
On each bid we have many partners.
But each partner can be on many other bids.

( there is much more information that I want to store, such as varying number of contacts for each partner and also for each project from a shared contact database, but I will keep things simple to start off with)

So I know that I have a many to many relationship here.

so I have:

TblPartners
With all the specific partner information
Autonumber Primary Key


TblProjects
With all the Specific Project Information
Autonumber Primary KEy

Link table
Tbl Link ProjectsPartners
With ProjectID and PartnerID shared Primary Key

I have linked the tables up in the relationships view

But how exactly do I set things up so I can enter the data?
How do I tell the database that Project X has partners A, B and F. I understand how Access automatically detects relationships in one to many relationships. But how can it work with Many to Many. I feel I am missing some extra thing I need to do.

Do I need to do a Query of some kind and then add a form?
Are subforms involved in some way?

I am pulling my hair out over this one and would be extremely grateful for any help?


Richard

 

[KenReay]

Hi

Looks like you are spot on track with your tables.

I would expect to create the data entry froms using a main form for the Project, with a sub form for the Partners

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Richardl78]

thanks for the reply.

How exactly would I do the forms? I am still don't see how the many to many relationship works.

How does the link table get populated?


Sorry for all the questions.
Richard

 

[Richardl78]

Basically how do I make a form that lets me assign a partner to a project? Given that there will be a variable number of partners per project.

I can input the partners names in a seperate form.

This shouldn't be as complicated as all this should it??

 

[GoDawgs]

Usually when I have that situation I go with subforms...that way you can have the parent form be the project that you want, then in the subform you can select from combo boxes the names of each person involved (I usually set the form to be datasheet view with a combo box on it). This solves your problem because the subform would actually have the linked table as the recordsource...which would be populated with the parent form's project id and the combo box's partner id. Hope that helps.

Kevin

 

[Richardl78]

I have tried to do that but it seems to set things up as a one to many relationship and not a many to many. As when I enter a partner name it enters a new one everytime.

So i dont so much need to input the partners ( i can do this elsewhere, rather I jsut need to link them to that particular project.


Any tips?

thanks alot for replying firstly though

 

[GoDawgs]

You have something set up wrong then. The parent form needs to have Projects as the recordsource, the sub form needs to have the linked table (not the Partners table) as the recordsource. The combo box in the sub form would look up the names in the partners table and then store the partner ID in the linked table.

 

[Richardl78]

What you are saying makes alot of sense

How do I make the combo table look up the partner name though

If i just called partner name a combo list then there are no names there to select from

any ideas??

 

[lespaul]

When you place the combo box on the form, the wizard should open asking if you want the information to come from a table/query or if you want to type in a list. Select the table/query option. Then select the table (or query) you want to use to populate the box (I would recommend the PK and the PartnerName fields from the table). The wizard will assume that you want to hide the PK field and only display the name.

Then you can use the value from the combo box to run an insert query to the table that stores the partners and projects:

INSERT INTO TABLENAME (PROJECTID, PARTNERID) VALUES (Form!FormName!ProjectIDField, Form!FormName!ComboBoxName)

(syntax may be a little off, but you should get the idea)

HTH

Leslie

 

[KenReay]

Hi

No need for insert queries, you just base teh subform on a query which joins the Linking table and the partners table, that way the partner name is available in the recordsource

A maintenance form of this type can be set up without any code being necessary to insert records

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Richardl78]

i can do that but then I am entering a new partner name in each time.

For each project I want to be able to select a multiple number of partners from drop downlists.

The only way I have found of doing this is by making the partners ID and Project ID fields in the link table a combo box of tblprojects and tbl partners with the ID number so I can select a partner for a project. I do this is table design view.

But then I can only see the partnerID in my subform and I can't see the partners name, as my subform is based upon my link table.

Anymore anyone? I really want to get a handle on this. I have appreciated all the help that people have given me. I jsut need to go one step further.

RIchard

 

[KenReay]

Hi Richard

Post an EMAil address and I will send you an example, it is much easier to do than to explain (honestly!)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Richardl78]

my email address is RichardLashford2002@yahoo.com

Thanks alot I really appreciate it.

Richard

 

[tahilg]

Your Link table consists of two foreign keys. However you design your forms for data entry you must ensure that records with the corresponding primary keys exist in both of the other tables before you add record(s) to the Link table.

So in your example you must ensure that a record for Project X exists in TblProjects and that records for Partner A, Partner B and Partner F exist in TblPartners before you try to add records (X,A), (X,B) and (X,F) to the Link table.

From a drop-down list you can only select partners that already exist in TblPartners. For a new partner you must provide the means and code to add the new partner's record to TblPartners before adding records for it in the Link table.

HTH

Gunny

 

[docmeizie]

You Should have three tables connected like so:

Bids Bids_Partners Partners
----- -------------- ---------
BidID(pk)---->Bid(fk)
Partners(fk)<----------Partners(pk)

Bids_Partners table should be tied to a subform. The subform should be tied to a main form for either bids or partners.

If I take a peek in your Windows, to fix a problem, does that make me a &quot;Peeping Tom&quot;? Hmmmmmmmmmmv

 

[tahilg]

A small correction to docmeizie:

Bids Bids_Partners Partners
----- -------------- ---------
BidID(pk)---->BidID(fk)
PartnerID(fk)<----------PartnerID(pk)

If you make Bids_Partners a subform to either Bids or Partners then you will be able to enter a record in Bids_Partners for which there is no valid link on the other side.

If, say, you make it a subform to Bids you can enter a record into into Bids and in the subform enter a record into Bids_Partners with a PartnerID that does not exist (yet) in Partners.

Gunny

 

[zambrtp]

ALL:

I am working on a many-to-many relationships with multiple subforms..can I have that example DB as well...Access 2000 has me puzzled ....

Tereza.P.Zambrano@saic.com

 

[docmeizie]

Ahh that may be true Tahilg, but you may also set the combo boxes for the subform to allow additions to the Partners table which would create the key.

If I take a peek in your Windows, to fix a problem, does that make me a &quot;Peeping Tom&quot;? Hmmmmmmmmmmv

 

[willir]

zambrtp

Read Paul Litwin's article on Fundementals of Relational Database Design.

http://www.abcdataworks.com/Developers/Index.html

If you want more specific info, consider posting a separate thread.

Richard

 

[tahilg]

I agree docmeizie. I was just trying to call attention to the possibility and warn that data integrity issues should not be overlooked. I was not criticizing.

Off topic - about your signature - whether or not peeking in other peoples' Windows makes you a &quot;Peeping Tom&quot; you might get shot at. Might that not make you into a &quot;Peeking Duck&quot;? :>))

Gunny