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

    Delete duplicate records (2000)

    I have a table that has ProductId, tierID and ProvisionDetail
    I would like to delete the records that have duplicate ProductID according to whether the Provision Detail is the same. If ProvisionDetail contains a "Yes" for both duplicate ProductID's then I would like to delete one. If one of the duplicate productID's has a "Yes" for ProvisionDetail and the other a "NO" for ProvisionDetail, then I want to keep both records. How would I set this up and if you could be specific I would be grateful.

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

    Re: Delete duplicate records (2000)

    Can two records with the same ProductID both have ProvisionDetail = "No"? If so, what do you want to happen with these?

    Can there be more than two records with the same ProductID? If so, do you want to keep only one record for each value of ProvisionDetail, or ...?

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

    Re: Delete duplicate records (2000)

    Yes - two records with the same ProductId can have ProvisionDetail = No. In that case, I still want to remove one of the two. Yes to the second question too - there can be more than just two duplicates of ProductID. I would like to end up with just one ProductId - two at the most - with different ProvisionDetails.
    I hope this make sense. Thanks for your help.

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

    Re: Delete duplicate records (2000)

    The easiest solution is to copy the unique records to a new table, and use that instead of the original one:
    <UL><LI>In the database window, copy, then paste the table. Select the "Structure Only" option.
    <LI>Create a query based on the original table. Add all fields to the query grid.
    <LI>Change it into a Totals query. Leave ProductID and ProvisionDetail at Group By, and set the other fields to First.
    <LI>Switch to Datasheet view to check whether the outcome is what you want.
    <LI>Switch back to design view, and change the query into an Append Query. Select the new table as target.
    <LI>Execute the query by selecting Query | Run or clicking the Run button.[/list]If this is not feasible, it'll be more complicated.

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

    Re: Delete duplicate records (2000)

    Thank you so much for your help. You have helped me in so many Access delimas. Thanks so much for your help.

Posting Permissions

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