Results 1 to 4 of 4
  1. #1
    dansutherst is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2011
    Posts
    2

    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
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Instead of deleting records (and then someday regretting that action) you can build a query that returns only the latest record. The query needed for each option is quite similar. Can use two methods to either delete or filter.
    1. Build a query that returns [Job Number] and Max([Imported Date]) and use that as criteria in another query to delete records
    SELECT * FROM FirstQuery RIGHT JOIN [Main Table] ON FirstQuery.[Job Number] = [Main Table].[Job Number]
    WHERE [Imported Date]=[maxOfImported];
    2. Use DMax function in query
    SELECT * FROM [Main Table] WHERE DMax("[Imported Date]","[Main Table]","[Job Number]=" & [Job Number])=[Imported Date];

    To delete replace SELECT with DELETE and instead of = in the WHERE clause use <>

    Notice all the []? If you don't use spaces or special characters or reserved words the [] would not be needed.

  3. #3
    dansutherst is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2011
    Posts
    2
    Hi June7, thanks for getting back to me.

    Like I said I am a complete novice at all SQL code. When i tried to create a new query with the first line of your code it, I changed the (FirstQuery) to the query name i have in my DB which is named (find duplicates for main table) and it gives me a syntax error. All i did was create a new query and paste just your code shown below in and changed where it lists FirstQuery. I have no idea if that is correct or not!? Would it be possible to break it down a bit so I can understand exactly what to do step by step? Ideally I just want to delete the records with this query.

    Thanks very much!

    SELECT * FROM FirstQuery RIGHT JOIN [Main Table] ON FirstQuery.[Job Number] = [Main Table].[Job Number]
    WHERE [Imported Date]=[maxOfImported];

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I know the query structure worked with my data sample. But my data was a different table and field names. I just substituted yours into SQL then posted in thread. Do what I did, use the Query Designer to build query, instead of just pasting my sample.

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

Similar Threads

  1. Exporting records based on date
    By justinwright in forum Import/Export Data
    Replies: 35
    Last Post: 05-11-2011, 04:53 PM
  2. Duplicate query with complex delete function
    By rushforth in forum Queries
    Replies: 9
    Last Post: 08-20-2010, 01:21 AM
  3. Replies: 1
    Last Post: 05-21-2010, 02:22 PM
  4. Replies: 0
    Last Post: 04-08-2010, 12:22 PM
  5. Delete duplicate records
    By Zukster in forum Queries
    Replies: 1
    Last Post: 08-26-2009, 03:14 AM

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
  •  
Other Forums: Microsoft Office Forums