Duplicates

[michael_parry2005]

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

 

[ChrisM]

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]

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

 

[ChrisM]

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.

 

[ChrisM]

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.