delete duplicates of choice

[Guest]

HI,

I have one table that is the result of a union query on two tables. ONE of
the fields in both tables designates the source table (alpha or beta) and is
important for the purposes for which I created this table. However, some, but
not all, of the records in beta table are identical (duplicates) to records
from alpha table. I conducted a "find duplicates" query on all the fields
besides the "source table" field to locate the duplicates. As the "find
duplicates" query allows, I also display the ONE field that has a different
value between the duplicate records, "source table". I would like to delete
the duplicate record that come from the "alpha" table. Is there a way to do
this. I have tried many ways but can't figure it out. Help!!!!

 

[Guest]

The easiest way is to change your "Find duplicates" query into a delete
query, because it already identifies the duplicate records. Open your "Find
duplicates" query in SQL view so that you can edit it. Add the following
text to the very beginning of the query:

DELETE *
FROM
(

.. . . and at the end of the query, remove the semicolon at the very end and
then add the following text:

)
WHERE ([source table] = "alpha");

For example, if the Query Wizard created the following SQL statement for you:

SELECT tblUnionTbl.PID, tblUnionTbl.PlayerName, tblUnionTbl.SrcTable
FROM tblUnionTbl
WHERE (((tblUnionTbl.PID) In (SELECT [PID] FROM [tblUnionTbl] As Tmp
GROUP BY [PID],[PlayerName] HAVING Count(*)>1 And [PlayerName] =
[tblUnionTbl].[PlayerName])))
ORDER BY tblUnionTbl.PID, tblUnionTbl.PlayerName);

.. . . then you'd edit it as follows:

DELETE *
FROM
(SELECT tblUnionTbl.PID, tblUnionTbl.PlayerName, tblUnionTbl.[source table]
FROM tblUnionTbl
WHERE (((tblUnionTbl.PID) In (SELECT [PID] FROM [tblUnionTbl] As Tmp
GROUP BY [PID],[PlayerName] HAVING Count(*)>1 And [PlayerName] =
[tblUnionTbl].[PlayerName])))
ORDER BY tblUnionTbl.PID, tblUnionTbl.PlayerName)
WHERE ([source table] = "alpha");

.. . . where tblUnionTbl is the name of the table that records in the union
of two tables were appended to, PID is the primary key of the original source
tables, PlayerName is just a column to display, source table is the name of
the column that contains either "alpha" or "beta" to identify which table the
record originally came from, and the duplicate "alpha" records are the
records to get rid of.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.