Multiple Joins in Multiple Table Search query 2

[dmuroff]

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 tables are linked as follows:

Retailers--Uses--Distributors
Retailers--Orders--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.Email
FROM (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.RetailerID
WHERE (((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?
Please let me know if you need more info!
Thanks in advance!

 

[lespaul]

you need to change the join type.

In your query design, there is a relationship line between retailers table and products table, right click on that line and change the join type to:

select all the records from tblRetailers and only those records that match from tblProducts.

This will change it to a Left join. For more information on joins see, 'Understanding SQL Joins'

HTH

Leslie

 

[dmuroff]

Hi Leslie,

I read the article and took your advice. I tried this late last night and it did not work.

The thing is that I have 6 tables that are being used in the query. (Some just for the relationship)

Retailers--Uses(RetailerID,DistributorID)--Distributors
Retailers--Orders(RetailerID,ProductID)--Products

When I try what you just said I have done this:

Include ALL records from 'Products' and only those records from 'Orders' where the joined fields are equal
AND
Include ALL records from 'Orders' and only those records from 'Retailers' where the joined fields are equal.

This gives me the error: "The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a seperate query that performs the first join and then include that query in your SQL statement."

If I try:

Include ALL records from 'Retailers' and only those records from 'Orders' where the joined fields are equal
AND
Include ALL records from 'Orders' and only those records from 'Products' where the joined fields are equal.

It performs the same way that it did before by only querying those records that have ordered products.

Any thoughts?
Thanks!

 

[lespaul]

OK, I don't see six tables in your query!?

now, you don't want to change the relationship between products and orders, just between retailers and orders.





Leslie

 

[dmuroff]

I know there are not 6 tables in the query but for the database as a whole there are 6 tables that contribute to this query.

If I am understanding you correctly you want me to do this:

SELECT DISTINCT Retailers.RetailerID, Retailers.RetailerName, Retailers.Address, Retailers.City, Retailers.Province, Retailers.PostalCode, Retailers.AreaCode, Retailers.PhoneNumber, Retailers.FaxNumber, Retailers.ContactName, Retailers.Email
FROM (Retailers LEFT 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.RetailerID
WHERE (((Retailers.RetailerName) Like [forms]![frmMultiFieldSearch]![txtRetailerTwo]) AND ((Distributor.DistributorName) Like [forms]![frmMultiFieldSearch]![txtDistTwo]) AND ((Products.ProductName) Like [forms]![frmMultiFieldSearch]![txtProductTwo]));

This gives me the same error as before.

 

[dmuroff]

Would a union help?

 

[lespaul]

Ok, let's start over.

The tables you are trying to get information from are:

Retailers
RetailerID (PK)

Products
ProductID (PK)

Orders
ProductID (FK - composite PK?)
RetailerID (FK)

Distributors
DistributorID (PK)

Uses
RetailerID (FK - composite PK?)
DistributorID (FK)

And you want the retailers that match the RetailerName and DistributorNmae and products name, right?

Did I get all the above right?


Leslie

 

[dmuroff]

Exactly right Leslie!

But if, lets say a retailerName is not entered it should only take into account DistributorName and ProductName and so on.

If you would like to see a copy of the database please let me know. Maybe it would be easier if you saw it because I know my explanations may not be the greatest.

Thanks for the help!

 

[lespaul]

Ok, I'd have to search the threads, but since what you want to do is have a conditional search. If the form control is filled in search for the filled in value, but if the control is blank, return all records. Right?

I'll look around and try to point you in the right direction, I really don't do Access, I try to help with queries and table design and when we get to Access specifics I tend to get a little fuzzy.

I'll post back with anything I find.

les

 

[PHV]

But if, lets say a retailerName is not entered it should only take into account DistributorName and ProductName and so on.
Either play with the IIf and IsNull functions or with the Nz function in your WHERE clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dmuroff]

Do you think it would be simpler to create a query in which ALL records from ALL 6 tables will be displayed and then just query that query?

This would solve the table problem of finding records where it meets all the requirements because all records in existance are being shown.

Is this a poor way of setting up a query?

 

[dmuroff]

OK... I've discovered something...

SELECT DISTINCT Retailers.RetailerID, Retailers.RetailerName, Retailers.Address, Retailers.City, Retailers.Province, Retailers.PostalCode, Retailers.AreaCode, Retailers.PhoneNumber, Retailers.FaxNumber, Retailers.ContactName, Retailers.Email
FROM (Retailers LEFT 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.RetailerID
WHERE (((Retailers.RetailerName) Like [forms]![frmMultiFieldSearch]![txtRetailerTwo]) AND ((Distributor.DistributorName) Like [forms]![frmMultiFieldSearch]![txtDistTwo]) AND ((Products.ProductName) Like [forms]![frmMultiFieldSearch]![txtProductTwo]));

By default:
[forms]![frmMultiFieldSearch]![txtRetailerTwo],
[forms]![frmMultiFieldSearch]![txtDistTwo] and
[forms]![frmMultiFieldSearch]![txtProductTwo]

are set to "*".

So if nothing is entered in these fields the value is "*" meaning all. Now because some retailers do not have products ordered, the productID associated with them is left blank. Is there anything for me to enter in the default value that will include the null fields as well as those that have product information? (I.E. a "*" that takes into account null fields also)

Thanks!

 

[PHV]

Take a look at the Nz function, as I suggested in my previous post:
AND ((Nz(Products.ProductName)) Like [forms]![frmMultiFieldSearch]![txtProductTwo]));

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dmuroff]

Wow!
It's amazing what 2 little letters can do!
Nz worked perfectly!

Thanks PHV!
You saved me!!!!!!!!