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

[sandi]

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

[sandi]

hi there

could you tell me how to eliminate the duplicate data in the MS ACCESS

Sandi

[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:

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

[sandi]

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

[PEB]

Change it to:

SELECT PNo , LastName , Count(*) GROUP BY PNo , LastName WHERE Count(*)>1 ;