Results 1 to 6 of 6
  1. #1
    krag is offline Medium experience
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    71

    Deleting records from a table which have same field values in another query

    I have a master table of machine tool layouts with enquiry number and tool layout numbers as main fields (not using any primary key).


    I have a query generated separately from other criteria in the project, listing the enquiry numbers and tool layout numbers that I want to delete from the master table.
    I have made delete query showing the master table as "Where" and built in the criteria for the 2 main fields referring to the other query.
    In datasheet view it is correctly showing the selected records.
    However, when I run the query to delete, it gives a message "Could not delete from specified tables".

    I also tried using the field joins from the query to the table but same thing happens.
    Any ideas on how to make it work?

    I could of course use recordset in VBA and delete the selected records using a loop but would like to avoid that.
    Thanks in advance

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Change the query to use Unique Records.
    That is DELETE DISTINCTROW .... instead of DELETE ...
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    krag is offline Medium experience
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    71
    Hi isladogs
    That was quick and worked.
    First time to use DISTINCTROW.
    Looks like there is no way to set it in normal design view but only in SQL.
    Thanks a lot.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You can also do it in design view by editing the query property sheet.

    Click image for larger version. 

Name:	Screenshot 2021-09-24 133508.png 
Views:	11 
Size:	12.9 KB 
ID:	46283
    Change unique records to Yes
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    krag is offline Medium experience
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    71
    Thanks again isladogs
    That is a very useful tip and I can use that in many other places as well.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome
    Setting unique records = yes (DISTINCTROW) is usually required where running DELETE or UPDATE queries involving more than one table.

    You may find my 2 part article on Synchronising Data useful. In particular, the second part contains several examples where this method is used
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 5
    Last Post: 03-11-2019, 02:35 PM
  2. Replies: 3
    Last Post: 02-16-2016, 03:10 PM
  3. MS Access sum values of one field in all records of one table
    By logamuthu in forum Import/Export Data
    Replies: 2
    Last Post: 11-22-2011, 12:39 PM
  4. Replies: 11
    Last Post: 12-14-2010, 01:25 PM
  5. Deleting values after a field is updated
    By mwhcrew in forum Forms
    Replies: 3
    Last Post: 08-16-2010, 02:52 AM

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