Query to delete duplicate records - but NOT original

  • Thread starter Thread starter SusanV
  • Start date Start date
S

SusanV

Hi all,

I've got an excel spreadsheet which I need to incorporate as a new table in
the access database we use to track a truly fugly project. This spreadsheet
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 spreadsheet, 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
 
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
sheet):

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
 
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 said:
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
sheet):

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 said:
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

table
in in
the
Click to expand...
Click to expand...
 
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 said:
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 said:
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
sheet):

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 said:
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

Hi all,

I've got an excel spreadsheet which I need to incorporate as a new table
in
the access database we use to track a truly fugly project. This
spreadsheet
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 spreadsheet, 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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
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
sheet):

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


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

Hi all,

I've got an excel spreadsheet which I need to incorporate as a new table
in
the access database we use to track a truly fugly project. This
spreadsheet
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 spreadsheet, then set up forms, reports,
queries,
etc.
Sounds simple, no? Well, here's the rub. I have no idea WHAT they
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
OK, beauty - that gets dupes as defined - now I have to get them to filter
without losing the other fields. Oh, BTW, I found out why this is such a
mess - they have a multi-field primary key, but didn't send us that field in
the excel export <sigh>

Anyways... now I lost what I was going to do to filter these now that I have
them. Crap, too many copies of notepad open, closed the wrong one I suppose.
Thanks for your help, I don't think I'm gonna manage this today...


Roger Carlson said:
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 said:
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 said:
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


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
sheet):

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


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

Hi all,

I've got an excel spreadsheet which I need to incorporate as a new
table
in
the access database we use to track a truly fugly project. This
spreadsheet
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 spreadsheet, 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
Click to expand...
Click to expand...
Click to expand...
 
Thanks tons Roger for your help. I got it this morning in about 2 minutes
with a new empty table, multi-field key and append query. And go figure -
now they don't want or need the data. <shrug>

Roger Carlson said:
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 said:
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 said:
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


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
sheet):

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


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

Hi all,

I've got an excel spreadsheet which I need to incorporate as a new
table
in
the access database we use to track a truly fugly project. This
spreadsheet
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 spreadsheet, 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
Click to expand...
Click to expand...
Click to expand...
 
At least you've learned something. That's never a waste!

--
--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 said:
Thanks tons Roger for your help. I got it this morning in about 2 minutes
with a new empty table, multi-field key and append query. And go figure -
now they don't want or need the data. <shrug>

Roger Carlson said:
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 said:
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
Click to expand...
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.

=/


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


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
sheet):

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
Click to expand...
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


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

Hi all,

I've got an excel spreadsheet which I need to incorporate as a new
table
in
the access database we use to track a truly fugly project. This
spreadsheet
has multiple columns, but the key columns are ShipName and
Click to expand...
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 spreadsheet, 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
Click to expand...
Click to expand...
Click to expand...
 
Too true! What's annoying though is I have done this before, just had a
major brain-fart I guess <grin>
Oh, and in true manager fashion, they DO want the data now. Good thing I
never delete anything right away

Roger Carlson said:
At least you've learned something. That's never a waste!

--
--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 said:
Thanks tons Roger for your help. I got it this morning in about 2 minutes
with a new empty table, multi-field key and append query. And go figure -
now they don't want or need the data. <shrug>

Roger Carlson said:
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


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.

=/


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


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
sheet):

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


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

Hi all,

I've got an excel spreadsheet which I need to incorporate as a new
table
in
the access database we use to track a truly fugly project. This
spreadsheet
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 spreadsheet, 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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top