I pasted your SQL query in place of mine. It would not run because of
error - "improper bracketing of John Deere Invoice".
Thanks
JR
"Bill Manville" wrote in message
...
I pasted your query into an Access database of mine.
Obviously it would not work as I don't have your tables, but it did appear
in
the list of available views in MSQuery (make sure you have Views selected
in
the Options dialog from the MSQuery wizard).
It is unusual in my experience to use one field that you name in the query
in
the definition of another field and I wonder whether that might be a cause
of
a problem.
I would have written it as
SELECT Orders.OrderDate, Contracts.ContractNum,
Orders.PurchaseOrderNumber,
Customers.CompanyName, Customers.BillingAddress, Customers.City,
Customers.StateOrProvince, Customers.PostalCode, Orders.ShipName,
Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince,
Orders.ShipPostalCode, Orders.ShipCountry, [Order Details].LineItem,
[Order
Details].Quantity, Products.ProductCode, Products.ProductName,
Customers.ControlNum, [Order Details].SerialNum, [Order
Details].UnitPrice,
[Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price],
IIf([Order Details]!UnitPrice*[Order Details]!Quantity=0,[Order
Details]!UnitPrice*[Order Details]!Quantity,Null) AS [Amount Before
Trade-In],
IIf([Order Details]!UnitPrice*[Order Details]!Quantity<0,[Order
Details]!UnitPrice*[Order Details]!Quantity,Null) AS [Trade-In Amount],
[Order
Details].OrderID, Orders.ShipDate, [Order Details].OrderID, (IIf([Order
Details]!UnitPrice*[Order Details]!Quantity=0,[Order
Details]!UnitPrice*[Order Details]!Quantity,Null)*0.95) AS [Dealer 95%],
0.5*IIf([Order Details]!UnitPrice*[Order Details]!Quantity=0,[Order
Details]!UnitPrice*[Order Details]!Quantity,Null) AS [SEC 5%],
Dealer.DealerName, Dealer.ContactPerson, Dealer.City, Dealer.State,
Dealer.UnitCode, Dealer.DealerNum, Dealer.DealerType, [DlvDlrJDInv] AS [JD
Invoice #], [JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD
Invoice Amount]*Products!HandlingPct AS [Handling $]
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON
Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
((Contracts INNER JOIN (SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) ON Contracts.ContractID = Products.ContractID) INNER JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID) ON
Orders.OrderID = [Order Details].OrderID;
Incidentally, I am surprised by the 0.5 in that query - should it not be
0.05?
You can always reconstruct the query in MSQuery if you are having trouble
accessing its Access definition.
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup
|