SQL Delete Function

  • Thread starter Thread starter Andi B
  • Start date Start date
A

Andi B

Hi everyone!

I'm looking for a little help with a database I'm creating:

Every a spreadsheet containing some orderbook information is imported into
an access database, overwriting the information from the previous week. The
spreadsheet contains duplicate entries for some lines however. After
importing the data, a macro runs a query that displays all lines that are
duplicates, and all but one of each must be manually deleted before the
database can be used correctly. In order to remove the need to do this
manually I was wondering if it would be possible to use a delete query to
remove all but one of each of the duplicate records. I have created a
prototype query, but when run it deletes all the duplicate entries. Any
advice on what I can do to achieve my aim?

Thanks in advance,

Andi
 
I just noticed a mistake there - I'm a stickler for getting these things
right: The first sentence should read "Every week a spreadsheet..."

Sorry :)

Thanks again,

Andi
 
Hi everyone!

I'm looking for a little help with a database I'm creating:

Every a spreadsheet containing some orderbook information is imported into
an access database, overwriting the information from the previous week. The
spreadsheet contains duplicate entries for some lines however. After
importing the data, a macro runs a query that displays all lines that are
duplicates, and all but one of each must be manually deleted before the
database can be used correctly. In order to remove the need to do this
manually I was wondering if it would be possible to use a delete query to
remove all but one of each of the duplicate records. I have created a
prototype query, but when run it deletes all the duplicate entries. Any
advice on what I can do to achieve my aim?

Thanks in advance,

Andi
Click to expand...

I'd suggest having a permanent table with a unique Index on the
combination of fields which define a duplicate. Rather than importing
into a new table, import into this table and use SetWarnings to
suppress the error message "x records were not added due to key
violations". Run a Delete query

DELETE * FROM localtable;

before running the append.

John W. Vinson[MVP]
 
Andi B said:
Hi everyone!

I'm looking for a little help with a database I'm creating:

Every a spreadsheet containing some orderbook information is imported into
an access database, overwriting the information from the previous week.
The spreadsheet contains duplicate entries for some lines however. After
importing the data, a macro runs a query that displays all lines that are
duplicates, and all but one of each must be manually deleted before the
database can be used correctly. In order to remove the need to do this
manually I was wondering if it would be possible to use a delete query to
remove all but one of each of the duplicate records. I have created a
prototype query, but when run it deletes all the duplicate entries. Any
advice on what I can do to achieve my aim?

Thanks in advance,

Andi
Click to expand...

Rather than import all the lines and then delete the duplicates, you could
consider writing a query that will not collect duplicates in the first
place. If, when you say duplicates, you mean exact duplicates, then you can
simply change the query so it only returns unique values. However, often
with spreadsheet data, the rows are not exact duplicates and you may need to
write a query with a 'group by' clause.
 
Back
Top