Find Duplicates

[Parasyke]

I have a form that uses has a series of about 10 different queries to
get the final VIEW query to populate my form. I wrote the below to find
the duplicates, which works. I don't want to delete the duplicates just
not show them. Any ideas? I just want to show the records that have NO
duplicates.

SELECT [UWID], [UWName], [JobTitle], [BUName], [EmployHireDate],
[ApptStartDate], [TermDate], [EmployTypeCode], [Active], [DL_Loans],
[DL_Loss], [DL_Watch], [DL_Refer], [LR_Loans], [LR_Penalty],
[LR_Correct], [LR_Watch], [LR_Refer], [AuditLevel], [CSAudits_2004],
[CS2004_Perc], [CSAudits_2005], [CS2005_Perc], [CSAudits_12MoCum],
[CS12Mo_Perc], [CS_SigFinds], [DLPriorLosses], [DLNewLosses]
FROM qry2005WatchListInfo
WHERE [UWID] In (SELECT [UWID] FROM [qry2005WatchListInfo] As Tmp GROUP
BY [UWID] HAVING Count(*)>1 )
ORDER BY [UWID];

[Rick Wannall]

Change "Count(*)>1" to "Count(*)=1", which finds only the ones that don't
have duplicates.

[Parasyke]

What if I want all that have just one copy AND one record only of those
that have multiple copies?

Thanks!
Rick Wannall wrote:

Change "Count(*)>1" to "Count(*)=1", which finds only the ones that don't
have duplicates.

[Rick Wannall]

What field uniquely identifies each row in the table you're selecting from?

[Rick Wannall]

By it cannot identify each row uniquely, since it is the field you're using
to detect duplication.

If you want to be able to present just one row out of a group of duplicates,
you must have one (or some combination of fields) that would uniquely
identify the rows, so that it is possible to select the
min/max/first/last/something unique identifier from within the group
identified by the same UWID. Sometimes a date field works for this. If you
have a DateCreated field, or DateUpdated, that might do the trick.

[Parasyke]

UWID is the keyfield in the table

Rick Wannall wrote:

What field uniquely identifies each row in the table you're selecting from?

[Larry Linson]

It could also be done with a subquery or multiple queries using the TOP 1
option, if it doesn't matter which of the records with duplicate key is
chosen.

Larry Linson
Microsoft Access MVP
"Rick Wannall" <wa*****@notadomain.de> wrote in message
news:wC********************@newssvr21.news.prodigy .com...

By it cannot identify each row uniquely, since it is the field you're
using
to detect duplication.

If you want to be able to present just one row out of a group of
duplicates,
you must have one (or some combination of fields) that would uniquely
identify the rows, so that it is possible to select the
min/max/first/last/something unique identifier from within the group
identified by the same UWID. Sometimes a date field works for this. If
you
have a DateCreated field, or DateUpdated, that might do the trick.