Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Removing all but one duplicate! (Access 2000 >)

    Hi,
    The find duplicates query lists all the records that have a duplicate value in the specified field. I cannot just delete all the records that appear in the result as that will delete ALL, where I want to delete all EXCEPT ONE! Do I just change the filter from:
    In (SELECT [InvNo] FROM [Invoices] As Tmp GROUP BY [InvNo] HAVING Count(*)>1 ) to:
    In (SELECT [InvNo] FROM [Invoices] As Tmp GROUP BY [InvNo] HAVING Count(*)>2 ), and delete the resulting records, or is there another way to remove all but one record?
    Regards,
    Rudi

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

    Re: Removing all but one duplicate! (Access 2000 >)

    That won't work. If you have multiple records with the same InvNo, are those records completely identical?
    If so, you can create a query that selects unique records (set the Unique Values property of the query to Yes), then either use the query instead of the table, or change it into a Make Table query to create a new table with unique records.
    If not, you must find a way to decide which one of the records with the same InvNo should be kept, and which ones should be deleted.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Removing all but one duplicate! (Access 2000 >)

    <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>
    Actually, the data is sample data that I acquired recently. I don't care which records remains and which record must be kept! But the bell went off when you said unique values. Changing this property to true in a single InvNo field query will solve my problem! Thx
    Regards,
    Rudi

Posting Permissions

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