Help -Duplicates difficulty - Help

[Diggar]

I hope I can explain this well. I have a list of about 30K records that have come from combining 4 different lists.

What this means is that a company may have been on 1 or all 4 of the original lists. So depending on how many lists the company was on will be how many times it is duplicated in the new list.

So if I do sort on the complete list I may see some of these examples.

Ex. 1.)
ID Identifier Co Name Address1 City St
29 LM ABC 12 Elm Dewar DC
123 TG ABC 12 Elm Dewar DC
99 MC ABC 12 Elm Dewar DC
245 AD ABC 12 Elm Dewar DC

Ex 2.)
ID Identifier Co Name Address1 City St
29 LM ABC 12 Elm Dewar DC
123 TG ABC 12 Elm Dewar DC
99 MC ABC 12 Elm Dewar DC

Ex 3.)
ID Identifier Co Name Address1 City St
29 LM ABC 12 Elm Dewar DC
99 MC ABC 12 Elm Dewar DC
245 AD ABC 12 Elm Dewar DC

Ex 4.)
ID Identifier Co Name Address1 City St
123 TG ABC 12 Elm Dewar DC
99 MC ABC 12 Elm Dewar DC
245 AD ABC 12 Elm Dewar DC

Ex 5.)
ID Identifier Co Name Address1 City St
99 MC ABC 12 Elm Dewar DC
245 AD ABC 12 Elm Dewar DC


And so on.


So no matter how many are duped I want to delete the entire group IF the group has MC in it. There may be as little as 2 as in Ex 5 or as many as 4
as in Ex 1.

So I want to find a way to find all duplicates of any company in the complete list, then delete all duplicated GROUPS that have the MC as part of it.

I hope this helps.
Thanks

 

[Malchik]

You can use a query like:

SELECT DISTINCTROW First(ID) , First(Identifier), Count(ID) AS NumberOfDups
FROM tblReport
GROUP BY ID, Identifier
HAVING (((Count(ID))>1) AND ((Identifier))>1));

this will give you howmany record you have for each ID and Identifier if there is more than 1 of them Mal'chik

 

[Diggar]

Thanks Mal'chik

I am not sure where to apply this script. I can start a new query but where do I place your script?

Thanks