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

 

[zambrtp]

Appreciate the link...I still need an example of how someone was able to implement many-to-many using Access 2000 forms. I dont have a problem with the database design its the form that is troubling.
Thanks

 

[docmeizie]

Fa Shig a Dil. Its cool. LMAO. Yea, I probably would get shot at.
Okay Zambrtp, here is what you are looking for.

With a many to many relationship all you have to do is turn the table that connects the prime tables(Bids_Partners in my example above) into a form. Now I usually make sure the that controls are combo boxes for this. That way if you already have data you can just point and click. Now once you turn Bids_Partners in a form, set that form to the side for a minute. Next would be completely up to how you want to view the data. With the example above the two prime tables were Bids by itself and Partners by itself. In my cases I use one of the prime tables and create a form. Either, or, or both is completely up to you.

Now with either the Bids or the Partners form make room at the bottom of the form and insert a subform/subreport from the toolbox. Your subform is going to be the first form you made or Bids_Partners. Make sure the linking fields are correct, and bada bing bada boom, you got yerself a many to many relationship in form/subform style.


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

 

[zambrtp]

Doc:

Ok that is to view the data...what about input forms...
I have a DB that is to request parts, do the procurement side and keep inventory based on project, delivery, interm, build and CM control names
The part will be entered for ordering based on what project, delivery, interim, build etc...
basically one part could look like this...
General info: name , date, date needed order, charge no, other info that wont change for this whole order
Then comes the CM name, project, build, interm, delivery, all the information about the part, and its SUB parts and its information, then what facilities it for.
I created forms with subforms...but my linking table doesnt seem to populate therefore my relationships are not held.
And is there a better way to do GUID? Im using Autonumber..

 

[docmeizie]

Okay, it would be helpful if I could have your table layout to see how you have them linking together. If you could post it then I could help with your set up. Now one rule of thumb is that when one table has even one field that may belong to another table then you could look there for a linking between the two tables.

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

 

[willir]

zambrtp

Like I said earlier, please consider posting your issue as a new thread and we will respond. From my perspective, it becomes awkward when working on two or more different issues - similar problem perhaps, but different data and different situations.

Richard

 

[zambrtp]

Richard:

Sorry I missed the part about starting a new thread...Ok Ill do that..thanks