Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Posts
    13

    Unanswered: DELETE QUERY (Eliminate duplicates)

    Hi Everybody,

    I hope somebody can help me...as I have beating my head and I do not know how to do it:-(


    I deleted duplicates eliminating each earlier record in DATE OF QUESTION field with the following query which works:

    DELETE makepart1.[MEMBER ID], makepart1.[DATE OF QUESTION], makepart1.[DATE OF ACTION]
    FROM makepart1
    WHERE MAKEPART1.[Date of Question] Is Not Null AND
    CLng(Nz(MAKEPART1.[Date of Question],0))<(SELECT Max(CLng(Nz([makepart2].[Date of Question],0))) FROM MAKEPART2 WHERE MAKEPART2.[MEMBER ID]=MAKEPART1.[MEMBER ID]) ;

    Keeping the above criteria I wold like to go to the next step: eliminating duplicate
    1.For those who has equal value of DATE OF QUESTION (as the result of the above query) I should delete NULL duplicate record under DATE OF ACTION .

    2. After that For those ones who have values equal under BOTH headings I should delete anyone anyway (for ex, for id 44 I have to delete no matter 1st or 2nd record )

    Member id date of question date of action
    88 3/30/2003 3/30/2003
    88 3/30/2003
    66 1/11/2004
    66 1/11/2004 1/11/2004
    33 2/1/2004 2/1/2004
    33 2/1/2004
    44 4/2/2004 4/2/2004
    44 4/2/2004 4/2/2004

    The output should be:
    Member id date of question date of action
    88 3/30/2003 3/30/2003
    66 1/11/2004 1/11/2004
    33 2/1/2004 2/1/2004
    44 4/2/2004 4/2/2004

    I assumed I just should add next criteria at the end of my former query (AND MAKEPART1.[Date of ACTION] Is Null) to do the 1st part as I did below

    DELETE makepart1.[MEMBER ID], makepart1.[DATE OF QUESTION], makepart1.[DATE OF ACTION]
    FROM makepart1
    WHERE MAKEPART1.[Date of Question] Is Not Null AND
    CLng(Nz(MAKEPART1.[Date of Question],0))<(SELECT Max(CLng(Nz([makepart2].[Date of Question],0))) FROM MAKEPART2 WHERE MAKEPART2.[MEMBER ID]=MAKEPART1.[MEMBER ID]) AND MAKEPART1.[Date of ACTION] Is Null;

    But system give me a message saying :

    Enter Parameter value


    Makepart1.DATE OF ACTION which means I did it wrong!

    How can I do it?

    Any help would be greately appreciated

    Katrin

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    The easiest way to do this is with a make table query. Simply put your table in a design grid, and double-click each field in the displayed table to put it in the grid. Click on the "epsilon" (Greek 'E') to make it a totals query, which ignores duplicate records. To add another level of confidence, click anywhere on the top of the grid, and open the "View" menu and select "Properties" to display the Property Sheet. Change the Unique Records entry to "Yes". Then run the query. It should delete all duplicate records for you.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well...instead of deleting, I would identify what I wanted to keep, the create a new table, rename the old one as a backup, then name the new one as the old tables name

    Just make your final select using DISTINCT
    Attached Thumbnails Attached Thumbnails Duplicates.bmp  
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Quote Originally Posted by Sam Landy
    The easiest way to do this is with a make table query. Simply put your table in a design grid, and double-click each field in the displayed table to put it in the grid. Click on the "epsilon" (Greek 'E') to make it a totals query, which ignores duplicate records. To add another level of confidence, click anywhere on the top of the grid, and open the "View" menu and select "Properties" to display the Property Sheet. Change the Unique Records entry to "Yes". Then run the query. It should delete all duplicate records for you.
    As a side note, the 'Totals' toolbar button has a sigma (S), which is a Greek 'S', which makes sense when you consider that it is the toolbar button icon in Excel to Sum.

    TD

  5. #5
    Join Date
    Jan 2005
    Posts
    13

    Delete Query to eliminate duplicates (what option of Total ?)

    i tried to use sigma E as well as Unique values=y but it doesn't work.

    I wonder if it happend because One should use count or WHERE instead Group By by default?
    Or
    Should I leave GROUP BY at all fields except DATE of Action?

  6. #6
    Join Date
    Jan 2005
    Posts
    13

    Unhappy Duplicates part ...CountOfActionDate: count(*)

    I also tried Brett solution and I am confused doing Duplicates part ...CountOfActionDate: count(*)

    I am not sure How can I buid this expression? Let's say the real field name consists of couple of words like DATE OF ACTION.

    Should I put countOf DATE OF ACTION: count(*)?

    I wonder how system will know it refers to the certain field (DATE OF ACTION)? Or probably I misunderstood the idea? Actuall I tried to put:

    field ATE OF ACTION

    total:count

    criteria:>1

    In such a case system just give me one record (which is not correct)
    I am so confused? Could you please clarify this part?

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by katrin_king
    i tried to use sigma E as well as Unique values=y but it doesn't work.

    I wonder if it happend because One should use count or WHERE instead Group By by default?
    Or
    Should I leave GROUP BY at all fields except DATE of Action?
    Certainly, the GROUP BY is appropriate for all columns but the Date column. However, don't set the UNIQUE VALUES property to Yes, but the UNIQUE RECORDS property.

    Sam

  8. #8
    Join Date
    Jan 2005
    Posts
    13
    And What option should be under Date column?

Posting Permissions

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