Deleting Duplicate Records, Keeping First Occurance

[MJ]

My problem is from a list of records of transactions where I have a number of
duplicates throught out any given month. I have devised a way to sort the
dups out into a separate table (containing ALL of the dups for that month),
grouped and sorted in the order I want to see them as dup. Now I need to
weed out all dups EXCEPT the first one of each different

tblDUPS
FC AT Enc Age AcctBal Hold
* A DUP 4960002 0-30 $1,514.75 BDH
A DUP 4960002 0-30 $1,514.75 BDH
A DUP 4960002 0-30 $1,514.75 IVH
* C DUP 5000000 365+ ($160.48) BDH
C DUP 5000000 365+ ($160.48) No Charges
C DUP 5000000 365+ ($160.48) No Charges
* B DUP 5210002 0-30 $51.14 BDH
B DUP 5210002 0-30 $51.14 BDH
B DUP 5210002 0-30 $51.14 IVH

In the table above, duplicates are define by fields: FC; Enc; Age; &
AcctBal. I want to keep the first occurance (*'d) and I want to delete the
others. The resulting list should look like:

tblDUPS
FC AT Enc Age AcctBal Hold
* A DUP 4960002 0-30 $1,514.75 BDH
* C DUP 5000000 365+ ($160.48) BDH
* B DUP 5210002 0-30 $51.14 BDH

How can I do this efficiently?

Thank you in advance for your insight and assistance.

 

[bcap]

If they were exact dupes then you could simply SELECT DISTINCT them into
another table. But they are not exact dupes, because the "Hold" field
varies, so on what basis are you deciding which is the 1st record in each
group? I could hazard a guess from your sample data that the one you keep
must have a value of "BDH" in the Hold field, but it's only a guess.

If the guess is correct then:

SELECT DISTINCT * FROM tblDUPS WHERE Hold = "BDH"

Otherwise, more information needed!

 

[MJ]

Bcap,

Actually it doesn't matter to me what is in the "Hold" field, but ALL of the
resulting data will be appended back into the original source table where is
came from for reporting purposes.

In reality, when constructing the tblDUPS from tblOrig, the DUPs data is
grouped by Enc, AcctBal and sorted ASC by Hold. I hope this helps clarify
the information?

--

MJ

If they were exact dupes then you could simply SELECT DISTINCT them into
another table. But they are not exact dupes, because the "Hold" field
varies, so on what basis are you deciding which is the 1st record in each
group? I could hazard a guess from your sample data that the one you keep
must have a value of "BDH" in the Hold field, but it's only a guess.

If the guess is correct then:

SELECT DISTINCT * FROM tblDUPS WHERE Hold = "BDH"

Otherwise, more information needed!

 

[bcap]

I believe, then, that a query such as this would do the trick:

SELECT FC, AT, Enc, Age, AcctBal, Min(Hold) AS Hold FROM tblDUPS GROUP BY
FC, AT, Enc, Age, AcctBal

If you want the results to go into an existing table then you can wrap this
up in an INSERT query:

INSERT INTO tblOrig SELECT FC, AT, Enc, Age, AcctBal, Min(Hold) AS Hold FROM
tblDUPS GROUP BY FC, AT, Enc, Age, AcctBal

Bcap,

Actually it doesn't matter to me what is in the "Hold" field, but ALL of
the
resulting data will be appended back into the original source table where
is
came from for reporting purposes.

In reality, when constructing the tblDUPS from tblOrig, the DUPs data is
grouped by Enc, AcctBal and sorted ASC by Hold. I hope this helps clarify
the information?