Syntax Error

C

Cathleen

I keep getting this error in my purchase order report and dont know what I am
missing. I know enough to be dangerous... I may have screwed this up beyond
help. Please see what you can do.
Thank you in advance!
SELECT DISTINCTROW Employees.*, Products.*, Inventory Transactions.*,
Purchase Orders.*, Suppliers.*, nz([Inventory
Transactions].[UnitPrice])*nz([UnitsOrdered]) AS Subtotal, [Shipping
Methods].[ShippingMethod] FROM Suppliers INNER JOIN (Products INNER JOIN
([Shipping Methods] RIGHT JOIN ([Inventory Transactions] INNER JOIN
(Employees RIGHT JOIN [Purchase Orders] ON [Employees].[EmployeeID]=[Purchase
Orders].[EmployeeID]) ON [Inventory Transactions].[Inventory
Number]=[Purchase Orders].[Invoice Number] ON [Shipping
Methods].[ShippingMethodID]=[Purchase Orders].[ShippingMethodID]) ON
[Products].[ProductItemNumber]=[Inventory Transactions].[ProductItemNumber])
ON [Suppliers].[SupplierID]=[Purchase Orders].[SupplierID] WHERE ((([Purchase
Orders].[Invoice Number]=[forms]![Purchase Orders]![Invoice Number]));

Sincerely,
Cathleen
 
J

John Spencer

Well, you definitely are missing a closing parentheses in the from clause.
I'm not quite sure where it goes but I'll guess

SELECT DISTINCTROW Employees.*, Products.*, Inventory Transactions.*,
Purchase Orders.*, Suppliers.*
, nz([Inventory Transactions].[UnitPrice])*nz([UnitsOrdered]) AS Subtotal
, [Shipping Methods].[ShippingMethod]

FROM
Suppliers INNER JOIN
( Products INNER JOIN
( [Shipping Methods] RIGHT JOIN
( [Inventory Transactions] INNER JOIN
( Employees RIGHT JOIN
[Purchase Orders] ON [Employees].[EmployeeID]=
[Purchase Orders].[EmployeeID])
ON [Inventory Transactions].[Inventory Number]=
[Purchase Orders].[Invoice Number] ) <<<---- Was Missing
ON [Shipping Methods].[ShippingMethodID]=
[Purchase Orders].[ShippingMethodID])
ON [Products].[ProductItemNumber]=
[Inventory Transactions].[ProductItemNumber])
ON [Suppliers].[SupplierID]=[Purchase Orders].[SupplierID]

WHERE [Purchase Orders].[Invoice Number]=[forms]![Purchase Orders]![Invoice
Number]
 
C

Cathleen

Thank you so much for replying so quickly.
It still does not work, a (SQL statement: Query builder) has now popped up
and when I save it and try and close it a message pops up that says the "Join
expression not supported". How can I fix that? What does that mean? Thank
you for your patience!
Cathleen

John Spencer said:
Well, you definitely are missing a closing parentheses in the from clause.
I'm not quite sure where it goes but I'll guess

SELECT DISTINCTROW Employees.*, Products.*, Inventory Transactions.*,
Purchase Orders.*, Suppliers.*
, nz([Inventory Transactions].[UnitPrice])*nz([UnitsOrdered]) AS Subtotal
, [Shipping Methods].[ShippingMethod]

FROM
Suppliers INNER JOIN
( Products INNER JOIN
( [Shipping Methods] RIGHT JOIN
( [Inventory Transactions] INNER JOIN
( Employees RIGHT JOIN
[Purchase Orders] ON [Employees].[EmployeeID]=
[Purchase Orders].[EmployeeID])
ON [Inventory Transactions].[Inventory Number]=
[Purchase Orders].[Invoice Number] ) <<<---- Was Missing
ON [Shipping Methods].[ShippingMethodID]=
[Purchase Orders].[ShippingMethodID])
ON [Products].[ProductItemNumber]=
[Inventory Transactions].[ProductItemNumber])
ON [Suppliers].[SupplierID]=[Purchase Orders].[SupplierID]

WHERE [Purchase Orders].[Invoice Number]=[forms]![Purchase Orders]![Invoice
Number]

Cathleen said:
I keep getting this error in my purchase order report and dont know what I
am
missing. I know enough to be dangerous... I may have screwed this up
beyond
help. Please see what you can do.
Thank you in advance!
SELECT DISTINCTROW Employees.*, Products.*, Inventory Transactions.*,
Purchase Orders.*, Suppliers.*, nz([Inventory
Transactions].[UnitPrice])*nz([UnitsOrdered]) AS Subtotal, [Shipping
Methods].[ShippingMethod] FROM Suppliers INNER JOIN (Products INNER JOIN
([Shipping Methods] RIGHT JOIN ([Inventory Transactions] INNER JOIN
(Employees RIGHT JOIN [Purchase Orders] ON
[Employees].[EmployeeID]=[Purchase
Orders].[EmployeeID]) ON [Inventory Transactions].[Inventory
Number]=[Purchase Orders].[Invoice Number] ON [Shipping
Methods].[ShippingMethodID]=[Purchase Orders].[ShippingMethodID]) ON
[Products].[ProductItemNumber]=[Inventory
Transactions].[ProductItemNumber])
ON [Suppliers].[SupplierID]=[Purchase Orders].[SupplierID] WHERE
((([Purchase
Orders].[Invoice Number]=[forms]![Purchase Orders]![Invoice Number]));

Sincerely,
Cathleen
 
J

John Spencer

Basic question did you copy and paste the SQL from the post? If so, did you
remove the "<<<---- Was Missing" from the query?

I really can't help you further, other than to say try building the query
you need one step at a time. It could be that I got the parentheses in the
wrong place. Access drives me crazy with all the parentheses it uses.

At a minimum you know that the problem is in the FROM clause of the query.


Cathleen said:
Thank you so much for replying so quickly.
It still does not work, a (SQL statement: Query builder) has now popped up
and when I save it and try and close it a message pops up that says the
"Join
expression not supported". How can I fix that? What does that mean?
Thank
you for your patience!
Cathleen

John Spencer said:
Well, you definitely are missing a closing parentheses in the from
clause.
I'm not quite sure where it goes but I'll guess

SELECT DISTINCTROW Employees.*, Products.*, Inventory Transactions.*,
Purchase Orders.*, Suppliers.*
, nz([Inventory Transactions].[UnitPrice])*nz([UnitsOrdered]) AS Subtotal
, [Shipping Methods].[ShippingMethod]

FROM
Suppliers INNER JOIN
( Products INNER JOIN
( [Shipping Methods] RIGHT JOIN
( [Inventory Transactions] INNER JOIN
( Employees RIGHT JOIN
[Purchase Orders] ON [Employees].[EmployeeID]=
[Purchase Orders].[EmployeeID])
ON [Inventory Transactions].[Inventory Number]=
[Purchase Orders].[Invoice Number] ) <<<---- Was Missing
ON [Shipping Methods].[ShippingMethodID]=
[Purchase Orders].[ShippingMethodID])
ON [Products].[ProductItemNumber]=
[Inventory Transactions].[ProductItemNumber])
ON [Suppliers].[SupplierID]=[Purchase Orders].[SupplierID]

WHERE [Purchase Orders].[Invoice Number]=[forms]![Purchase
Orders]![Invoice
Number]

Cathleen said:
I keep getting this error in my purchase order report and dont know what
I
am
missing. I know enough to be dangerous... I may have screwed this up
beyond
help. Please see what you can do.
Thank you in advance!
SELECT DISTINCTROW Employees.*, Products.*, Inventory Transactions.*,
Purchase Orders.*, Suppliers.*, nz([Inventory
Transactions].[UnitPrice])*nz([UnitsOrdered]) AS Subtotal, [Shipping
Methods].[ShippingMethod] FROM Suppliers INNER JOIN (Products INNER
JOIN
([Shipping Methods] RIGHT JOIN ([Inventory Transactions] INNER JOIN
(Employees RIGHT JOIN [Purchase Orders] ON
[Employees].[EmployeeID]=[Purchase
Orders].[EmployeeID]) ON [Inventory Transactions].[Inventory
Number]=[Purchase Orders].[Invoice Number] ON [Shipping
Methods].[ShippingMethodID]=[Purchase Orders].[ShippingMethodID]) ON
[Products].[ProductItemNumber]=[Inventory
Transactions].[ProductItemNumber])
ON [Suppliers].[SupplierID]=[Purchase Orders].[SupplierID] WHERE
((([Purchase
Orders].[Invoice Number]=[forms]![Purchase Orders]![Invoice Number]));

Sincerely,
Cathleen
 
C

Cathleen

I removed it and its telling me join error- what does Join error mean? Does
it mean a parenthesis? I appreciate you trying. Thank you

John Spencer said:
Basic question did you copy and paste the SQL from the post? If so, did you
remove the "<<<---- Was Missing" from the query?

I really can't help you further, other than to say try building the query
you need one step at a time. It could be that I got the parentheses in the
wrong place. Access drives me crazy with all the parentheses it uses.

At a minimum you know that the problem is in the FROM clause of the query.


Cathleen said:
Thank you so much for replying so quickly.
It still does not work, a (SQL statement: Query builder) has now popped up
and when I save it and try and close it a message pops up that says the
"Join
expression not supported". How can I fix that? What does that mean?
Thank
you for your patience!
Cathleen

John Spencer said:
Well, you definitely are missing a closing parentheses in the from
clause.
I'm not quite sure where it goes but I'll guess

SELECT DISTINCTROW Employees.*, Products.*, Inventory Transactions.*,
Purchase Orders.*, Suppliers.*
, nz([Inventory Transactions].[UnitPrice])*nz([UnitsOrdered]) AS Subtotal
, [Shipping Methods].[ShippingMethod]

FROM
Suppliers INNER JOIN
( Products INNER JOIN
( [Shipping Methods] RIGHT JOIN
( [Inventory Transactions] INNER JOIN
( Employees RIGHT JOIN
[Purchase Orders] ON [Employees].[EmployeeID]=
[Purchase Orders].[EmployeeID])
ON [Inventory Transactions].[Inventory Number]=
[Purchase Orders].[Invoice Number] ) <<<---- Was Missing
ON [Shipping Methods].[ShippingMethodID]=
[Purchase Orders].[ShippingMethodID])
ON [Products].[ProductItemNumber]=
[Inventory Transactions].[ProductItemNumber])
ON [Suppliers].[SupplierID]=[Purchase Orders].[SupplierID]

WHERE [Purchase Orders].[Invoice Number]=[forms]![Purchase
Orders]![Invoice
Number]

I keep getting this error in my purchase order report and dont know what
I
am
missing. I know enough to be dangerous... I may have screwed this up
beyond
help. Please see what you can do.
Thank you in advance!
SELECT DISTINCTROW Employees.*, Products.*, Inventory Transactions.*,
Purchase Orders.*, Suppliers.*, nz([Inventory
Transactions].[UnitPrice])*nz([UnitsOrdered]) AS Subtotal, [Shipping
Methods].[ShippingMethod] FROM Suppliers INNER JOIN (Products INNER
JOIN
([Shipping Methods] RIGHT JOIN ([Inventory Transactions] INNER JOIN
(Employees RIGHT JOIN [Purchase Orders] ON
[Employees].[EmployeeID]=[Purchase
Orders].[EmployeeID]) ON [Inventory Transactions].[Inventory
Number]=[Purchase Orders].[Invoice Number] ON [Shipping
Methods].[ShippingMethodID]=[Purchase Orders].[ShippingMethodID]) ON
[Products].[ProductItemNumber]=[Inventory
Transactions].[ProductItemNumber])
ON [Suppliers].[SupplierID]=[Purchase Orders].[SupplierID] WHERE
((([Purchase
Orders].[Invoice Number]=[forms]![Purchase Orders]![Invoice Number]));

Sincerely,
Cathleen
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top