Ovewriting Duplicates

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

I'm doing a cut and paste with SQL....it wrks fine. What I need to do
is whenever I'm pasting I need to delete the duplicate entries. Lets
say after pasting I have two 1 and two 3. I would need to delete one on
the 1 and one of the 3. Or maybe overwrite any duplicates without
deleteing them. I guess I'm grabbing at a few straws here, so any help
is apprecited.
Thnaks
DS
 
Delete all the 1s and 3s and then append?

If that answer doesn't work then you're going to need to explain what you're
after in a little more detail.
 
Rob said:
Delete all the 1s and 3s and then append?

If that answer doesn't work then you're going to need to explain what you're
after in a little more detail.
Click to expand...
Ok I'll try explain. I have a table with records called MenuInfo I
copy these records into another table with the same structure. This
table is called HoldInfo. Now I need a way of deleting any duplicates
while keeping only one of the duplicates.
Thanks
DS
 
DS said:
Ok I'll try explain. I have a table with records called MenuInfo I
copy these records into another table with the same structure. This
table is called HoldInfo. Now I need a way of deleting any duplicates
while keeping only one of the duplicates.
Thanks
DS
Click to expand...

How do you decide which of the duplicates you want to keep?
 
Rob said:
How do you decide which of the duplicates you want to keep?
Click to expand...
It doesn't matter since they are both the same...maybe I can run a
duplicate query and then a delete query that would run one less than the
number of duplicates, but how would I set this up?
Thanks
DS
 
PMFJI

Wouldn't it be simpler not to create the duplicates in the first place?
E.g. using an append query like this:

INSERT INTO HoldInfo SELECT * FROM MenuInfo
WHERE MenuInfo.XXX NOT IN (SELECT XXX FROM HoldInfo);

(XXX being a field that you can use to identify duplicates)
 
John said:
PMFJI

Wouldn't it be simpler not to create the duplicates in the first place?
E.g. using an append query like this:

INSERT INTO HoldInfo SELECT * FROM MenuInfo
WHERE MenuInfo.XXX NOT IN (SELECT XXX FROM HoldInfo);

(XXX being a field that you can use to identify duplicates)
Click to expand...
I suppose your right. But the problem is I'm not just copying one
record. I'm copying many records and there might be more than oe set of
duplicates, this is where its even more confusing.
Thanks
DS
 
DS said:
I suppose your right. But the problem is I'm not just copying one
record. I'm copying many records and there might be more than oe set of
duplicates, this is where its even more confusing.
Thanks
DS
Click to expand...


As John says, it'd be better to avoid the creation of duplicates in the
first place, but if you really have to do it in the way you suggest then it
would be something like:

Create a grouped query, grouping by all the fields that will actually be
duplicated, but with the PK (if you don't have one, add it) set to min (or
max, in fact) - this will just give you the PK fields for the record that
you are going to keep.

Write a delete query that deletes any records with PK values that don't
appear in the first query (...not straightforward, shout if you need help
with how to do that)
 
Rob said:
As John says, it'd be better to avoid the creation of duplicates in the
first place, but if you really have to do it in the way you suggest then it
would be something like:

Create a grouped query, grouping by all the fields that will actually be
duplicated, but with the PK (if you don't have one, add it) set to min (or
max, in fact) - this will just give you the PK fields for the record that
you are going to keep.

Write a delete query that deletes any records with PK values that don't
appear in the first query (...not straightforward, shout if you need help
with how to do that)
Click to expand...
Boy this is getting complicated, I've been scouring the net and it seems
that a lot of people want to delete duplicate fields but keep just one.
I put this together but it deletes everything....I was trying to get it
to loop on the NumberofDups field where it would delete all and the stop
at one.

Private Sub Command2_Click()
With Me.List0
..RowSource = "SELECT First(MenuInfo.MenuID) AS [MenuID Field], " & _
"First(MenuInfo.MenuCatID) AS [MenuCatID Field], " & _
"First(MenuInfo.ItemID) AS [ItemID Field], " & _
"First(MenuInfo.ModCatID) AS [ModCatID Field], " & _
"First(MenuInfo.ModID) AS [ModID Field], " & _
"First(MenuInfo.AttachID) AS [AttachID Field], " & _
"First(MenuInfo.AttModCatID) AS [AttModCatID Field], " & _
"First(MenuInfo.AttachModID) AS [AttachModID Field], " & _
"Count(MenuInfo.MenuID) AS NumberOfDups " & _
"FROM MenuInfo " & _
"GROUP BY MenuInfo.MenuID, MenuInfo.MenuCatID, " & _
"MenuInfo.ItemID, MenuInfo.ModCatID, MenuInfo.ModID, " & _
"MenuInfo.AttachID, MenuInfo.AttModCatID, MenuInfo.AttachModID " & _
"HAVING (((Count(MenuInfo.MenuID))>1) " & _
"AND ((Count(MenuInfo.AttachModID))>1));"
..Requery
End With

