Re: Removing Duplicates

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


.

Relevant Pages

  • Re: Removing Duplicates
    ... remove the duplicates based upon only those two fields. ... I am currently loading the import file into a temporary Access table then ... //update main access table records with info from temp access table ... Brian Bushay (TeamB) ...
    (borland.public.delphi.database.ado)
  • Re: Automating a query
    ... good luck, ... I guess I did not have the right code in for the SQL statement? ... then set index of the fieldin "test" ... "No Duplicates" ...
    (microsoft.public.access.queries)
  • Re: Check table for like values
    ... am looking for a way to find more than just duplicates. ... FROM tblImported INNER JOIN tblContacts ... If you are trying to do this programmatically, copy the SQL statement above ... Dim strSql As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Check table for like values
    ... am looking for a way to find more than just duplicates. ... FROM tblImported INNER JOIN tblContacts ... If you are trying to do this programmatically, copy the SQL statement above ... Dim strSql As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Dublicate Records in Combo Box
    ... >>Try this SQL statement: ... >>DISTINCTROW returns records that are unique for all ... I can't get those duplicates to go away, ...
    (microsoft.public.access.forms)