By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 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
Share this Question
Share on Google+
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

PEB
Expert 100+
P: 1,418
PEB
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
  1. SELECT DISTINCT  PNo , LastName , Count(*) WHERE Count(*)>1;
  2.  
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

PEB
Expert 100+
P: 1,418
PEB
Change it to:

SELECT PNo , LastName , Count(*) GROUP BY PNo , LastName WHERE Count(*)>1 ;
Sep 26 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.