delete duplicates

[kikeman]

Hi, I have been trying to hard to delete some duplicates and I cannot,

I have two tables:

1.-nproyecto(does not have primary-key, error from last developer)
2.-Distict (query table)

The first has duplicated projects, each project has a "Nproy" that is a text
and that "Nproy" column has duplicates the other fields are the same.

the "Distict", I have managed to get rid of duplicates with the following
SQL command:

SELECT First(nproyecto.Nproy) AS FirstOfNproy, nproyecto.Nproy,
nproyecto.Nofe, nproyecto.desc
FROM nproyecto
GROUP BY nproyecto.Nproy, nproyecto.Nofe, nproyecto.desc;

But now that I want to create another Query table to delete the duplicates
it shows nothing

From the query design I have join from the field "Nproy" from "nproyecto"
table to "Nproy" from "Distict", and I used the following command just to
show the duplicates but it does not work:

DELETE nproyecto.*, nproyecto.Nproy
FROM nproyecto INNER JOIN [nproyecto - Distinct Records] AS [Distinct] ON
nproyecto.Nproy = Distinct.Nproy
WHERE (((nproyecto.Nproy)<>[Distinct].[FirstOfNproy]));

Any suggestions?

Thanks,

 

[Allen Browne]

Suggestions:

Option 1. Add an autonumber field, and mark it as primary key. You can now
distinguish between duplicates, and so you can select one to keep and remove
the other(s.) Here's how:
http://allenbrowne.com/subquery-01.html#DeDuplicate
If you are determined to violate the rules that require every table to have
a primary key, you can remove the autonumber again afterwards.

Option 2. Turn your query into a Make Table query.
Make a new table that contains the de-duplicated record.
Delete the original table.
Rename the new one to the old one.