[RESOLVED] Access Many-to-Many Relationships

[crater]

Hey everyone, I have been looking now for around 3 hrs how to get this to work, can any one help or give me a link(s) to learn from.
I have 3 Tables built in Access: TblContractors,TblOwners and TblContract_Owner_Pointer the relationships is set like this:
TblOwners:
OwnerID PK
Name
Address ect.

TblContractors:
ContractorID PK
Name
Address
ect

TblContractor_Owner_Pointer
OwnerID FK to TblOwners
ContractorID FK to TblContractor

A simple Many-to-Many relationship right?
1 Owner can have Many Contractors and 1 Contractor can have Many Owners

So I place the Owners table onto the form as detail BUT The related table TblContractor is not under the TblOwners Table as a related table. TblContractor_Owner_Pointer is as it should be. But it is not what I want. I want to be able to move through my Owners, changing Child Controls (also Detail view) by the OwnerID and ContractorID.

So Owner 1 can have Contractor 1,4,7 or Contractor 1 can have Owner 1,2,5 ect. Get the picture? 1 owner may have 2 different contractors on the job and 1 Contractor may work for 3 different Owners.

I have read threads that get close but I get very confused easily (sorry). One thread even said that Many-To-Many Relations were very rare. In this app I think they will dominate.
Thanks for any advise

[RhinoBull]

Instead of TblContractor_Owner_Pointer table I would have say Contracts table that has contract details including (but not limited to) OwnerID and ContractorID fileds and set either to be FK pointing to their respective PK.
ContractID could be the PK on this table and will make each record unique.
If you set that up it will be quite simple to filter data by owner and/or contractor.

[crater]

Thanks for the reply Rhino,
Please, please, please don't take offense, but isn't your suggestion pretty much the same as what I'm already doing? I agree that a simplier Table name would help and I guess I forgot that I had Contractor_ClientID as the PK on that table already. So other than changing the names of the table and the PK field, I still can't see a way of filtering through this table, the elusive Many-to-Many relation. Please take a look at my relationship attachement. Is it what your talking about and correct.

Simular situation is (I assume for normalization) is seperating a States table from the owners, and contractors table. And assign a ContractorID and a OwnerID to the States table to eliminate duplication.

[crater]

Ok working with the above tables in my ds, I want to filter the contractors by owner. Looking at the Dataset what table could I bring onto the form to accomplish this. I don't see a "Contractor" related table in the Owner Table just the Related Contracts which does not include the contractors name address ect. If I include this info in the Contract Tbl then I would be duplicating information. Once in the Contractors table and again in the Contracts tbl.

[crater]

WOW I got it. However I used the Access Form Wizard to come up with the Sql Statement:

SELECT [TblContractors].[Contractor], [TblContractors].[ContractorAddress], [TblContractors].[City], [TblContractors].[State], [TblContractors].[Zip], [Contracts].[OwnerID] FROM TblContractors INNER JOIN Contracts ON [TblContractors].[ContractorID]=[Contracts].[ContractorID]

AND I think part of my problem was that I was working with the orginal DataSet so that is why the other Tables weren't related under my Owners Table

So for now I got it working. still am leary of how, but why question something that works I guess.

Thanks.