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

    Delete Query removing Smallest values

    Hello,

    I have a table(Table1) and have two fields. Field1 and Field2. Field2 is the field that has the values. There can be duplicate values in Field1. for example:

    Field1 Field2
    Mark 1
    Mark 2
    Mark 3



    I want to remove the smallest values from the table and only keep the max value.

    Thanks for your help

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    MAKE A BACKUP OF YOUR DB

    Then try this DELETE query:
    Code:
    DELETE tblYourTable.Field1, tblYourTable.Field2FROM tblYourTable
    WHERE (((tblYourTable.Field2)<>(SELECT Max(tblYourTable.Field2) AS MaxOfField2 FROM tblYourTable;)));
    Replace tblYourTable with the name of your table
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    Hello, it is deleting every record that doesn't have the max number in Field2. Is there a way we can make it so we keep the Max record of each unique value in Field1?

  4. #4
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    I'm sorry if I'm not being clear enough, but I'm trying to keep the max number of each unique number in field1

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    That is what it should do.
    Can you post the SQL code you came up with for your DELETE query, and then actually provide a small data example, and explain what is happening to those records when you run the DELETE query?

  6. #6
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    DELETE FROM Table1
    WHERE (((Table1.Field2) <> (SELECT MAX(Table1.Field2)
    FROM [Table1])));

    Here is an example of my data:
    Field1 Field2
    Mark 1
    Mark 2
    Jess 3
    Jess 4
    Rachel 5

    And when I run the query it is deleting every record except for Rachel because it has the max value. But I want it to remove all the minimum values grouped by Field1. So keep the max values for Mark, Jess and Rachel.

  7. #7
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Try this:
    Code:
    DELETE FROM Table1
    WHERE Table1.Field2 Not In (SELECT DMax("Field2","Table1","Field1='" & [Field1] & "'") AS MaxOfField2
    FROM Table1);

  8. #8
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    Nice! it deleted most of them but for some reason their are still a couple records it didn't delete.

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Can you post some examples that it did not delete?

    Potential reasons I can think of is the following:
    - Multiple records with the same max value for the same group
    - An extra space or character in Field1, so records are not being grouped in the way you think

  10. #10
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    I can't post the data I'm sorry it's private information. But the records don't have the same max value. Maybe there are extra spaces in there. Can we apply a trim function?

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Try running this query and viewing the results.:
    Code:
    SELECT Table1.Field1, Count(Table1.Field2) AS CountOfField2
    FROM Table1
    GROUP BY Table1.Field1
    ORDER BY Table1.Field1;
    It will Group the records by Field1 and count the number of them.
    For the records it isn't deleting, are you seeing multiple similar entries for Field1?
    If you run this after running the other query, you should only see ones in the Count column. Do you see any records showing anything other than a 1?
    If so, look at those records in your table, and look at what different values are entered in for Field2 for them. Are they are all numeric?

  12. #12
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    Yes there are more than 1 counts for a couple records in Field1. The data is different in Field2 for them also. The field is a text field but with numeric data

  13. #13
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You might want to try changing it to a numeric field. Perhaps how some of those numbers are written don't work well with the Max function, which is a function for numbers.

  14. #14
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    I changed it to a numeric field and am getting the same thing.

  15. #15
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Can you post the actual SQL code for the query you are using?
    Maybe you made an error when trying to translate it from the simple example shown here to your actual database.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-05-2015, 12:23 PM
  2. Helo with removing duplicate values in a query
    By David Sabot in forum Queries
    Replies: 1
    Last Post: 04-22-2013, 12:26 PM
  3. delete query produce #deleted in all deleted values
    By learning_graccess in forum Queries
    Replies: 2
    Last Post: 03-31-2012, 07:20 AM
  4. Smallest in group
    By pvanryzin in forum Queries
    Replies: 3
    Last Post: 08-17-2009, 09:42 AM
  5. Removing the delete prompt.
    By botts121 in forum Programming
    Replies: 4
    Last Post: 06-26-2009, 11:45 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