Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2006
    Posts
    40

    Unanswered: Duplicate records issue

    Hi guys,

    I am using MS Acess in visual basic environment. what i wants to achieve is to be able to check for any duplicate entries in the database using sql quey (insert, update). But nt too sure as to hw this can be achieved. Any guidance or code ref is kindly appreciated. I tried to use a foreach loop to iterate through the records but nt working fine, i have declared the variables as a variant for the for each loop.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    There's a query wizard just for this! In Help enter Find duplicate records then click on Find duplicate records or field values using a query wizard.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Getting rid of the duplicates is the fun part. Here's a general game plan on 1 way to do it:
    1. Change the query Missinglinq mentioned above to a make-table query and make a table (call it for example: DupRecs)
    2. If you have a lot of linked tables to your main table with duplicates, this will make it more difficult, if not, then proceed....
    3. Change the query mentioned above to a delete query and delete the records.
    4. Using your new DupRecs table, create a query with this table and change the query properties to unique values.
    5. Append the unique records back into your main table (I can't remember though if you need to create another table first or you can just do an append query (on unique records in conjunction with the Find Duplicates query) back to your main table - I think you can set it to unique records and also append back to your main table). Otherwise, if you have to, do a make-table and make another table of unique records and then simply append that table back to your main table.

    If you had a lot of linked tables to your main table with duplicates, you're in for some fun and don't do steps 3-5 above yet. For example Mary Smith might be duplicated with a primary key (or autonumber) of 35 and 40. The question is (in the other relational tables), which number do you keep. Some people may have entered some information under Mary Smith's id of 35 and others may have entered information under the id of 40.

    If this is the case, let us know and I'll go further into explanation of some ways you can handle this - it can get pretty tough if it's not done right.
    Last edited by pkstormy; 12-19-06 at 15:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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