Query Error Help.

[Work23]

Hello. I have limited knowledge using Microft Access. I am trying to use the minus operation in my query and its producing an error. Below is the code along with the error code. Is there anything I'm missing in theory or is it just syntax. Any help is greatly appreciated. Thank you very much.

(SELECT PRODUCT.PRODUCTNAME
FROM PRODUCT, ORDEREDPRODUCT)
MINUS
(SELECT PRODUCT.PRODUCTNAME
FROM PRODUCT
WHERE PRODUCT.PRODUCTNUMBER=ORDEREDPRODUCT.PRODUCTNUMBER);


**Sytanx error in Union Query**

 

[PHV]

What is supposed to do the minus operation ?

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

 

[Work23]

The result of the query is supposed to be the list of the products never ordered.

So, I'm selecting the product names from the product table minus the selection of the productnames with the join of product number from the product table and the ordered product table.

 

[PHV]

Something like this ?
SELECT P.PRODUCTNAME
FROM PRODUCT As P LEFT JOIN ORDEREDPRODUCT As O
ON P.PRODUCTNUMBER = O.PRODUCTNUMBER
WHERE O.PRODUCTNUMBER Is Null

Or this ?
SELECT PRODUCTNAME
FROM PRODUCT
WHERE PRODUCTNUMBER Not In (SELECT PRODUCTNUMBER FROM ORDEREDPRODUCT)

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