Re: Query to delete duplicate records - but NOT original

From: Roger Carlson (NO-Rog3erc-SPAM_at_hotmail.com)
Date: 02/22/05 

Date: Tue, 22 Feb 2005 12:51:27 -0500

Yes. This line:
   SELECT DISTINCT * INTO Holding...
says that you want distinct records across all of your fields. DISTINCT *
is the same as DISTINCTROW. Simply getting rid of the DISTINCT predicate
will fix it. 

-- 
--Roger Carlson
  Access Database Samples: www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
  http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
"SusanV" <svanallen@nospam-mvps.org> wrote in message
news:%23OELPUQGFHA.2032@tk2msftngp13.phx.gbl...
> I started with delete queries, but the reason I quit the deletes is
because
> whether i go pure SQL or use the silly wizard I get wacky results (below)
> and got tired of having to bring the data back in so now I'm looking to
find
> unique records - I've already got an update query to grab the unique
records
> and make a table, now if I can filter the dupes I'll be able to then
append
> to the unique table. And I know this is probably a back-asswards way to do
> it but... please bear with me.
>
> Now the SQL says it should bring in dupes of all records with matching
> criteria in only those 2 fields. However, it doesn't. Say I have 4 records
> with matching shipname and HSC, and 3 of those have identical other
fields -
> it will bring in 3 records not 4. Or I have 2 records with same shipname
and
> hsc but one other field different - it ignores those 2. So the criteria is
> wrong somehow, and it is still looking at all the fields as though I was
> doing a uniquerow, which I'm not. Does this make any sense at all? Doesn't
> to me.
>
> =/
>
>
> "Roger Carlson" <NO-Rog3erc-SPAM@hotmail.com> wrote in message
> news:uIp3fOQGFHA.524@TK2MSFTNGP14.phx.gbl...
> > I'm not following.  You talk about a delete query, but you're showing a
> > MakeTable query.  The MakeTable query you show below will make a table
> > containing all of the duplicates based on ShipName and HSC regardless of
> > the
> > values of the other fields.
> >
> > Your Delete query should look like this:
> > DELETE E.ID, *
> > FROM AECRF AS E
> > WHERE (((E.ID)>(SELECT MIN(X.ID)
> > FROM AECRF X WHERE E.shipname = X.shipname and E.HSC = X.HSC )));
> >
> > Now, this REQUIRES that you create an Autonumber field in your table.
The
> > autonumber doesn't have to be your primary key and it doesn't need to be
> > used anywhere else, but this method of removing duplicates requires it.
> >
> >
> > -- 
> > --Roger Carlson
> >  Access Database Samples: www.rogersaccesslibrary.com
> >  Want answers to your Access questions in your Email?
> >  Free subscription:
> >  http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
> >
> >
> > "SusanV" <svanallen@nospam-mvps.org> wrote in message
> > news:OtAUi9PGFHA.444@TK2MSFTNGP15.phx.gbl...
> >> Thanks Roger, I've tried that approach. The problem I'm having is that:
> >>
> >> A. I need to key in on 2 fields (ShipName and HSC) to create my
criteria,
> >> and
> >> B. Some of the duplicate records are not picked up as duplicates in the
> > key
> >> fields, as they have different data in other fields (which is not
vital,
> > but
> >> should not be lost - the purpose being that we have to make corrections
> >> to
> >> these other fields). So I still get duplicate Ship/equip in the table
> > after
> >> the delete query. Even taking the parameters of a duplicates query such
> >> as
> >> (pushing into a new table called holding, got tired of reimporting the
> > excel
> >> ***):
> >>
> >> select distinct * into holding from AECRF WHERE [ShipName] In (SELECT
> >> [ShipName] FROM [AECRF] As Tmp GROUP BY [ShipName],[HSC] HAVING
> >> Count(*)>1
> >> And [HSC] = [AECRF].[HSC])
> >> ORDER BY [ShipName], [HSC];
> >>
> >> I STILL get duplicates if other fields (the ones we have to fix) are
not
> >> equal. Shouldn't this query ONLY match the 2 named fields of ShipName
and
> >> HSC? Why is it looking for records which are duplicate acroos the
board?
> >>
> >> I'm confused and frustrated... and thankful for your help!
> >>
> >> Susan
> >>
> >>
> >> "Roger Carlson" <NO-Rog3erc-SPAM@hotmail.com> wrote in message
> >> news:O8e$aoPGFHA.3316@tk2msftngp13.phx.gbl...
> >> > On my website (www.rogersaccesslibrary.com) is a small sample
database
> >> > called: "RemoveDuplicates.mdb" which illustrates how to do this.
> >> >
> >> > -- 
> >> > --Roger Carlson
> >> >  Access Database Samples: www.rogersaccesslibrary.com
> >> >  Want answers to your Access questions in your Email?
> >> >  Free subscription:
> >> >  http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
> >> >
> >> > "SusanV" <svanallen@nospam-mvps.org> wrote in message
> >> > news:%23OhNdFPGFHA.2180@TK2MSFTNGP10.phx.gbl...
> >> >> Hi all,
> >> >>
> >> >> I've got an excel spread*** which I need to incorporate as a new
> > table
> >> > in
> >> >> the access database we use to track a truly fugly project. This
> >> > spread***
> >> >> has multiple columns, but the key columns are ShipName and EquipID.
It
> >> >> was
> >> >> extracted from a VERY poorly designed database that I have no access
> > to.
> >> >> What my guys need is for me to extract a unique list of equipment,
> >> > including
> >> >> vesselname, from this spread***, then set up forms, reports,
> >> >> queries,
> >> > etc.
> >> >> Sounds simple, no? Well, here's the rub. I have no idea WHAT they
did
> > in
> >> > the
> >> >> originating database, but we have approximately 2500 duplicate
records
> >> > (out
> >> >> of the total of 7000).
> >> >>
> >> >> Now I can do a find duplicates, easy beans. But how do I delete the
> >> >> duplicates without losing BOTH records (or in some cases all 5,
ugh).
> >> >>
> >> >> Please help, I've been fighting with this since Friday and am at my
> > wits
> >> >> end...
> >> >>
> >> >> Thanks in advance,
> >> >>
> >> >> SusanV
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>
Quantcast