Dim Dupe As Integer
For Dupe = 1 To Nz([NumberOfDups] - 1, 0)
DoCmd.SetWarnings False
Dim DupeSQL As String
DupeSQL = "DELETE * FROM MenuInfo "
DoCmd.RunSQL (DupeSQL)
DoCmd.SetWarnings True
Next Dupe
End Sub
Any help apprciated.
Thanks
DS
 
DS said:
Rob said:
John Nurick wrote:

PMFJI

Wouldn't it be simpler not to create the duplicates in the first place?
E.g. using an append query like this:

INSERT INTO HoldInfo SELECT * FROM MenuInfo
WHERE MenuInfo.XXX NOT IN (SELECT XXX FROM HoldInfo);

(XXX being a field that you can use to identify duplicates)




It doesn't matter since they are both the same...maybe I can run a
duplicate query and then a delete query that would run one less than the
number of duplicates, but how would I set this up?
Thanks
DS


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.


I suppose your right. But the problem is I'm not just copying one
record. I'm copying many records and there might be more than oe set of
duplicates, this is where its even more confusing.
Thanks
DS
Click to expand...



As John says, it'd be better to avoid the creation of duplicates in the
first place, but if you really have to do it in the way you suggest then it
would be something like:

Create a grouped query, grouping by all the fields that will actually be
duplicated, but with the PK (if you don't have one, add it) set to min (or
max, in fact) - this will just give you the PK fields for the record that
you are going to keep.

Write a delete query that deletes any records with PK values that don't
appear in the first query (...not straightforward, shout if you need help
with how to do that)
Click to expand...
Boy this is getting complicated, I've been scouring the net and it seems
that a lot of people want to delete duplicate fields but keep just one.
I put this together but it deletes everything....I was trying to get it
to loop on the NumberofDups field where it would delete all and the stop
at one.

Private Sub Command2_Click()
With Me.List0
.RowSource = "SELECT First(MenuInfo.MenuID) AS [MenuID Field], " & _
"First(MenuInfo.MenuCatID) AS [MenuCatID Field], " & _
"First(MenuInfo.ItemID) AS [ItemID Field], " & _
"First(MenuInfo.ModCatID) AS [ModCatID Field], " & _
"First(MenuInfo.ModID) AS [ModID Field], " & _
"First(MenuInfo.AttachID) AS [AttachID Field], " & _
"First(MenuInfo.AttModCatID) AS [AttModCatID Field], " & _
"First(MenuInfo.AttachModID) AS [AttachModID Field], " & _
"Count(MenuInfo.MenuID) AS NumberOfDups " & _
"FROM MenuInfo " & _
"GROUP BY MenuInfo.MenuID, MenuInfo.MenuCatID, " & _
"MenuInfo.ItemID, MenuInfo.ModCatID, MenuInfo.ModID, " & _
"MenuInfo.AttachID, MenuInfo.AttModCatID, MenuInfo.AttachModID " & _
"HAVING (((Count(MenuInfo.MenuID))>1) " & _
"AND ((Count(MenuInfo.AttachModID))>1));"
.Requery
End With

Dim Dupe As Integer
For Dupe = 1 To Nz([NumberOfDups] - 1, 0)
DoCmd.SetWarnings False
Dim DupeSQL As String
DupeSQL = "DELETE * FROM MenuInfo "
DoCmd.RunSQL (DupeSQL)
DoCmd.SetWarnings True
Next Dupe
End Sub
Any help apprciated.
Thanks
DS
Click to expand...

If you do it by code then you'd need to build an SQL string with some kind
of criterion included. "DELETE * FROM MenuInfo" is always going to delete
all records. But you can do it purely with queries. Example:

Set up a table called Duplicates.
Fields:
ID (autonumber and PK)
Field1 (any data type)
Field2 (any data type)

Add some data including duplicated records.

