Query to delete duplicate records in staging table

[ms]

Access 2000:
I am trying to delete duplicate records imported to a staging table leaving one
of the duplicates to be imported into the live table. A unique record is based
on a composite key of 3 fields (vehicleID, BattID, and ChgHrs). VehicleID and
BattID are a TEXT datatype and ChrHrs are a number(long int.) datatype. Since
records to be imported can have duplicate records of the composite key I need to
clean all but one of the duplicate records before importing into the live table.
I import the records using an import specification file.

Thank you,
mark

[DFS]

"ms" <[e-mail]> wrote in message
news:6s********************@comcast.com...

Access 2000:
I am trying to delete duplicate records imported to a staging table leaving one of the duplicates to be imported into the live table. A unique record is based on a composite key of 3 fields (vehicleID, BattID, and ChgHrs). VehicleID and BattID are a TEXT datatype and ChrHrs are a number(long int.) datatype. Since records to be imported can have duplicate records of the composite key I need to clean all but one of the duplicate records before importing into the live table. I import the records using an import specification file.

Thank you,
mark

The Find Duplicates query wizard might help, but it's not even needed -
unless you just want to see which records are duplicated.

* If you do the insert via the db.Execute method in code it will insert the
valid records, but give you no indication any dupes failed to insert.

* If you execute an append query from the query window, it will tell you how
many dupes could not be inserted, and it will insert the records meeting the
primary key (and other validation constraints).

* You could set the same pk on your staging table, so no dupes can get in
there. Depends on how the data is coming in, and if you need to examine it
first.

[ms]

Thank you.
I am inserting the data using an import specif. file and this:

'Import Raw text to stageChargeLog table.
DoCmd.TransferText acImportFixed, "BMIDLog Link Specification", stageBMIDLog", file

Then I run a query to replace recs from stage table to live table:
CurrentDb.QueryDefs("CleanDups_stageBMIDLog").Exec ute

QUERY:
DELETE stageBMIDLog.*
FROM BMIDLog INNER JOIN stageBMIDLog ON (BMIDLog.BattID = stageBMIDLog.BattID)
AND (BMIDLog.VehicleID = stageBMIDLog.VehicleID) AND (BMIDLog.TotalChgAhs =
stageBMIDLog.TotalChgAhs);

but it fails and gives me a msg saying: "Could not delete from specified
tables". What I don't understand is that the same query as a select statement
returns the records I want to delete. What am I missing?

DFS wrote:

"ms" <[e-mail]> wrote in message
news:6s********************@comcast.com...

Access 2000:
I am trying to delete duplicate records imported to a staging table

leaving one

of the duplicates to be imported into the live table. A unique record is

based

on a composite key of 3 fields (vehicleID, BattID, and ChgHrs). VehicleID

and

BattID are a TEXT datatype and ChrHrs are a number(long int.) datatype.

Since

records to be imported can have duplicate records of the composite key I

need to

clean all but one of the duplicate records before importing into the live

table.

I import the records using an import specification file.

Thank you,
mark

The Find Duplicates query wizard might help, but it's not even needed -
unless you just want to see which records are duplicated.

* If you do the insert via the db.Execute method in code it will insert the
valid records, but give you no indication any dupes failed to insert.

* If you execute an append query from the query window, it will tell you how
many dupes could not be inserted, and it will insert the records meeting the
primary key (and other validation constraints).

* You could set the same pk on your staging table, so no dupes can get in
there. Depends on how the data is coming in, and if you need to examine it
first.