Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Query (2003)

    I am trying to create a delete query. I want to delete records that have only a vision product. A client can have multiple products. Client A can have products PPO and VIS or any number of variations. I want to delete those clients that just have VIS. How would I not delete those clients that have VIS but also PPO, HMO etc. Client A has VIS, PPO, Trad. Client B has VIS only. I want to delete clients that have VIS only. Thanks for any help you can provide.

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Query (2003)

    Linda,
    Is all this information in one field? Or is it in separate fields? If it's in one field, you can use criteria ="Vis" in your delete query. If it is in multiple fields, you would have to use two criteria for each field, both Is Not Null and <> "Vis".
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Query (2003)

    That data is as follows
    Client Product
    ClientA PPO
    ClientA VIS
    ClientB VIS
    ClientC VIS
    ClientD HMO
    ClientD VIS
    ClientD POS

    The only client I want to delete is ClientB and ClientC.
    I don't want to delete the other clients because they have other products.
    I hope I am not confusing things.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Query (2003)

    I don't understand - "you would have to use two criteria for each field, both Is not Null and <> "VIS'.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Query (2003)

    Ok, I didn't know you had multiple records for each client. I have a *dirty* fix, however I'm sure one of the admins will come up with a better solution. You might want to create a find duplicates query. After that, create a query, sourcing your find duplicates query as well as your main table, linking on your ClientID (or client name, or whatever distinguishing field you have). Filter for only those with one duplicate, and then from that select those records who also have "VIS". Hope that is clear enough.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Query (2003)

    You need several queries. Say thay your table is named tblData.

    First, create a query qryVIS that returns all clients that have a vision product (and possibly other products):

    SELECT Client
    FROM tblData
    WHERE Product = "VIS"

    Next, create a query qryNoVIS that returns all clients that don't have a vision product:

    SELECT Client
    FROM tblData
    WHERE Product <> "VIS"

    (that is < > without a space in between)

    Finally, create a delete query:

    DELETE Client
    FROM qryVIS
    WHERE Client Not In (SELECT Client FROM qryNoVIS)

    Replace Client and Product with the appropriate field names.

Posting Permissions

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