Finding Duplicates

[cranebill]

Ok I am looking for a way to find duplicat phone numbers in a Table....

This SQL was generated By Access and works to find the full number (area code etc):

SELECT [Customer Information Table].Phone, [Customer Information Table].[Customer ID], [Customer Information Table].[Customer Name]
FROM [Customer Information Table]
WHERE ((([Customer Information Table].Phone) In (SELECT [Phone] FROM [Customer Information Table] As Tmp GROUP BY [Phone] HAVING Count(*)>1 )))
ORDER BY [Customer Information Table].Phone;


I have been playing with it to try and pull up duplicated of the last 7 numbers( no area code, trying to find duplicate numbers because of area code changes)

I tried usint Right(Phone,7) in various spots but then i get an aggregate function error. Any Ideas?

Bill

 

[adamroof]

Have you tried the Find Duplicates Wizard?

Click on Queries, then click New, should pop up with that option above Find Unmatched Query Wizard.

oops, just read your problem again....

are your dashes stored in your table?
If so, i have a quick form that can remove REALLY fast! Parenthesis' and dashes!

 

[cranebill]

No its formatted for phone numbers and only has numbers... no symbols

Bill

 

[MinusM]

Try replacing this part of the SQL

WHERE ((([Customer Information Table].Phone) In (SELECT [Phone] FROM [Customer Information Table] As Tmp GROUP BY [Phone] HAVING Count(*)>1 )))


By


WHERE ((Right([Customer Information Table].Phone),7) In (SELECT Right([Phone],7) FROM [Customer Information Table] As Tmp GROUP BY Right([Phone],7) HAVING Count(*)>1 )))


Best of luck