Multiple Joins in Multiple Table Search query
Good day all..
I am trying to create a simple Search form in Access where a user can select a desired record and query multiple tables using the inputs.
I would like them to be able to query Retailers, Distributors and Products.
The 6 tables are linked as follows:
Although some of these tables are not included in the query, they are required to ensure relationships.
Retailers -- Uses (RetailerID,DistributorID) -- Distributors
Retailers -- Orders (RetailerID,ProductID) -- Products
All retailers have at least one distributor BUT a retailer may or may not have ordered any products.
I have created my form but the query linked to the form is having some trouble. It is only selecting those records that have ordered products. For example, if I query a retailer name only and it does not have any ordered products, it will not display. Is there a problem with the table joins? The SQL for the query is displayed here:
SELECT DISTINCT Retailers.RetailerID, Retailers.RetailerName, Retailers.Address, Retailers.City, Retailers.Province, Retailers.PostalCode, Retailers.AreaCode, Retailers.PhoneNumber, Retailers.FaxNumber, Retailers.ContactName, Retailers.EmailFROM (Retailers INNER JOIN (Products INNER JOIN Orders ON Products.ProductID=Orders.ProductID) ON Retailers.RetailerID=Orders.RetailerID) INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID=Uses.DistributorID) ON Retailers.RetailerID=Uses.RetailerIDWHERE (((Retailers.RetailerName) Like forms!frmMultiFieldSearch!txtRetailerTwo) And ((Distributor.DistributorName) Like forms!frmMultiFieldSearch!txtDistTwo) And ((Products.ProductName) Like forms!frmMultiFieldSearch!txtProductTwo));
Is it possible for me to do this the way I have set it up?
I have tried using a LEFT JOIN between Retailers and Orders but I get an ambiguous join error. I have tried other joins as well but either I get that error or it does the same thing by only displaying those records that have ordered products.
Would creating muliple queries and using a union help?
Please let me know if you need more info!
Thanks in advance!