Deleting Duplicate records

[Guest]

HELP! Back in July someone wrote the following to help someone to delete
duplicate records. Could soneone please explain in furhter detain how to do
this so I can try it? See below.


In another post I explained the below:

What I tried to do was create 10 primary keys on 10 different fields and
then took the original table to make a new one. I thought this would require
all 10 to match and make a new table. Well it did not.

INSERT INTO [BL-PRO ADJUSTMENTS NO DUPS]
SELECT [BL-PRO ADJUSTMENTS ALL].*
FROM [BL-PRO ADJUSTMENTS ALL];

When I run a duplicate query on the 10 fields the result is about 82,000
records, meaning half a duplicated of 41,000. When I did the above etc it has
over 94,000 less records. Obviosly, it is finding duplicates in each primary
Key I identified and giving those. I need only the exact same records for all
fields to be taken out ONLY.

Hopefully this explains what I tried to do.

 

[Smartin]

HELP! Back in July someone wrote the following to help someone to delete
duplicate records. Could soneone please explain in furhter detain how to do
this so I can try it? See below.


In another post I explained the below:

What I tried to do was create 10 primary keys on 10 different fields and
then took the original table to make a new one. I thought this would require
all 10 to match and make a new table. Well it did not.

INSERT INTO [BL-PRO ADJUSTMENTS NO DUPS]
SELECT [BL-PRO ADJUSTMENTS ALL].*
FROM [BL-PRO ADJUSTMENTS ALL];

When I run a duplicate query on the 10 fields the result is about 82,000
records, meaning half a duplicated of 41,000. When I did the above etc it has
over 94,000 less records. Obviosly, it is finding duplicates in each primary
Key I identified and giving those. I need only the exact same records for all
fields to be taken out ONLY.

Hopefully this explains what I tried to do.

Hi Tom,

Between the reply you copied and your result I'm not sure what occurred.
The SELECT DISTINCT suggestion should produce a table/query of unique
rows /as determined by the fields you specify/.

Creating additional key fields doesn't make a whole lot of sense here.
Indeed, if the keys are unique and you have duplicates in the table, the
key creation would fail.

Did you try the DISTINCT query?

 

[Guest]

No Do not know how. Did find some info about a query that shows only unique
records. Just now need to try and make it a table so I can run some queries
on the new table. Hope that is easy.
--
Tom

HELP! Back in July someone wrote the following to help someone to delete
duplicate records. Could soneone please explain in furhter detain how to do
this so I can try it? See below.

Hi,

Not sure what you mean by " I want to delete just the actual duplicate
(not
the original)." ? If they are duplicate records meaning a row is
repeated multiple times then there is no distinction to which one is
the original record.

But in any case, you can use the DISTINCT keyword to get each distinct
record in your table and insert the results into another table. The
result would give you a copy of your table without the duplicates.
Something along these lines....

SELECT DISTINCT Column1, Column2
INTO TableNoDups
FROM TableWithDups
GROUP BY Column1, Column2

Cheers!
- Lem

In another post I explained the below:

What I tried to do was create 10 primary keys on 10 different fields and
then took the original table to make a new one. I thought this would require
all 10 to match and make a new table. Well it did not.

INSERT INTO [BL-PRO ADJUSTMENTS NO DUPS]
SELECT [BL-PRO ADJUSTMENTS ALL].*
FROM [BL-PRO ADJUSTMENTS ALL];

When I run a duplicate query on the 10 fields the result is about 82,000
records, meaning half a duplicated of 41,000. When I did the above etc it has
over 94,000 less records. Obviosly, it is finding duplicates in each primary
Key I identified and giving those. I need only the exact same records for all
fields to be taken out ONLY.

Hopefully this explains what I tried to do.

Hi Tom,

Between the reply you copied and your result I'm not sure what occurred.
The SELECT DISTINCT suggestion should produce a table/query of unique
rows /as determined by the fields you specify/.

Creating additional key fields doesn't make a whole lot of sense here.
Indeed, if the keys are unique and you have duplicates in the table, the
key creation would fail.

Did you try the DISTINCT query?