Removing Duplicate records in a Table 1

[robojeff]

This may seem like a simple question for most of you but I have never had to do this before...

I made a find duplicate qry with the wizard but this only finds duplicates and doesn't delete them...

What is the easiest way to removing Duplicate records in a Table?

thanks

 

[MajP]

I do this in two parts.
1) build a query, "qryKeep" to return the records you want to keep by using an aggregate query to return the one of each group (first, max, min, etc.). Something like:

SELECT tbl1.intValue,
First(tbl1.autoID) AS autoID
FROM tbl1
GROUP BY tbl1.intValue;

2) Then delete everything not in this query
DELETE tbl1.autoID
FROM tbl1
WHERE (((tbl1.autoID) Not In (select autoID from qryKeep)));

 

[PHV]

faq701-5721

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robojeff]

Thanks MajP for the tip...

I am not familiar with aggregate queries but if I understand you correctly, I need to create 2 queries, one that utilizes the other so I created the two following queries using your suggested code:

DUP1qry:
SELECT tbl1.Item, First(tbl1.Item) AS autoID
FROM tbl1
GROUP BY tbl1.Item;

DUP2qry:
DELETE tbl1.Item
FROM tbl1
WHERE (((tbl1.Item) Not In (select autoID from DUP1qry)));

I first ran a find duplicates query and here is a partial list:

Item Field NumberOfDups
011-445200 3
033-426500 2
041-704000 4

Then when I run DUP2qry, I get the following message box:

"You are about to delete 0 rows from the specified table..."

Afterwards, I ran the find duplicates query again and here is a partial list:

Item Field NumberOfDups
011-445200 3
033-426500 2
041-704000 4

What am I missing?

Thanks again for your help!

 

[PHV]

What is the PrimaryKey of tbl1 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robojeff]

Thanks PHV for the tip-

I am not sure what DF (YourDuplicateFieldName) is but if I understand it correctly this is a name for a field that I want to designate to contain a duplicate number and not an actual field in the table, is this correct?

With this assumption in mind, when I run the followin query, it is asking me for the value of Duplicate which leads me to believe that I need to use something else for thie field...

DELETE [Duplicate]
FROM [tbl1]
WHERE [Item] Not In (SELECT Max([Item]) FROM [tbl1] GROUP BY [Duplicate]);

What should the DF (YourDuplicateFieldName) be if my table only contains the following items:

Item - text
Description - text
Itemtype - Text

Thanks again for your help!

 

[robojeff]

tbl1 does not have a primary key.

I tried to add one but then when I run my make query to create tbl1 from some ODBC tables it deletes the existing table and overwrites the primary key...

 

[PHV]

make query to create tbl1
What is the SQL code of this query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robojeff]

SQL code to make tbl1 is:

------------------------------
SELECT ONEBPCSF_MPBL01.BCHLD AS Item, ONEBPCSF_IIM.IDESC AS Description, ONEBPCSF_IIM.IITYP AS ItemType, 0 AS ASMFlag INTO tbl1
FROM ONEBPCSF_IIM INNER JOIN ONEBPCSF_MPBL01 ON ONEBPCSF_IIM.IPROD = ONEBPCSF_MPBL01.BCHLD
ORDER BY ONEBPCSF_MPBL01.BCHLD DESC;
------------------------------

Also, later in my project I will have several instances where I will need to append data to this table which might potentially add duplicate records which I will need to remove after the fact.... unless there is a way to eliminate duplicates from being entered into my table in the first place.

 

[PHV]

eliminate duplicates from being entered into my table in the first place
SELECT M.BCHLD AS Item, First(I.IDESC) AS Description, First(I.IITYP) AS ItemType, 0 AS ASMFlag
INTO tbl1
FROM ONEBPCSF_IIM AS I INNER JOIN ONEBPCSF_MPBL01 AS M ON I.IPROD = M.BCHLD
GROUP BY M.BCHLD
ORDER BY M.BCHLD DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robojeff]

Thanks PHV-
That works really slick...
I notice that after this query runs, the table tbl1 which is created with this query still does not have a Primary key, which is probably Ok,
but this leads me to wondering what will happen when I append to this table later... Will I need to worry run a query similar to this one in which
that query uses this table as the source?

I attempted to create a query to clean up duplciates after
the appends that I will be making later to this table as follows:
------------------
SELECT tbl1.Item, First(tbl1.IDESC) AS Description, First(tbl1.ItemType) AS FirstOfItemType, 0 AS ASMFlag INTO tbl1
FROM tbl1
GROUP BY tbl1.BCHLD
ORDER BY tbl1.BCHLD DESC;
------------------
Not sure if I have the syntax correct as I get a message
"You tried to execute a query that does not include the specified expression 'Item' as part of an aggrgegate function"

If I can apply this to clean up the table later, I will have
all the answers that I need for this one.

thanks again for your help PHV!

 

[PHV]

SELECT tbl1.Item, First(tbl1.Description) AS Description, First(tbl1.ItemType) AS ItemType, First(ASMFlag) AS ASMFlag INTO tbl1noDup
FROM tbl1
GROUP BY tbl1.Item
ORDER BY tbl1.Item DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robojeff]

hmmm...

I tried this into a query SQL and ran it:

SELECT tbl1.Item, First(tbl1.Description) AS Description, First(tbl1.ItemType) AS ItemType, First(ASMFlag) AS ASMFlag INTO tbl1noDup
FROM tbl1
GROUP BY tbl1.Item
ORDER BY tbl1.Item DESC

and got the following message:

"Circular reference caused by alias "ASMFlag" in query definition's Select list."

Did I type something in wrong?

 

[PHV]

Sorry for the typo:
SELECT tbl1.Item, First(tbl1.Description) AS Description, First(tbl1.ItemType) AS ItemType, First([!]tbl1.[/!]ASMFlag) AS ASMFlag INTO tbl1noDup
FROM tbl1
GROUP BY tbl1.Item
ORDER BY tbl1.Item DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robojeff]

Thanks PHV-

That works good; just one more question...

To make this complete where I can run this query to remove all duplicates within the table tbl1,
do I need to then dump the results of this query back into tbl1 with another query or can both operations
be combined as one aggregate query?