Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Dups (2003)

    I have a query where I did a find duplicates query and I have 312 duplicates. Really I have half of that. The find duplicates query shows both records. I want to keep one of the duplicates and get rid of the other. Is there a way to only show one duplicate? I want to then delete those records leaving me with one record per set of duplicates. I should have 156 records once I delete the duplicate records.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Dups (2003)

    You'll have to find a way to specify which of each pair of duplicates you want to delete. Access cannot decide that for you.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Dups (2003)

    Either one can be deleted. The code in the find duplicates query is as follows:
    In (SELECT [Group Number] FROM [TEST4] As Tmp GROUP BY [Group Number] HAVING Count(*)>1 )
    Is there some way to edit this to show only one of the dups? Then I could just delete those records and have only one left.
    Thanks for your help.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Dups (2003)

    I am getting a syntax error. Here is what I am using:
    (SELECT Max(t.[Provision Value] FROM TEST4 AS t WHERE t.[Group Number] = TEST4.[Group Number]))
    At first it said I was missing a parenthesis so I put one at the end.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete Dups (2003)

    try:
    (SELECT Max(t.[Provision Value]) FROM TEST4 AS t WHERE t.[Group Number] = TEST4.[Group Number])
    Put the parenthese after the field that is MAX

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Dups (2003)

    Am I supposed to leave the criteria in the Group Number field also? When I run the query, it doesn't look like it is running.

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete Dups (2003)

    I am not sure, you will have to ask Hans, Ia mnot that good on Dupe Queries

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Dups (2003)

    I ran the query that found the dups. I exported it into Excel and ran a macro to remove the dups. I imported the file back to Access. Now I have a table of the records I want to delete from the query Test4. How do I do this? I can make a table out of the query Test4 if I can't use the Test4 query. I don't know how to construct a delete query.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Dups (2003)

    Edited by HansV to correct error

    Is there another field in TEST4 that is different for each record in a pair of duplicates? If so, add this field to the Find Duplicates query (if it isn't already present). Let's say it is named Field1. Set the criteria for this field to

    (SELECT Max(t.[Field1]) FROM TEST4 AS t WHERE t.[Group Number] = TEST4.[Group Number])

    Switch to datasheet view to verify that the query selects the correct number of records. Then switch back to design view.
    You should now be able to change the query to a delete query and run it.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Dups (2003)

    There was an error in my previous reply. Zave has already posted the correction.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Dups (2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Dups (2003)

    I have two tables - tblTest4 and TblDups. How do I remove the records in tblTest4 using tblDups. I have searched on how to create a Delete query but I get nothing useful.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Dups (2003)

    Depends on the structure of the tables. There is no general recipe. See my previous reply.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Dups (2003)

    The data is sensitive. See attached. I joined the dup table to the table that I want to remove dups from and the select query shows the records I want to delete. Now how do I delete from the table? The attached shows the message I get.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Dups (2003)

    Can you change "Where" to "From" in one of the columns from Test4tbl, or does it automatically change back to "Where"?

Page 1 of 2 12 LastLast

Posting Permissions

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