Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove Duplicates (2000)

    I'm trying to find a way to remove the duplicates from a DB. The table in question does have an autonumber to differentiate between duplicates, but I can't seem to find a way of just selecting one of a pair. I'm attaching a cut down table of duplicates for anyone who'd like to have a go at.
    TIA
    Paul
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Remove Duplicates (2000)

    Are HI_Date and HI_PK included in the duplicate test? I would doubt it.

    Try this:
    SELECT HistoryDupes.HI_IDENT, HistoryDupes.HI_OPERATOR, HistoryDupes.HI_DATE, HistoryDupes.HI_TIME, HistoryDupes.HI_FAULT, HistoryDupes.HI_HRSLOG, HistoryDupes.HI_HRSUSED, Max(HistoryDupes.HI_PK) AS MaxOfHI_PK
    FROM HistoryDupes
    GROUP BY HistoryDupes.HI_IDENT, HistoryDupes.HI_OPERATOR, HistoryDupes.HI_DATE, HistoryDupes.HI_TIME, HistoryDupes.HI_FAULT, HistoryDupes.HI_HRSLOG, HistoryDupes.HI_HRSUSED


    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Duplicates (2000)

    When I ran into the same problem recently, the concensus that I received was it was much easier to prevent duplicates in the first place than to remove them automatically. So... if it's possible within your situation to copy the records into a new table, you can create a "no duplicates" index on the appropriate columns in the new table. Then just make an append query into the new table. The "no duplicate" index will prevent the duplicate records from being appended.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  4. #4
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Duplicates (2000)

    Got it! I thought I'd tried the 'max' on the HI_PAK field, but obviously not. It's still a bit of a work around to get it all doe properly, but I'll do a macro or something to automate it a bit [img]/forums/images/smilies/smile.gif[/img]

    For reference, this is what I ended up doing;
    1. Created a bog standard find duplicates query, with the HI_PAK field (an auto-number one) just displaying.
    2. Changed it to a make table query & run it.
    3. Changed it again to an Append query & saved it.
    4. Changed it once more to Delete query, run it & did a save as to a new name.
    5. Used Pats' SQL, as an Append query, to find the single records, & pop them into the original table.
    6. Created a Delete query to empty the new table.

    Now every week, month or whatever, I'll run it to clear things out.

    Cheers gents [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

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