[AccessD] query that deletess dups ?

Another idea is to create a copy of the structure of the table...identify 
the fields that make up a unique key and make them a key in the new 
table...run an APPEND query from old table to populate new table...it will 
omit the dups for you...

Hope it helps...

Mark


>From: bruce_bruen at mlc.com.au
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] query that deletess dups ?
>Date: Fri, 23 Jan 2004 11:19:34 +1100
>
>
>
>
>
>Check the archives, I had a similar problem last year.  I believe it was
>either Wiliam H or Gustav thtat provided the answer using a select first,
>last or max field in the table you wish to delete from. You could search on
>my surname.
>
>Sorry, the actual anser is on a drive I dont have anymore....
>
>Bruce
>
>
>
>                       "Dave Sharpe"
>                       <davesharpe2 at cox.net>            To:       "Access 
>Developers discussion and problem solving"
>                       Sent by:                          
><accessd at databaseadvisors.com>
>                       accessd-bounces at databasea        cc:
>                       dvisors.com                      Subject:  Re: 
>[AccessD] query that deletess dups ?
>
>
>                       23/01/2004 11:14
>                       Please respond to Access
>                       Developers discussion and
>                       problem solving
>
>
>
>
>
>
>Oleg ( I think )
>
>As there have been no attempts yet; I googled and found the
>following that looks like it should help You.
>
>http://www.databasejournal.com/features/msaccess/article.php/3077791
>
>Dave
>
>
>----- Original Message -----
>From: <Oleg_123 at xuppa.com>
>To: <accessd at databaseadvisors.com>
>Sent: Thursday, January 22, 2004 4:20 PM
>Subject: [AccessD] query that deletess dups ?
>
>
>How can I delete dup records ? Anyone can give me a hint...
>
>SELECT DISTINCTROW [emp table].[employee number], [emp table].[employee
>name], [emp table].COMPANY
>FROM [emp table]
>WHERE ((([emp table].[employee number]) In (SELECT [employee number] FROM
>[emp table] As Tmp GROUP BY [employee number],[employee name] HAVING
>Count(*)>1  And [employee name] = [emp table].[employee name])))
>ORDER BY [emp table].[employee number], [emp table].[employee name];
>
>
>-----------------------------------------
>Get Breaking News from CNN, ABC, NBC, CBS Now.
>http://www.xuppa.com/news/?link=webmail
>
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Get a FREE online virus check for your PC here, from McAfee. 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963