Eliminate duplicates

Results 1 to 3 of 3

Thread: Eliminate duplicates

  1. #1
    Join Date
    Dec 1969

    Default Eliminate duplicates

    Hi,<BR><BR>I have an access table that contains user details. <BR><BR>Many have submitted more than once.<BR><BR>I need to eliminate those additional submissions retaining only one set of details for each user.<BR><BR>Unique fields are telephone number and email address.<BR><BR>Can anyone please help..? I know this should be easy but i can&#039;t get my head round it.<BR><BR>Thanks in advance,<BR><BR>Kes

  2. #2
    Join Date
    Dec 1969

    Default RE: Eliminate duplicates

    I&#039;m not convinced Access SQL is up to this, with its ropey subquery support. here&#039;s some T-SQL which would do it...<BR><BR>SET QUOTED_IDENTIFIER ON <BR>GO<BR>SET ANSI_NULLS ON <BR>GO<BR><BR>CREATE PROCEDURE [dbo].[spDuplicateEmails] <BR>AS<BR>SELECT msID, msEmail, msFirstName, msSurname FROM tblMemberStaff WHERE msEmail IN (<BR>SELECT msEmail --, count(*) as instances<BR>FROM tblMemberStaff<BR>GROUP BY msEmail<BR>HAVING count(*) &#062; 1<BR>)<BR><BR>GO<BR>SET QUOTED_IDENTIFIER OFF <BR>GO<BR>SET ANSI_NULLS ON <BR>GO<BR><BR>ripped right out of one of my DBs. it selects, but you could use that to delete equally.<BR><BR>alternatively you could just SELECT DISTINCT<BR><BR><BR>j

  3. #3
    Join Date
    Dec 1969

    Default ASPFAQs, category Databases, Queries

    There is a link to the ASPFAQs at the top right corner of this very page.<BR><BR>Look in the category noted in the subject line of this message for your question.<BR><BR>The ASPFAQs are your friends. Visit your friends often.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts