Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

ELIMINATE RECORDS WITH ONLY ONE FIELD DUPLICATED?

Discussion in 'Information Technology' started by CUTLIPCONSULTING, Nov 1, 2005.

  1. CUTLIPCONSULTING

    CUTLIPCONSULTING
    Expand Collapse
    Guest

    I HAVE A TABLE I HAVE SORTED BY FIELD 6 - CLAIM # (WHICH HAS SOME DUPLICATE
    INFO) AND FURTHER SORTED BY FIELD 16 - DATE (OLDEST FIRST). ALL OTHER FIELDS
    ARE DIFFERENT FROM ONE ANOTHER. I WANT TO ELIMINATE THOSE RECORDS WHERE FIELD
    6 CONTAINS THE SAME NUMBER AS ANOTHER RECORD BUT WHERE THE FIELD 16 DATE IS
    EARLIER THAN THE OLDEST DATE. IN DOING THIS, I WANT TO SEE ALL THE DATA IN
    THE OTHER FIELDS WHEN I AM THROUGH.

    FOR EXAMPLE, THERE ARE 3 RECORDS WITH A FIELD 6 CLAIM # OF 1234. ONE HAS A
    DATE OF 12-01-05, ONE HAS A DATE OF 11-01-05, AND THE THIRD HAS A DATE OF
    10-01-05. I WANT TO ELIMINATE THE RECORDS WITH THE 10 AND 11 DATES AND KEEP
    ONLY THE 12-01-05 RECORD. I ALSO WANT TO SEE THE DATA IN THE OTHER 28 FIELDS
    I HAVE WHEN I'M LEFT WITH THE 12-01-05 RECORD.

    THE DUPLICATE QUERY SEEMS TO ELIMINATE ONLY RECORDS WHERE ALL 28 FIELDS ARE
    THE SAME. THAT WON'T SOLVE MY PROBLEM.
     
  2. Loading...

    Similar Threads Forum Date
    Canada New Canadian Act Aimed to Eliminate 'Ghost' Immigration Agents in Punjab Breaking News Dec 10, 2010
    Legal Campaign seeks to eliminate religious discrimination in workplace Breaking News Apr 11, 2010
    Sikh News Punjab govt determined to eliminate child labour: Minister (Outlook India) Breaking News Nov 14, 2007
    Such a dance eliminates hell Gurmat Vichaar Aug 2, 2006
    Eliminate self-conceit ! Interfaith Dialogues Sep 1, 2005

  3. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    You'll need to stack your queries. The first query needs to check for
    duplicates based on Claim#, sorted by date DESC, using the MAX value grouped
    by Claim #. The second will add the rest of the fields linking on both of the
    values (MAX Date and Claim #) returned by the first.
    --
    Arvin Meyer, MCP, MVP
    Microsoft Access
    Free Access downloads
    http://www.datastrat.com
    http://www.mvps.org/access


    "CUTLIPCONSULTING" wrote:

    > I HAVE A TABLE I HAVE SORTED BY FIELD 6 - CLAIM # (WHICH HAS SOME DUPLICATE
    > INFO) AND FURTHER SORTED BY FIELD 16 - DATE (OLDEST FIRST). ALL OTHER FIELDS
    > ARE DIFFERENT FROM ONE ANOTHER. I WANT TO ELIMINATE THOSE RECORDS WHERE FIELD
    > 6 CONTAINS THE SAME NUMBER AS ANOTHER RECORD BUT WHERE THE FIELD 16 DATE IS
    > EARLIER THAN THE OLDEST DATE. IN DOING THIS, I WANT TO SEE ALL THE DATA IN
    > THE OTHER FIELDS WHEN I AM THROUGH.
    >
    > FOR EXAMPLE, THERE ARE 3 RECORDS WITH A FIELD 6 CLAIM # OF 1234. ONE HAS A
    > DATE OF 12-01-05, ONE HAS A DATE OF 11-01-05, AND THE THIRD HAS A DATE OF
    > 10-01-05. I WANT TO ELIMINATE THE RECORDS WITH THE 10 AND 11 DATES AND KEEP
    > ONLY THE 12-01-05 RECORD. I ALSO WANT TO SEE THE DATA IN THE OTHER 28 FIELDS
    > I HAVE WHEN I'M LEFT WITH THE 12-01-05 RECORD.
    >
    > THE DUPLICATE QUERY SEEMS TO ELIMINATE ONLY RECORDS WHERE ALL 28 FIELDS ARE
    > THE SAME. THAT WON'T SOLVE MY PROBLEM.
     

Share This Page