Many-to-Many relationships

[AmyGotz]

This is an exteremly confusing situation so please bare with me. I have a table that contains a record for each customer. These customers can have relationships with other customers, therefore, producing a many-to-many relationship on records within this table. I have created another table to hold the relationships. Basically all this table holds is the primary key from the customer table listed twice (once for the first customer and a second time for the customer they have a relationship with). My problem is that when I try to query to pull the information in for the form I can't figure out a way to tell Access that I want the address, city, state, zip, etc. associated with the second customer. It always gives me the information for the first customer in my relationship table. My SQL is as follows:

SELECT [tblBusinessPartnerXref].[CustCodePartner1], [tblBusinessPartnerXref].[CustSuffixPartner1], [tblBusinessPartnerXref].[CustCodePartner2], [tblBusinessPartnerXref].[CustSuffixPartner2], [tblSites].[SiteName], [tblParents].[chrCustType], [tblAddressDetail].[vchrAddrLine1], [tblAddressDetail].[vchrCity], [tblAddressDetail].[chrState], [tblAddressDetail].[chrAddType]
FROM tblSites INNER JOIN tblBusinessPartnerXref ON (([tblSites].[AddrCustCode]=[tblBusinessPartnerXref].[CustCodePartner2]) AND ([tblSites].[AddrCustSuffix]=[tblBusinessPartnerXref].[CustSuffixPartner2])) OR (([tblSites].[AddrCustCode]=[tblBusinessPartnerXref].[CustCodePartner1]) AND ([tblSites].[AddrCustSuffix]=[tblBusinessPartnerXref].[CustSuffixPartner1]))
WHERE (([tblBusinessPartnerXref].[CustCodePartner1]=[Forms]![frmXReference]![AddrCustCode] AND [tblBusinessPartnerXref].[CustSuffixPartner1]=[Forms]![frmXReference]![AddrCustSuffix]) OR ([tblBusinessPartnerXref].[CustCodePartner2]=[Forms]![frmXReference]![AddrCustCode] AND [tblBusinessPartnerXref].[CustSuffixPartner2]=[Forms]![frmXReference]![AddrCustSuffix])) AND ([tblAddressDetail].[chrAddType]='S');

Any help is greatly appreciated

 

[Pat Hartman]

Your query doesn't make any sense. You have references to tables that are not included in the From clause.

To get addresses for both (or either) customer, you need to add the address table twice to the query grid. Draw join lines from the first customer to the first instance of the address table and draw a join line from the second customer to the second instance of the address table. Make sure to rename the fields from the address table or you'll have trouble referencing them later. For example, in the Cust1.Address1 field, preceed the field name with Cust1Addr1: preceed the cooresponding field for the second customer with Cust2Addr1: That way you'll be able to easily determine which address fields are for which customer.

 

[AmyGotz]

Pat,
Thank you for the reply. I thought I understood what you were saying however, when I try to make it work I still get the same results as I did with my previous query. The new query looks like:

SELECT tblBusinessPartnerXref.CustCodePartner1, tblBusinessPartnerXref.CustSuffixPartner1, tblBusinessPartnerXref.CustCodePartner2, tblBusinessPartnerXref.CustSuffixPartner2, tblSites_1.SiteName AS Cust2SiteName, tblAddressDetail.chrAddType AS Cust2AddType, tblAddressDetail.vchrAddrLine1 AS Cust2Addr, tblAddressDetail.vchrCity AS Cust2City, tblAddressDetail.chrState AS Cust2State, tblAddressDetail.chrZipCode AS Cust2Zip, tblParents.chrCustType AS Cust2Type
FROM tblSites AS tblSites_1 INNER JOIN (((tblParents INNER JOIN tblSites ON tblParents.ParentCustCode = tblSites.AddrCustCode) INNER JOIN tblAddressDetail ON (tblSites.AddrCustCode = tblAddressDetail.AddrCustCode) AND (tblSites.AddrCustSuffix = tblAddressDetail.AddrCustSuffix)) INNER JOIN tblBusinessPartnerXref ON tblSites.AddrCustCode = tblBusinessPartnerXref.CustCodePartner1) ON (tblSites_1.AddrCustSuffix = tblAddressDetail.AddrCustSuffix) AND (tblSites_1.AddrCustCode = tblAddressDetail.AddrCustCode)
WHERE (((tblBusinessPartnerXref.CustCodePartner1)=[Forms]![frmXReference]![AddrCustCode]) AND ((tblBusinessPartnerXref.CustSuffixPartner1)=[Forms]![frmXReference]![AddrCustSuffix]));

Thanks again!

 

[Pat Hartman]

If you want to use a query to get either address, you need to add the columns for BOTH addresses. You can't use an optional join to join to the address table on one customer or another. You need to get the address fields for BOTH customers and then user which ever one you want.