Help with my IFF AND statement. Error is provided.

[vennardk]

First off, I need to say that I am trying to teach myself the ins and outs of access so I am still very new. I use the design mode for my queries. I realize a lot of experts use the SQL view but I can't quite grasp that yet. I am taking the northwind traders example and modifying it to work for me. I am using Access 2007. Anyway, here is my query along with the error message:

SELECT [Order Details].*, IIf([Customers].[State/Province]="MI" And [Products].[Taxable]="Yes",CCur([Quantity]*[Unit Price]*(1-[Discount]))*0.06,0) AS Tax, CCur(([Quantity]*[Unit Price]*(1-[Discount]))+[Tax]) AS [Extended Price], [Order Details Status].[Status Name]
FROM Products INNER JOIN ((Customers RIGHT JOIN Orders ON Customers.ID = Orders.[Customer ID]) LEFT JOIN ([Order Details] LEFT JOIN [Order Details Status] ON [Order Details].[Status ID] = [Order Details Status].[Status ID]) ON Orders.[Order ID] = [Order Details].[Order ID]) ON Products.ID = [Order Details].[Product ID];

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 separate query that performs the first join and then include that query in your SQL statement. (Error 3258)

I kind of understand what the error is saying... Basically it doesn't know what to do first. I've tried splitting it up to no avail. What I am trying to do is say "If customer's state is Michigan (MI) AND product is Taxable" Than add tax.

Also, I am not sure I am doing it correctly because someone from Michigan could have two products (one taxable and one not taxable) how can I tell it to add tax to only the one product, not the subtotal?

Thanks for any help.

 

[redneckgeek]

You have two distinct problems there. The way access builds the FROM statement based on your QBE grid is sometimes mindboggling.

That's not a huge query... start from scratch. Get all your joins in line first, before trying to add expressions. I don't think you need that many Outer joins (left or right) if your database was built properly. Add a table, open the query, add another table, open the query... Then go back and put your expressions in.

 

[apr pillai]

Query Problem

INNER JOIN and LEFT JOIN will not work together even if they are on different Tables. It is better if you split the Query into more than one. If possible the LEFT JOIN part in one query and use that Query as input to the Next one for INNER JOIN or RIGHT JOIN etc.

 

[raskew]

Hi -

I don't have A2007 (thank God), but apparently the layout of Northwind Traders has changed because [Products].[Taxable] and ([Customers].[State/Province] you cite don't exist in my earlier version. I'm a little confused about your Iif() statement since I can't spot the False statement, which is a requirement with the Iif() function. It just may be that I'm a little slow.

Anyway, here's a working example using an earlier version that applies the rule to customers from California (CA), since there are no MI customers included.

SELECT
    [Order Details].*
  , [Order Details].[Quantity]*[Order Details].[UnitPrice]*(1-[Discount])*0.06 AS Tax
  , [Order Details].[Quantity]*[Order Details].[UnitPrice]*(1-[Discount])+[Tax] AS ExtendedPrice
  , Customers.Region
FROM
   Customers 
INNER JOIN
   (([Order Details] 
INNER JOIN
   Products 
ON
   [Order Details].ProductID = Products.ProductID) 
INNER JOIN
   Orders 
ON
   [Order Details].OrderID = Orders.OrderID) 
ON
   Customers.CustomerID = Orders.CustomerID
WHERE
   (((Customers.Region)="CA"));

Note that all the joins are INNER JOINS.

HTH - Bob

 

[vennardk]

raskew,

My first post had my edited code. Here is the Original code for Order Details Extended:

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


Thanks for the code. It looks very good. How can I have it check to see if the product is taxable?

Just to clarify my business can have the following situations:

Taxable good in MI = Tax the product
Non Taxable good in MI = No tax
Taxable good in non-MI = No Tax
Non Taxable good in non-MI = No Tax

The problem is a lot of order will have a taxable good and a non taxable good going to MI. The current system I think will tax it all because they are in michigan. Rather I need to check once to see if they are in Michigan and check again to see if each product is taxable.

Currently, I can not seem to find where tax is calculated. There is a table entitled Orders Tax Status. However, no field in the Products table.

If you need me to post any tables or queries, please let me know. Thanks for your guidance!

 

[raskew]

Hi -


Just checked again. I've got no Orders Tax Status table, so if you wanted to post that, perhaps it would be helpful.

Bob

 

[vennardk]

The table is very simple:

Design View:
ID
Tax Status Name

Table View:
0 Tax Exampt
1 Taxable

I looked at the relationship tables to see what this table does... It is a one to many relationship to the Orders Table. So [Orders Tax Status]. [ID] is linked to [Orders].[Tax Status]

This is discouraging because I believe it is currently set-up so that an entire order is either taxable or exempt instead of individual products like I need.

Thanks for your time.

EDIT: Just an FYI as we go through this, I have not added any tables, queries, forms, ect...