
April 9th, 2004, 10:50 AM
|
|
Registered User
|
|
Join Date: Aug 2003
Posts: 2
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
Looking for Duplicates
I have a situation where I have the following fields in a database:
OrgName
AreaCode
Phone
The problem is, there may be some entries where the same organization was entered, but was entered with a different "OrgName", although the phone number is the same.
I did the following:
SELECT Phone, COUNT(OrgName) AS TotalRecords
FROM tProvider
GROUP BY Phone
In a Query which got me a list of all the organizations and the number of entries they have. This works ok, because then I can do a SELECT off of this query for all where the TotalRecords > 1.
HOWEVER, I noticed that some of these came up as duplicates, although the Area Code is actually different. SO, how do I put the Area Code and Phone number into one "variable" that I can then group by? I tried doing:
SELECT AreaCode + ' ' + Phone AS PhoneNumber, COUNT(OrgName) AS TotalRecords
FROM tProvider
GROUP BY PhoneNumber
But this doesn't work? Any ideas? Do I have to use two seperate queries, or can I write this as one?
Thanks!
-Grimps
|