Re: SQL Delete Function

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: John Vinson (jvinson_at_STOP_SPAM.WysardOfInfo.com)
Date: 03/03/05 

Date: Wed, 02 Mar 2005 17:40:36 -0700

On Wed, 2 Mar 2005 22:20:25 -0000, "Andi B" <mgem35@dsl.pipex.com>
wrote:

>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'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]

Relevant Pages

  • RE: Showing data based on other values
    ... Importing and linking have two different purposes. ... data not in the local database and that you do not want in the database. ... To create an append query you start out creating a regular ...
    (microsoft.public.access.dataaccess.pages)
  • Re: Best design for importing montlhly data
    ... Can I link excel files to the database even if the xls is a new file each ... should I run a make table query and overwrite it? ... If the data you are importing ... I have basically the same question for the excel files. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Access problem
    ... First off, let me say that no offense is intended to the original database designer, as I wouldn't be surprised if she may have volunteered to create it without expecting to be compensated financially. ... Access will ask you "Which fields might contain duplicate information?) ... the second query ("Yearly Donations Received Querry Query") is based on the first one ("Yearly Donations Received ...
    (comp.databases.ms-access)
  • Re: Merging two tables.
    ... create an append query to the combined table that loads up ... table2 again observing any duplicate records. ... The database that these are linked to are the active employees of two sister ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Running Update Macro disables switchboard
    ... What are the reports and query that you are deleing and importing? ... The macro begins with 'setwarnings' to 'no,' and echo turned off. ... As mentioned in my first message, I placed a final line in to close the database, as a work around. ...
    (microsoft.public.access.macros)