Re: Removing Duplicates





I would do a query like this to see if you have duplicate key values

Select [Item number],[pack type], count(*) from yourTbl
Group by [Item number],[pack type]
Having (count(*) >2)

That will tell you if you have duplicate values.
I would then insert these records with these key value into a temporary table
for more handling and delete them from your insert table

If there is another field in your imported records that you can guarantee will
have a unique value you could do a select max on that field then use that value
and the key values to join back to the rest of the field to get a complete
record. One way you can do this is to have an autonumber field in your
temporary holding table

If you don't have a field with a unique value you need to write some delphi code
that iterates through the holding table and deletes all but the first record it
encounters for each pair of key values.



Select


I wish it were this straight forward and maybe I am making it to complicated
but I am trying to simply remove duplicates records coming in from a text
file based upon a composite key (Item number) and (pack type) so my
challange is that I need all the import text file information but I need to
remove the duplicates based upon only those two fields.

I am currently loading the import file into a temporary Access table then
using the join information that you provided me I am doing one sql statement
to update records in my Main Access table for those records that are a match
in the temporay Access table and then inserting new records that do not
exist in my Main Access table but do exist in the tempory Access table. At
some point I need to remove the duplicates based upon Item Number and Pack
Type. This is what my primary key is built upon for my Main Access table.

I am not very strong on my sql but so far this is what I am using assuming
that the import text file does not have any duplicates.

//load into temp access table
INSERT INTO [Temp_Import] ([User1],[Item Number],[Description],[Pack
Type],[Site Id],[User4]) SELECT [User1],[Item Num],[Description],[Pack
T],[Site ID1],[User4] FROM [TabDelimited.txt] IN "C:\Data\Delphi\Qbit\"
"Text;"

//update main access table records with info from temp access table
UPDATE [jeff] MT INNER JOIN Import.Temp_Import IT ON MT.[Item Number] =
IT.[Item Number] AND MT.[Pack Type] = IT.[Pack Type] SET MT.[User1] =
IT.[User1], MT.[Description] = IT.[Description], MT.[Site Id] = IT.[Site
Id], MT.[User4] = IT.[User4]

//insert new records into main access table where records do not exist in
temp access table
INSERT INTO [jeff] ([User1],[Item Number],[Description],[Pack Type],[Site
Id],[User4]) SELECT [User1],[Item Number],[Description],[Pack Type],[Site
Id],[User4] FROM Import.mdb.[Temp_Import] Import WHERE NOT EXISTS (SELECT
[Item Number], [Pack Type] FROM [jeff] WHERE [Item Number] = Import.[Item
Number] AND [Pack Type] = Import.[Pack Type])


"Brian Bushay TeamB" <BBushay@xxxxxxxxx> wrote in message
news:d9em22ttljj2k52m8bkfnkge2bvl4jbdr0@xxxxxxxxxx

What is the best way, using SQL, to remove duplicates from an Access
database?

If you have exact duplicate records do a
Select distinct * Into TempTable.
Then delete the contents of the original table and insert the records from
the
tempTable
--
Brian Bushay (TeamB)
Bbushay@xxxxxxxxx


--
Brian Bushay (TeamB)
Bbushay@xxxxxxxxx
.