ambiguous join error

[vennardk]

Been getting this error message for the last few days and I am unaware of how to fix it. I am trying to run one query that does Quantity*Unit Price*Discount*Tax

Tax is a query that is an IFF statement along the lines of "If ship State="MI" AND Product.Taxable="Yes", 1.06, 1"

Here is my SQL view of my two queries:

Order Details Extended:
SELECT [Order Details].*, CCur([Quantity]*[Unit Price]*(1-[Discount])*[Sales Tax].[Tax])
AS [Extended Price], [Order Details Status].[Status Name]
FROM [Sales Tax], [Order Details]
LEFT JOIN [Order Details Status] ON [Order Details].[Status ID] = [Order Details Status].[Status ID];

Sales Tax:
SELECT IIf([Orders].[Ship State/Province]="MI" And [Products].[Taxable]="Yes",1.06,1)
AS Tax
FROM Products
INNER JOIN ((Customers RIGHT JOIN Orders ON Customers.ID = Orders.[Customer ID]) INNER JOIN [Inventory Transactions] ON Orders.[Order ID] = [Inventory Transactions].[Customer Order ID]) ON Products.ID = [Inventory Transactions].[Product ID];

I see that I have a lot of joins in Sales Tax (too many?) However, I truly do not even know what left, right, and inner join means.

Where am I going wrong?

P.S. Everything is taken from Northwind Traders (2007) besides [Tax] being added in the first query and me designing the [Sales Tax] query.

 

[vennardk]

any input at all?

 

[khawar]

try using inner join instead of Left join

 

[neileg]

FROM [Sales Tax], [Order Details] 
LEFT JOIN [Order Details Status] ON [Order Details].[Status ID] = [Order Details Status].[Status ID];

There is no join for [Sales Tax]

 

[namliam]

Where am I going wrong?

Atleast one thing: DONT splurge your SQL on the forum like this
1) Use [ code ] and [/ code ], without the spaces around your code whenever you post (SQL) code on the forum

2) Format your SQL so it is readable!

3) Dont use spaces or special characters like /.)(*& etc in any names anytime anywhere....

SELECT IIf([Orders].[Ship State/Province]="MI" And [Products].[Taxable]="Yes",1.06,1) 
AS Tax
FROM Products 
INNER JOIN ((Customers 
RIGHT JOIN   Orders                 ON Customers.ID      = Orders.[Customer ID]) 
INNER JOIN [Inventory Transactions] ON Orders.[Order ID] = [Inventory Transactions].[Customer Order ID]) 
                                    ON Products.ID       = [Inventory Transactions].[Product ID];

Now that it is readable I can see that you are joining:
Products = [Inventory Transactions] => Orders = Customers

Legend:
= Inner join
=> Rigth Join
<= Left Join

Once you join a table "right" you HAVE to keep going "right" and not turn around again to equal or left.... it is a one way street.
So you have to change your inner join to Customers to a Right join, then it should work.
Products = [Inventory Transactions] => Orders => Customers