435,156 Members | 1,069 Online
Need help? Post your question and get tips & solutions from a community of 435,156 IT Pros & Developers. It's quick & easy.

# how to eliminate the duplicate data which is depend on the text

 P: 5 hi there i have fields name called .. PAF (text) , PNo (Number ) , LastName (Text) 2006/214/2 , 220101 , Winne 2006/321/3, 521496 , Joe 2006/321/1 , 521496 , Joe 2006/541/4 , 521496 , Joe I want the record of 2006/241/2 , 220101 , Winne 2006/541/4 , 521496 , Joe. is anyone can tell me how to eliminate the dublicate record of Joe ... Thanks ! Sandi Sep 25 '06 #1
4 Replies

 P: 5 hi there could you tell me how to eliminate the duplicate data in the MS ACCESS Sandi Sep 25 '06 #2

 Expert 100+ P: 1,418 Hi, So to do that the algorithme is a bit long! 1) Create a column in your table! Name it to_delete and assign it from type Yes/No 2) Create Query with this SQL: Expand|Select|Wrap|Line Numbers SELECT DISTINCT  PNo , LastName , Count(*) WHERE Count(*)>1;   Name it Doubles 3) Create UPDATE Query join your table and the query Doubles by the 2 fields and set to_delete=yes 4) Create Update Query where to_delete=yes and PNo = dmin("PNo","YOURTable","") Set to_delete to No 5) Finnally create a delete query that deletes all the records where to_delete = yes! :) It's long strong and annoying! isn't it? :) Best regards! hi there i have fields name called .. PAF (text) , PNo (Number ) , LastName (Text) 2006/214/2 , 220101 , Winne 2006/321/3, 521496 , Joe 2006/321/1 , 521496 , Joe 2006/541/4 , 521496 , Joe I want the record of 2006/241/2 , 220101 , Winne 2006/541/4 , 521496 , Joe. is anyone can tell me how to eliminate the dublicate record of Joe ... Thanks ! Sandi Sep 25 '06 #3

 P: 5 Hi there When i write this code , SELECT DISTINCT PNo , LastName , Count(*) WHERE Count(*)>1; there is an error saying "You tried to execute a quey that does not inclide the specified expression "PNo " as part of an aggregate function" Thanks Sandi Sep 26 '06 #4

 Expert 100+ P: 1,418 Change it to: SELECT PNo , LastName , Count(*) GROUP BY PNo , LastName WHERE Count(*)>1 ; Sep 26 '06 #5