Re: Selecting the last record (2nd) of a duplicate record and moving

Tech-Archive recommends: Speed Up your PC by fixing your registry

Kathie G via AccessMonster.com wrote:

Hello,
I have an issue where I am fixing a poorly designed db.  (It is still a non-
relational db, but I do not have time to correct that!)  Anyways, the problem
I am trying to address is the "developer" did not build any control in for
duplicate records, thus, duplicate records are in the table.  I need to have
the 2nd duplicate record of the set removed and moved to another table so I
can have the payments for those services reclaimed. :(  Any ideas?  I do have
"date added" so could I do a query on a count and date and take the latter
date and work it that way?

Or you could add a new Autonumber field to make the records unique. Run the "Find Duplicates" wizard to locate all the duplicate records. Of a set of duplicates, how do you determine which get moved and which remain in the original Table? I think I'd add a Yes/No (Boolean) field temporarily to the Table to identify those records which I planned to move, then check all but one of each set of duplicates. Having identified them, I could move all the checked ones to the other Table. (Or copy the original Table and, in one copy, delete the checked records and in the other copy delete the unchecked ones. It would take maybe 2 minutes, probably would be faster than writing an Append Query.) You could then delete the Yes/No field and possibly the new Autonumber field.

Another thought: You'd wind up with two Tables with similar data, not a great database design. Would it be possible to add a [Status] field that you could use to indicate that a record represented services to be reclaimed? You could generate a list any time you wished, via a Query.

  -- Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx>
  Please feel free to quote anything I say here.
.

Relevant Pages

  • Re: Identifying Duplicate Records
    ... response to what appeared to be a barely relevant question. ... in the list of options is Find Duplicates query. ... or bring up the dupe records to look at them. ... have a find duplicate records wizard. ...
    (microsoft.public.access.formscoding)
  • Re: Identifying Duplicate Records
    ... Indeed, the duplicate records ... Discovering the Find Duplicate query, ... or bring up the dupe records to look at them. ... have a find duplicate records wizard. ...
    (microsoft.public.access.formscoding)
  • Re: Identifying Duplicate Records
    ... the duplicate records problem represented one of the few occasions ... the Find Duplicate query, however, has allowed me to plug another gap in my ... or bring up the dupe records to look at them. ...
    (microsoft.public.access.formscoding)
  • Re: Identifying Duplicate Records
    ... Whatever you use, you then can use the results to flag the dupes for review in one way or another, or bring up the dupe records to look at them. ... This is a tricky little query to understand, from a beginner to intermediate point of view. ... To find our duplicate records we build a query inside the IN function that will return only those values that occur more than once in the table. ... then comparing the results but the details of ...
    (microsoft.public.access.formscoding)
  • Re: Deleting duplicate records
    ... FROM Vessel X WHERE A.Vessel=X.Vessel AND ... It is suggested by Roger Carlson http://www.rogersaccesslibrary.com who is ... You will see full explanation of how to eliminate duplicate records ... > duplicate records where have created Totals query to find Distinct Records ...
    (microsoft.public.access.queries)