By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,584 Members | 3,458 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,584 IT Pros & Developers. It's quick & easy.

Query to delete duplicate records in staging table

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
DFS
"ms" <ms@nospam.comcast.net> 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.

Nov 12 '05 #2

P: n/a
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" <ms@nospam.comcast.net> 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.


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.