Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete query (Access 2000)

    I want to delete from the table Customers all the customers except the customer number 60.
    If i succeed with the query, i can add on other numbers besides 60, for example 120,121,etc)

    I have built the following delete query :
    DELETE Customers.Customerid
    FROM Customers INNER JOIN orders ON Customers.Customerid = orders.customerid
    WHERE (((Customers.Customerid) Not In (60)));

    However, I receive the follwoing message : " Specify the table you want to delete "

    Can somebody help me ?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Delete query (Access 2000)

    Try the following:
    DELETE *
    FROM Customers INNER JOIN orders ON Customers.Customerid = orders.customerid
    WHERE (((Customers.Customerid) Not In (60)));

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Delete query (Access 2000)

    To add to what Pat has said.

    To delete from customers there is no need to join with orders, unless you are also trying to only delete customers who have orders !

    "If i succeed with the query, i can add on other numbers besides 60, for example 120,121,etc)"

    If you succeed then these people are already gone. Why not start by deleting just one person, then gradually expanding.
    This will delete just person 60.
    <pre> DELETE *
    FROM Customers
    WHERE (((Customers.Customerid) In (60)));
    </pre>

    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •