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

[Kathie G via AccessMonster.com]

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?

 

[Kathie G via AccessMonster.com]

Yeaup..you are probably right in all accounts. However, I do not have time
to reassess the db design presently. Note, I could redesign this doing many
things differently. The person who did this did not know what "relational"
meant at all. What the system is doing is importing *.csv files, then a
"validation of data" process occurs (basically in SQL instead of an Export
Mapping Table) so once the if this then make it this, the "invalid" records
are marked as "flagged" and the "valid" records are moved to the historical
table. Once there, I am now running a process that evaluates the records in
this table. If they are duplicate (and billed as if they are sitting in this
table they were also marked as "billed" during the last months billing) then
they are flagged and removed from that table and appended to the "flagged
error" table with comments of duplicate record - already billed. So I
think I will take your suggestion and copy the table and add a field for
"Y/N" and indicate the latter dups that way and delete the "true" records so
the fiscal peeps can at least report this to the agencies and recoop the
payments made for the duplicated records.

Thanks Vincent!

Hello,
I have an issue where I am fixing a poorly designed db. (It is still a non-

[quoted text clipped - 5 lines]

"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 <[email protected]>
Please feel free to quote anything I say here.