Results 1 to 4 of 4
  1. #1
    scor1pion is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    22

    Problem with delete query

    Hi All, wondering if someone could help me resolve my delete query problem?



    As a select query, this works as I need/want it to
    Click image for larger version. 

Name:	Select Query.jpg 
Views:	8 
Size:	62.8 KB 
ID:	43866

    Query Results, showing me I have a record with a Group but no UserID entered and a record with the same Group and an ID entered.
    Click image for larger version. 

Name:	Select Query Results.jpg 
Views:	7 
Size:	30.0 KB 
ID:	43872


    I want to delete records where the UserID Field is empty, but ONLY if the group has a complete record in the table already.
    Click image for larger version. 

Name:	Entries.jpg 
Views:	7 
Size:	8.8 KB 
ID:	43868

    When I convert to a delete query
    Click image for larger version. 

Name:	Delete Query.jpg 
Views:	7 
Size:	65.0 KB 
ID:	43870

    I get the following error/ I want to delete the record from the very table I ran my query against which is identified in the qury, but it doesn't seem to know that.
    Click image for larger version. 

Name:	Error Message.jpg 
Views:	7 
Size:	12.4 KB 
ID:	43871

  2. #2
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Could you please try:

    DELETE tblGDL_GDL_Members_Work.*
    FROM tblGDL_GDL_Members_Work
    WHERE tblGDL_GDL_Members_Work.[Group] In (SELECT Group FROM tblGDL_GDL_Members_Work WHERE UserID Is Not Null) AND tblGDL_GDL_Members_Work.UserID Is Null;

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    scor1pion is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    22
    Hi Vlad, Thank You!!!

    I corrected the table name...sorry I know they are long and not named the best. But after doing that the query worked. Access made some changes to it on its own...guess it does that. Here is what access changed it to.

    DELETE tbl_GDL_Group_Members_Work.*, tbl_GDL_Group_Members_Work.Group, tbl_GDL_Group_Members_Work.UserID
    FROM tbl_GDL_Group_Members_Work
    WHERE (((tbl_GDL_Group_Members_Work.Group) In (SELECT Group FROM tbl_GDL_Group_Members_Work WHERE UserID Is Not Null)) AND ((tbl_GDL_Group_Members_Work.UserID) Is Null));

  4. #4
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to hear it worked. Yes, Access does add a lot of brackets sometimes.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Delete query to delete records that meet criteria
    By neill_long in forum Queries
    Replies: 1
    Last Post: 06-11-2018, 02:41 PM
  2. Delete Query Problem
    By altrotx in forum Queries
    Replies: 1
    Last Post: 04-01-2013, 06:59 PM
  3. Replies: 2
    Last Post: 01-24-2012, 02:16 PM
  4. Problem With Delete Query
    By jo15765 in forum Queries
    Replies: 1
    Last Post: 12-22-2011, 11:04 AM
  5. Problem using rst.delete
    By P5C768 in forum Programming
    Replies: 3
    Last Post: 03-10-2011, 11:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums