SQL Delete duplicates problem - Access 2K

[salvelinus]

This should be easy, but I just can't get it to work right.
I have a table that contains records with duplicate phone numbers (data supplied by client). I only want one record per phone number.
I can delete duplicates by a hierarchy ranking, but some have the same rank. So next I delete based on keeping the most recent date. Ok, but there are still some duplicates with the same date. At this point, I figure I can just delete based on their autonumber ID field - doesn't matter if I keep the highest or lowest, so long as I'm left with just one.
This is what I can't get. I'd post some attempts, but not at work now. The odd thing is, if I filter the table by phone number at this point, I get only one record. Ok, that might throw an error. But if a run a find duplicates query based on an Access wizard, I get 406 records (not all same phone, but at least two for any individual number).
Any ideas? Thanks.

[kxcntry99]

Just to clarify if you sort the table by phone number you get no dups. But if you run this find dups query you get 406 dups...correct ???

what does the query look like exactly?

[RhinoBull]

There are quite of few different technics exist.
Take a look at How to delete duplicate records using sql?

[salvelinus]

kx - that is correct. What I should do is check the values in the code query I run against the wizard query, although I copied the wizard query almost verbatim at one point.
Rhino - I will look at those at work tomorrow, thanks. Like I say, I don't want to delete all records with duplicate phone numbers - I need to keep one per number. This query just needs to delete all duplicates that haven't already been eliminated by other methods, leaving one for each remaining at that point duplicated phone number. It really doesn't matter which when deletes reach this stage.
Thanks, both.

[kxcntry99]

Yes check what query the wizard is running. Especially since it sounds as if you view the raw table you see no dups. But if you run the wizard it finds dups...I just wonder where it is pulling from to get the dups

Then again so are you

[salvelinus]

kx - The wizard query is just the query Access makes, based on fldPhone. I used that query verbatim in code for a recordset. But when it runs, it errors out on the first attempt at deleting a record (a separate sql statement in a loop through the recordset). I check the values in the recordset at that point - phone number and id - in the table, and only get one record filtering on either. What I need to do is then run the wizard created query & check for those values there. Since it's the same query, I have no idea why one would have duplicates but not the other.
Rhino - I've checked some of the Google results. Most of them seem to deal with SQL Server in a way I can't easily translate to Access, but there are some I plan to try.
Thanks again, both.

[kxcntry99]

Its a little unorthadox and may not be applicipable in your case but I have had great success with a create table query and a bunch of group by's to combine the dups into one record and then insert the one record into a new table.

Again it doesn't always work depending on the situation.

[Ecniv]

Usually it is a pain in the butt to delete duplicates.
Flagging them however is much easier

You can (I think) use an update query and a field to flag the dulpicate.
Question is do you want to flag the original / first as well as the dups or just the dups (newer records)?


One way.

UPDATE <table>
SET <dupflag> = true
WHERE <id> in (
-- sub query goes here
)

The sub query needs to return just the ids you are interested in. If you are flagging any dup you just need to

SELECT <table>.<id>
FROM <table> INNER JOIN (
    SELECT <phoneno>, count(<phoneno>) 
    FROM <Table> 
    GROUP BY <phoneno> 
    HAVING Count(<phoneno>) >1) as sq
ON <table>.<phoneno> = sq.phoneno

<table> is the tablename (surround with square brackets)
<phoneno> is the phonenumber field name (surround with square brackets)
sq is the alias for the sub-sub query.

The above flags all duplicated rows.
If you only want those that aren't the first record you need to get creative with subqueries
Or run in two steps. First one above to get the records.
Second one to update those highlighted back to false if they are the first record for a common field (ie customerid).

Another route is via code; sort the data and loop through. If you have one (or more) fields that are key and haven't changed, then flag the record as a duplicate.

Your choice if you want to delete it then, or just add filters in your queries to remove them.