Nearly correct query needs checking PLZ

  • Thread starter Thread starter jonathan.cohen150887
  • Start date Start date
J

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)))
 
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
 
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
'====================================================
 
Back
Top