Results 1 to 15 of 15
  1. #1
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283

    Delete Query Help

    Hello,



    I have two tables. Table1 And Table2. I have two fields in each table. both are the same, Field1 and Field2. I want to delete all the criteria out of Table1 that matches up with Table2. What do you think is the best way to do this. I cannot setup joins..

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I want to delete all the criteria out of Table1 that matches up with Table2.
    I am not sure what you mean by this. Criteria is found in queries, not tables.

    Perhaps it will make more sense if you can show us samples of your data, and what it is you want to do.

  3. #3
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    I meant delete the data out of Table1 sorry.. okay like..

    Table1
    Field1 Field2
    1 5
    2 6
    3 7
    4 8

    Table2
    Field1 Field2
    1 5
    2 9
    3 10

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    So, do you want to delete data only if there are matches in BOTH Field1 and Field2?
    So, in your example, only 1,5 would be deleted?
    Exactly how many fields are we matching on (I ask because people often oversimplify the questions they ask, and that can lead to problems - let us know exactly what you are working with)?

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Do you have to delete the records? You could do a union query from both tables, then do 2nd query to do a Totals/GroupBy to get rid of the dups and use that query in your database?

  6. #6
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    Yes JoeM that is correct I just want to delete if there are matches in both fields. So 1 and 5 would be deleted. I'm just matching on 2 fields

  7. #7
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    Yes Bulzie I need to delete the records

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Try this:
    Code:
    DELETE DISTINCTROW Table1.*
    FROM Table1 INNER JOIN Table2 ON (Table1.Field2 = Table2.Field2) AND (Table1.Field1 = Table2.Field1);
    For an explanation, see: http://www.fmsinc.com/microsoftacces...ete-query.html

  9. #9
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    For some reason it isn't working. The query runs but it isn't deleting any data. I don't get it.

  10. #10
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If you change it to a SELECT query, does it return anything?
    Code:
    SELECT Table1.*
    FROM Table1 INNER JOIN Table2 ON (Table1.Field2 = Table2.Field2) AND (Table1.Field1 = Table2.Field1);
    If not, that is telling you that there are no duplicates to delete!

  11. #11
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I ran JoeM SQL delete and it worked on my test tables. Do you have a relationship set up between the 2 tables? Can you go into Table1 and manually delete a record to make sure you are able to do that?

    Also, make sure you have a good backup or copy of the data before doing any delete queries.

  12. #12
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    I copied and pasted the data into the other table and it worked..there must have been a space somewhere at the end or something. Can I put a trim function in there?

  13. #13
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I suggest cleaning up your data first before trying to process the Delete Query on it.
    Note that TRIM will work on normal spaces, but not special spaces or characters.

  14. #14
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    I don't know how else to clean it up..This data is getting imported from my outlook from emails.

  15. #15
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    See if you can recreate the situation where there is an apparent match that is not being recognized.
    Then go into each data table, and find out which record has the extra character (use the LEN function to count the characters of each entry, and see which one show a count that is longer than the apparent length).

    Once you have found that, we can determine what character that is by creating calculated fields like this:
    If extra character is at the beginning of the string: Asc(Left([Field1],1))
    If extra character is at the end of the string: Asc(Right([Field1],1))

    Once we have that code, we can look it up on the ASCII table: http://ascii.cl/

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

Similar Threads

  1. Delete Button vs. Delete Query
    By JennyL in forum Access
    Replies: 4
    Last Post: 02-11-2017, 10:11 PM
  2. Replies: 4
    Last Post: 12-13-2016, 09:48 AM
  3. Replies: 2
    Last Post: 01-24-2012, 02:16 PM
  4. Trying to Delete record using delete query
    By MooseOTL in forum Access
    Replies: 13
    Last Post: 10-04-2011, 02:30 AM
  5. Replies: 11
    Last Post: 03-30-2011, 01: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