Set up a new query called IDsToKeep
SQL:
SELECT Min(Duplicates.ID) AS MinOfID
FROM Duplicates
GROUP BY Duplicates.Field1, Duplicates.Field2;


Then another query that will actually run the deletions

SQL:
DELETE Duplicates.ID
FROM Duplicates
WHERE (((Duplicates.ID) Not In (select minofid from idstokeep)));
 
Rob said:
Rob said:
John Nurick wrote:


PMFJI

Wouldn't it be simpler not to create the duplicates in the first place?
E.g. using an append query like this:

INSERT INTO HoldInfo SELECT * FROM MenuInfo
WHERE MenuInfo.XXX NOT IN (SELECT XXX FROM HoldInfo);

(XXX being a field that you can use to identify duplicates)





It doesn't matter since they are both the same...maybe I can run a
duplicate query and then a delete query that would run one less than
Click to expand...
the
number of duplicates, but how would I set this up?
Thanks
DS


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.


I suppose your right. But the problem is I'm not just copying one
record. I'm copying many records and there might be more than oe set of
duplicates, this is where its even more confusing.
Thanks
DS



As John says, it'd be better to avoid the creation of duplicates in the
first place, but if you really have to do it in the way you suggest then
Click to expand...
it
would be something like:

Create a grouped query, grouping by all the fields that will actually be
duplicated, but with the PK (if you don't have one, add it) set to min
Click to expand...
(or
max, in fact) - this will just give you the PK fields for the record
Click to expand...
that
you are going to keep.

Write a delete query that deletes any records with PK values that don't
appear in the first query (...not straightforward, shout if you need
Click to expand...
help
with how to do that)
Click to expand...

Boy this is getting complicated, I've been scouring the net and it seems
that a lot of people want to delete duplicate fields but keep just one.
I put this together but it deletes everything....I was trying to get it
to loop on the NumberofDups field where it would delete all and the stop
at one.

Private Sub Command2_Click()
With Me.List0
.RowSource = "SELECT First(MenuInfo.MenuID) AS [MenuID Field], " & _
"First(MenuInfo.MenuCatID) AS [MenuCatID Field], " & _
"First(MenuInfo.ItemID) AS [ItemID Field], " & _
"First(MenuInfo.ModCatID) AS [ModCatID Field], " & _
"First(MenuInfo.ModID) AS [ModID Field], " & _
"First(MenuInfo.AttachID) AS [AttachID Field], " & _
"First(MenuInfo.AttModCatID) AS [AttModCatID Field], " & _
"First(MenuInfo.AttachModID) AS [AttachModID Field], " & _
"Count(MenuInfo.MenuID) AS NumberOfDups " & _
"FROM MenuInfo " & _
"GROUP BY MenuInfo.MenuID, MenuInfo.MenuCatID, " & _
"MenuInfo.ItemID, MenuInfo.ModCatID, MenuInfo.ModID, " & _
"MenuInfo.AttachID, MenuInfo.AttModCatID, MenuInfo.AttachModID " & _
"HAVING (((Count(MenuInfo.MenuID))>1) " & _
"AND ((Count(MenuInfo.AttachModID))>1));"
.Requery
End With

Dim Dupe As Integer
For Dupe = 1 To Nz([NumberOfDups] - 1, 0)
DoCmd.SetWarnings False
Dim DupeSQL As String
DupeSQL = "DELETE * FROM MenuInfo "
DoCmd.RunSQL (DupeSQL)
DoCmd.SetWarnings True
Next Dupe
End Sub
Any help apprciated.
Thanks
DS
Click to expand...


If you do it by code then you'd need to build an SQL string with some kind
of criterion included. "DELETE * FROM MenuInfo" is always going to delete
all records. But you can do it purely with queries. Example:

Set up a table called Duplicates.
Fields:
ID (autonumber and PK)
Field1 (any data type)
Field2 (any data type)

Add some data including duplicated records.

Set up a new query called IDsToKeep
SQL:
SELECT Min(Duplicates.ID) AS MinOfID
FROM Duplicates
GROUP BY Duplicates.Field1, Duplicates.Field2;


Then another query that will actually run the deletions

SQL:
DELETE Duplicates.ID
FROM Duplicates
WHERE (((Duplicates.ID) Not In (select minofid from idstokeep)));
Click to expand...
Thanks,
I'll give it a try....and get back to you.
DS
 
Back
Top