Find duplicates query not working.....

[HughMcMenamin via AccessMonster.com]

I have 5 Fields: If the ParentLink Field has dups plus ANY one of the other 3
fields have dups, I want to pull it, within a date range.
1) InvalidRouting
2) InvalidAcct
3) InvalidTypeSent
4) ParentLink
5) Date

Can anyone see what I am doing wrong? It's not working at all. It's not
giving error, it just doesn't show the dups and I can see them in the table
when I search for them manually. SQL below:

SELECT dbo_DDReturns.InvalidRouting, dbo_DDReturns.InvalidAcct, dbo_DDReturns.
InvalidTypeSent, dbo_DDReturns.ParentLink, dbo_DDReturns.Date
FROM dbo_DDReturns
WHERE (((dbo_DDReturns.InvalidRouting) In (SELECT [InvalidRouting] FROM
[dbo_DDReturns] As Tmp GROUP BY [InvalidRouting],[InvalidAcct],
[InvalidTypeSent],[ParentLink] HAVING Count(*)>1 And [InvalidAcct] =
[dbo_DDReturns].[InvalidAcct] And [InvalidTypeSent] = [dbo_DDReturns].
[InvalidTypeSent] And [ParentLink] = [dbo_DDReturns].[ParentLink])) AND (
(dbo_DDReturns.Date) Between [Start Date] And [End Date]))
ORDER BY dbo_DDReturns.InvalidRouting, dbo_DDReturns.InvalidAcct,
dbo_DDReturns.InvalidTypeSent, dbo_DDReturns.ParentLink;

I created with the find dups wizard.

Thanks in advance! Shamrox

 

[John Spencer]

Unfortunately, the Find dups query wizard has to have ALL the chosen fields
match to return a duplicate.

You would need to build 3 separate find dupes queries to find duplicates.

You could build the three queries and then grab the where clauses and
combine them with ors
SELECT dbo_DDReturns.InvalidRouting, dbo_DDReturns.InvalidAcct,
dbo_DDReturns.
InvalidTypeSent, dbo_DDReturns.ParentLink, dbo_DDReturns.Date
FROM dbo_DDReturns
WHERE (
dbo_DDReturns.ParentLink In
(SELECT [ParentLink]
FROM [dbo_DDReturns] As Tmp
GROUP BY [ParentLink],[InvalidAcct]
HAVING Count(*)>1 And [InvalidAcct] =[dbo_DDReturns].[InvalidAcct])
OR dbo_DDReturns.ParentLink In
(SELECT [ParentLink]
FROM [dbo_DDReturns] As Tmp
GROUP BY [ParentLink], [InvalidRouting]
HAVING Count(*)>1 And [InvalidRouting]
=[dbo_DDReturns].[InvalidRouting] )
OR dbo_DDReturns.ParentLink In
(SELECT [ParentLink]
FROM [dbo_DDReturns] As Tmp
GROUP BY [ParentLink], [InvalidTypeSent]
HAVING Count(*)>1 And [InvalidTypeSent]
=[dbo_DDReturns].[InvalidTypeSent] )
)
AND
dbo_DDReturns.Date) Between [Start Date] And [End Date]

ORDER BY dbo_DDReturns.InvalidRouting, dbo_DDReturns.InvalidAcct,
dbo_DDReturns.InvalidTypeSent, dbo_DDReturns.ParentLink;


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[shamrox]

Thanks John - Much appreciated