By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,411 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

Find Duplicates

P: n/a
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];

Jun 7 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Change "Count(*)>1" to "Count(*)=1", which finds only the ones that don't
have duplicates.
Jun 7 '06 #2

P: n/a
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.


Jun 7 '06 #3

P: n/a
What field uniquely identifies each row in the table you're selecting from?
Jun 7 '06 #4

P: n/a
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.
Jun 7 '06 #5

P: n/a
UWID is the keyfield in the table

Rick Wannall wrote:
What field uniquely identifies each row in the table you're selecting from?


Jun 7 '06 #6

P: n/a
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.

Jun 10 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.