Query based on multiple Many-To-Many Relationships

[cburgess]

Hi, I am currently building a system which uses many-to-many relationships. The basic layout is:

tblCompany and tblAddress with the Company having a "Registered Address" and a "Correspondence Address".. therefore I have two m2m relationships in order to save having multiple Address tables.

Everything is working fine except now I am trying to create a query which includes both the addresses for a search form.

I have a query at the moment qryCompanySearch. This works fine with one lot of the addresses. Eg:

tblCompany - tblCompanyRegisteredAddress (m2m) - tblAddress

My form works with my filtered search box and will find a company based on the post code (for example) of the registered address..

BUT when I add the second m2m table into the query, it just shows blank with no data in it. I'm aware that it is something to do with duplicates and this might not be the best way of building the system, but it is only to use on this search form as I basically want a "Magic Search" where you can type any data related to the company and it will find the company..

Hope this makes sense? Any help would be greatly appreciated as I'm struggling to find the answer to this online..

 

[Ranman256]

You only need 1 address table.

 

[plog]

To further Ranman's advice--tblCompanyRegisteredAddress is unnecessary. You should have a 1-many relationship between tblCompany and tblAddress, not a many-many relationship. tblAddress should look something like this:

tblAddress
addr_ID, autonumber primary key
ID_Company, number, foreign key to tblCompany
addr_Type, text, tells if Registered or Correspondence address
addr_Street, text, street address portion of address
addr_City, text, city portion
....
etc.

Then in a query you join just those 2 tables via the ID_Company