Welcome to SPN

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

Sign Up Now!

Duplicates

Discussion in 'Information Technology' started by michael_parry2005@hotmail.co.uk, Jul 28, 2006.

Tags:
  1. michael_parry2005@hotmail.co.uk

    Guest

    Hi

    I am creating a music database and have imported data (csv) from
    another source and it has imputted creatly without fault, however i
    would like to remove the duplicate artists eg. i've got two Abba, but
    i've tried different ways with no success.

    Thanks for your time
     
  2. ChrisM

    ChrisM
    Expand Collapse
    Guest

    Is there a unique Id on each row? If not, add a Autonumber Field which will
    give you one.

    Then somthing like:

    DELETE FROM myTable as outerLoop
    WHERE EXISTS
    (
    SELECT * FROM myTable as innerLoop
    WHERE innerLoop.ArtistName = outerLoop.ArtistName
    AND innerLoop.UniqueId <> outerLoop.UniqueId
    );


    EXPERIMENT ON A BACKUP COPY OF THE DATABASE!!!


    Cheers,

    Chris.

    <michael_parry2005@hotmail.co.uk> wrote in message
    news:1152785167.673351.149030@75g2000cwc.googlegroups.com...
    > Hi
    >
    > I am creating a music database and have imported data (csv) from
    > another source and it has imputted creatly without fault, however i
    > would like to remove the duplicate artists eg. i've got two Abba, but
    > i've tried different ways with no success.
    >
    > Thanks for your time
    >
     
  3. michael_parry2005@hotmail.co.uk

    Guest

    Thanks for this reply it's much appricated. I don't have much access
    knowledge so where would i put this expression.

    Thanks very very much
     
  4. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    ChrisM wrote:
    > > I am creating a music database and have imported data (csv) from
    > > another source and it has imputted creatly without fault, however i
    > > would like to remove the duplicate artists eg. i've got two Abba

    >
    > Is there a unique Id on each row? If not, add a Autonumber Field which will
    > give you one.


    Huh? The OP seems to be saying they want to key the data on artist
    name; surely that's the column for the UNIQUE constraint? Adding a
    unique integer and coonstraining it as UNIQUE will only facilitate the
    duplication of artists' names.

    Jamie.

    --
     
  5. ChrisM

    ChrisM
    Expand Collapse
    Guest

    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1152791429.898649.31940@m73g2000cwd.googlegroups.com...
    >
    > ChrisM wrote:
    >> > I am creating a music database and have imported data (csv) from
    >> > another source and it has imputted creatly without fault, however i
    >> > would like to remove the duplicate artists eg. i've got two Abba

    >>
    >> Is there a unique Id on each row? If not, add a Autonumber Field which
    >> will
    >> give you one.

    >
    > Huh? The OP seems to be saying they want to key the data on artist
    > name; surely that's the column for the UNIQUE constraint? Adding a
    > unique integer and coonstraining it as UNIQUE will only facilitate the
    > duplication of artists' names.
    >
    > Jamie.
    >
    > --


    Yea, but it seemed to me the best simplest way to remove the duplicates.
    Once that has been accomplished, the AutoNumber field could be removed, and
    the ArtistName made the primary key to stop further duplication.

    Do you have a better method for initially removing the duplicates? I'm not
    being sarcastic, I'm genuinely interested as to if there is a better way...

    Cheers,

    Chris.
     
  6. ChrisM

    ChrisM
    Expand Collapse
    Guest

    You would need to create a new query based on your 'Artist' table. This
    would go into the SQL view of the query.
    You will also need to change the field names and the table name to suit your
    actual data.

    You really should read the Access help on creating queries before you go
    much further. It will make your life ever so much easier ongoing... :)

    Cheers,
    Chris.

    <michael_parry2005@hotmail.co.uk> wrote in message
    news:1152788899.408198.144270@75g2000cwc.googlegroups.com...
    >
    >
    > Thanks for this reply it's much appricated. I don't have much access
    > knowledge so where would i put this expression.
    >
    > Thanks very very much
    >
     
  7. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    ChrisM wrote:
    > > The OP seems to be saying they want to key the data on artist
    > > name; surely that's the column for the UNIQUE constraint? Adding a
    > > unique integer and coonstraining it as UNIQUE will only facilitate the
    > > duplication of artists' names.

    >
    > Yea, but it seemed to me the best simplest way to remove the duplicates.
    > Once that has been accomplished, the AutoNumber field could be removed, and
    > the ArtistName made the primary key to stop further duplication.


    Agreed.

    Jamie.

    --
     

Share This Page