Results 1 to 5 of 5

Thread: [RESOLVED] how do i detect same phone number or same names in the databae?

  1. #1

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

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

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    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

  3. #3
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    759

    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.

  4. #4

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: how do i detect same phone number or same names in the databae?

    Quote Originally Posted by ChrisE View Post
    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

  5. #5

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: how do i detect same phone number or same names in the databae?

    Quote Originally Posted by Phill.W View Post
    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
  •  



Click Here to Expand Forum to Full Width