Nearly correct query needs checking PLZ

[jonathan.cohen150887]

Hi,

Ok I am using this query to delete the books from the book_stock table
that havent sold for the past 3 months.
To do it accoridng to the date I am using the order dates from the
customerorders table, and to know which ISBN_Ref
corresponds to which order i included the table
customer_orders_books.ISBN_Ref.

I have written up the following but it says I have a wrong number of
arguments.Could any of you have a look at it plz?

Thanks

DELETE Book_Stock.ISBN.Ref FROM Book_Stock
WHERE Book_Stock.ISBN.Ref =
(SELECT Book_Stock.ISBN.Ref, CustomerOrders.CO_ORDERDATE,
Customer_Books_Orders.ISBN_Ref
FROM Book_Stock, CustomerOrders, Customer_Orders_Books
WHERE Book_Stock.ISBN.Ref = Customer_Orders_Books.ISBN_Ref
HAVING CustomerOrders.CO_ORDERDATE <
DateAdd("mm",-03,Date(15/04/2008)))

 

[jonathan.cohen150887]

Since some books may not have been ordered at all I also included the
line at the end:

DELETE Book_Stock.ISBN.Ref FROM Book_Stock
WHERE Book_Stock.ISBN.Ref =
(SELECT Book_Stock.ISBN.Ref, CustomerOrders.CO_ORDERDATE,
Customer_Books_Orders.ISBN_Ref
FROM Book_Stock, CustomerOrders, Customer_Orders_Books
WHERE Book_Stock.ISBN.Ref = Customer_Orders_Books.ISBN_Ref
HAVING CustomerOrders.CO_ORDERDATE < DateAdd("m",-3,Date(15/04/2008))
AND Book_Stock.ISBN.Ref <> Customer_Orders_Books.ISBN_Ref)


But still, I get an error saying I have the wrong number of arguments

 

[John Spencer]

The subquery in the where clause should only return ONE field (zero to
many records) and you should be using IN as the comparison operator -
not equal.

You cannot use a HAVING clause unless you group (or aggregate the
returned results.

The following MIGHT work for you. I think

DELETE Book_Stock.ISBN.Ref
FROM Book_Stock
WHERE Book_Stock.ISBN.Ref IN
(SELECT Book_Stock.ISBN.Ref
FROM Book_Stock, CustomerOrders, Customer_Orders_Books
WHERE Book_Stock.ISBN.Ref = Customer_Orders_Books.ISBN_Ref
AND CustomerOrders.CO_ORDERDATE < DateAdd("m",-3,#2008/04/15#)
AND Book_Stock.ISBN.Ref <> Customer_Orders_Books.ISBN_Ref)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================