Results 1 to 7 of 7
  1. #1
    TundraMonkey is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    20

    Question Delete Query Deleting Too Much

    Hello,



    I can't seem to find a similar thread on this problem, and would appreciate some assistance.

    Importing data to a table each week that can sometimes include duplicate records, I created a duplicate query. It works well to identify those records that are duplicated, but instead of deleting the extra copies and leaving one it proceedes to delete them all.

    Any advice? Is there a better technique than the delete query?

    Thank you!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    How about posting the SQL for your Delete query and maybe we will see something?

  3. #3
    TundraMonkey is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    20
    DELETE [GPS Collar Locations].Date, [GPS Collar Locations].Time, [GPS Collar Locations].LATITUDE, [GPS Collar Locations].LONGITUDE, [GPS Collar Locations].ARGOSid
    FROM [GPS Collar Locations]
    WHERE ((([GPS Collar Locations].Date) In (SELECT [DATE] FROM [GPS Collar Locations] As Tmp GROUP BY [DATE],[TIME],[LATITUDE],[LONGITUDE] HAVING Count(*)>1 And [TIME] = [GPS Collar Locations].[TIME] And [LATITUDE] = [GPS Collar Locations].[LATITUDE] And [LONGITUDE] = [GPS Collar Locations].[LONGITUDE])));

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Change the word DELETE to SELECT and see what records are returned.

  5. #5
    TundraMonkey is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    20
    A-ha! I just removed the lat/long fields and left it to just find dupes of date/time per animal, and it works correctly.

  6. #6
    TundraMonkey is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    20
    I'll remember that tip, though... thank you!

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Excellent! Pretty easy solution wasn't it? Have fun with the rest of the project.

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

Similar Threads

  1. Error when deleting a record
    By access in forum Forms
    Replies: 5
    Last Post: 06-15-2009, 12:33 PM
  2. HELP with Delete Query
    By brown in forum Queries
    Replies: 1
    Last Post: 04-22-2009, 10:11 PM
  3. Problem Deleting Records with ADO
    By bdicasa in forum Programming
    Replies: 0
    Last Post: 08-21-2008, 09:27 AM
  4. Deleting Spaces
    By grgerhard in forum Import/Export Data
    Replies: 2
    Last Post: 04-30-2006, 06:42 PM
  5. Delete query, deleting customers froma table.
    By keithsrobinson in forum Queries
    Replies: 2
    Last Post: 02-14-2006, 11:33 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