-
Jun 27th, 2017, 05:20 AM
#1
Thread Starter
Enjoy the moment
[RESOLVED] how do i detect same phone number or same names in the databae?
hey,
i have a customer database that has duplicates phone numbers and duplicates name
is there a way to detect duplicates?
regards
salsa31
-
Jun 27th, 2017, 05:49 AM
#2
Re: how do i detect same phone number or same names in the databae?
Hello salsa,
open the Northwind Database and add this query
Code:
SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, *
FROM Employees
WHERE (((Employees.LastName) In (SELECT [Lastname] FROM [Employees] As Tmp
GROUP BY [Lastname],[Firstname]
HAVING Count(*)>1 And
[Lastname] = [Employees].[Lastname])));
just add in the Employees table a duplicate for checking the query works
regards
Chris
-
Jun 27th, 2017, 06:25 AM
#3
Re: how do i detect same phone number or same names in the databae?
To detect this duplication, use ChrisE's sample.
To prevent it happening again, create a Unique Index on the field in question.
(After finding and removing any duplicates, which will prevent the creation of said index ).
Regards, Phill W.
-
Jun 27th, 2017, 07:15 AM
#4
Thread Starter
Enjoy the moment
Re: how do i detect same phone number or same names in the databae?
Originally Posted by ChrisE
Hello salsa,
open the Northwind Database and add this query
Code:
SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, *
FROM Employees
WHERE (((Employees.LastName) In (SELECT [Lastname] FROM [Employees] As Tmp
GROUP BY [Lastname],[Firstname]
HAVING Count(*)>1 And
[Lastname] = [Employees].[Lastname])));
just add in the Employees table a duplicate for checking the query works
regards
Chris
Bingo!!!!!!
thank you ChrisE
-
Jun 27th, 2017, 07:16 AM
#5
Thread Starter
Enjoy the moment
Re: how do i detect same phone number or same names in the databae?
Originally Posted by Phill.W
To detect this duplication, use ChrisE's sample.
To prevent it happening again, create a Unique Index on the field in question.
(After finding and removing any duplicates, which will prevent the creation of said index ).
Regards, Phill W.
for sure ,tnx
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|