Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2011
    Posts
    9

    Unanswered: Delete entire records from a duplicate query based on oldest date

    This is my first post so be kind! I am having a problem with my Access 2003 DB. I have a duplicates query which searches the Main Table and brings back all duplicate records based on their Job Number. However now I want to create a delete query that looks at that duplicates query and deletes all the duplicate records with the oldest date, for example:

    Job Number Imported Date
    12345 28\04\2011
    12345 24\02\2011


    I would want to run the delete query and be LEFT with the below records:

    Job Number Imported Date
    12345 28\04\2011


    So it deleted the duplicate record that was imported into the DB first (therefore its the oldest record out the two). Please can anyone help???

    I'm not a DB master so presumably when someone lists code to put into the SQL view just talk to me like a newborn baby

    THANKS!!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If there is an Identity column in your table (an Identity column is a column [column = field] that uniquely identify a row [row = line] in a table, e.g. an AutoNumber column) you can use the following SQL statement (in my example SysCounter is the name of the Identity column):
    Code:
    DELETE Main_Table.*
    FROM Main_Table
    WHERE Main_Table.SysCounter Not In (
          SELECT MAX(SysCounter) 
          FROM Main_Table mt 
          WHERE Imported_Date=(
                SELECT MAX(Imported_Date) 
                FROM Main_Table 
                WHERE Job_Number=mt.Job_Number) 
          GROUP BY Job_Number);
    If there is no Identity column in the table, I don't see why you could not add one.
    Have a nice day!

  3. #3
    Join Date
    Apr 2011
    Posts
    9
    Hi Sinddho,

    Thanks for your reply. I tried to copy your code into a new query but it said it couldnt find main table. I tried with a space, without a space, with an underscore and every other variation I could try but just couldnt get it to work. This is obviously a n00b error from me but if you could let me know where i am going wrong that would be great.

    I have since created a new unique field called 'SysCounter' and have got a 'find duplicates' query that finds all the duplicate records. The only piece of the puzzle i want it to do is have a delete query that will look up records from that 'find duplicates' query and delete all the duplicates bar the one with the highest number in the 'SysCounter' field. So in theory it will delete all duplicates and leave the most up to date record with the highest number in the 'SysCounter' field.

    If you could give me some expert advice that would be awesome!

    Thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You have to replace 'Main_Table' by the actual name of the table in your database. If you table is named 'Main Table' (without quotes) you have to use sqare brackets because the space character cannot appear in an object name, so 'Main Table' becomes '[Main Table]' (all without the single quotes).

    By the way, it's always recommended to avoid using spaces and other special characters as well as reserved words in the names of database objects. See for instance: MS Access Tips - Access Explained

    My example then becomes:
    Code:
    DELETE [Main Table].*
    FROM [Main Table]
    WHERE [Main Table].SysCounter Not In (
          SELECT MAX(SysCounter) 
          FROM [Main Table] mt 
          WHERE Imported_Date=(
                SELECT MAX(Imported_Date) 
                FROM [Main Table] 
                WHERE Job_Number=mt.Job_Number) 
          GROUP BY Job_Number);
    Have a nice day!

  5. #5
    Join Date
    Apr 2011
    Posts
    9
    Thanks for getting back to me so quickly!

    I have copied that code in but when executed it asks for parameters values for the imported_date, job_number and mt job_number.

    I have since delete the imported date field. Since creating the field SysCounter that you suggested i had no need for the Imported Date. I tried to delete all references to Imported Date but when it ran it asks for the parameter values which i just clicked OK and it brings back every record.

    Could you please advise?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What I wrote about spaces and table name is also valid for column names: if there is a space in ANY object name (Table, Column, Query, etc.) that name MUST be enclosed in square brackets, so 'Imported Date' becomes '[Imported Date]', etc.
    Have a nice day!

  7. #7
    Join Date
    Apr 2011
    Posts
    9
    I am having no joy unfortunately, I have attached my DB and wondered if you could take a look? The password is tomw to get into the DB.

    Ideally I just need to create a new delete query that looks up records from the query named 'duplicates from main table' and delete all the duplicate records that have a lower SysCounter number. Whether you can do this delete query without using the 'duplicates from main table' query I'm not sure.

    For Example:

    Job Number SysCounter
    NCC546 120
    NCC546 254
    NCC546 367

    So it would delete the 2 duplicate records and keep the below record:

    Job Number SysCounter
    NCC546 367



    Thanks very much in advance
    Attached Files Attached Files

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is even easier as you don't have to take care of the anteriority/posteriority status of the rows that was determined by the [imported date] column. The query now simply becomes:
    Code:
    DELETE [Main Table].*
    FROM [Main Table]
    WHERE [Main Table].SysCounter Not In (
          SELECT MAX(SysCounter) 
          FROM [Main Table] 
          GROUP BY [Job Number]);
    Have a nice day!

  9. #9
    Join Date
    Apr 2011
    Posts
    9

    Talking

    Thanks that worked an absolute treat! I just create a complete duplicate of the table and it deleted half the records and all of them were the lower autonumber ones.

    Thanks very much for your assistance and patience!!


  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  11. #11
    Join Date
    Apr 2011
    Posts
    9
    ME AGAIN!!

    I have just been through the whole process and it has thrown up one last query.

    Basically, each month we import a mass of data from a CSV file and that will have records from day 1 to the current day. The problem we will have is duplicate records that have an invoice status of 'YES' but a lower autonumber than the records just imported.

    So the delete query will delete all the duplicates with the lowest autonumber and leave the record in tact with the higest autonumber. Which means the record which has been invoiced will be deleted which is obviously not what we want. For example:

    Job Number Invoice Status SysCounter
    JCC12345 blank 100
    JCC12345 yes 55

    The delete query would find those duplicates and delete the record in red font due to it having the lower autonumber. Is there anyway to ammed that delete query to never delete a record with 'YES' in the 'Invoice Status' field?

    So it would have to compare the records and if the 'Invoice Status' was blank it would delete that duplicate but if the invoice status was YES then it would delete the other duplicate with a null value?

    I hope this makes sense!

    Thanks for all your help

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This should work:
    Code:
    DELETE Main_Table.*
    FROM Main_Table
    WHERE Main_Table.SysCounter In (SELECT MIN(SysCounter) 
          FROM Main_Table
          WHERE Nz(Status, "") <> 'Yes'
          GROUP BY Job_Number);
    The use of the Nz() function is necessary to convert Null values to zero-length strings in the [Status] column because the comparison yields a different result set when Null values are present in the [Status] column.
    Have a nice day!

  13. #13
    Join Date
    Apr 2011
    Posts
    9
    Thanks for the reply!

    At first it gave me some errors but I realised i just needed to change some of the names around. Below is the code as it stands now. It runs without any errors but returns all records:

    DELETE [Main Table].*
    FROM [Main Table]
    WHERE [Main Table].SysCounter In (SELECT MIN(SysCounter)
    FROM [Main Table]
    WHERE Nz([Invoice Status], "") <> 'Yes'
    GROUP BY [Job Number]);


    Any ideas?

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There must be some difference between the table I use for testing my queries and the actual table you work with. Here everything works as expected (see attached screeshots).

    The only thing that comes to my mind at the moment (sorry: it's been a long week ) is that the [Invoice Status] column in your table is of Boolean type (Yes/No) here it's just a Text data type column. This could explain why the SQL statement does not work properly. If this is the case (Boolean column), then the query should be:
    Code:
    DELETE Main_Table.*
    FROM Main_Table
    WHERE Main_Table.SysCounter In (SELECT MIN(SysCounter) 
          FROM Main_Table
          WHERE Nz(Status, False) <> True
          GROUP BY Job_Number);
    Attached Thumbnails Attached Thumbnails ScreenShot006.jpg  
    Have a nice day!

  15. #15
    Join Date
    Apr 2011
    Posts
    9
    Hi,

    Thanks for getting back to me. I have just tried that code out and no joy. It only selects the records with blank in the 'Invoice Status' column. I have pasted the exact code used below:

    DELETE [Main Table].*
    FROM [Main Table]
    WHERE [Main Table].SysCounter In (SELECT MIN(SysCounter)
    FROM [Main Table]
    WHERE Nz([Invoice Status], false) <> true
    GROUP BY [Job Number]);

    Any ideas? Would it be possible for you to try the code at your end in the attached DB? In the DB i have attached it only has 3 records they are all duplicates. One record has a blank 'Invoice Status', one has a YES and the other has a NO. So in theory if the query works it would just leave the Yes record.

    Cheers
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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