Re: [MS_AccessPros] How to separate duplicates.

 

Robin

A.D. Tejpal, our retired friend in India came up with this one years ago. You have to match up all the fields that indicate a duplicate record. The lowest ID is kept as the "good" record.

DELETE *  

FROM T_Data  

WHERE (SELECT  Count(*) 

       FROM T_Data AS T  

       WHERE Nz(T.F1, "") =  Nz(T_Data.F1, "") 

       AND Nz(T.F2, "") =  Nz(T_Data.F2, "") 

       AND Nz(T.F3, "") =  Nz(T_Data.F3, "") 

       AND Nz(T.F4, "") =  Nz(T_Data.F4, "")  

       AND T.ID <= T_Data.ID) > 1;

Bill Mosca

__._,_.___

---

Posted by: wrmosca@comcast.net

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (12)

Visit Your Group

• New Members 3

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___