Identifying duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to identify duplicate [exception oid] + role for borrowers and
collateral owners. This is not working. How can I code it?

And, what does the HAVING clause do? I can not find a definition.

SELECT a.[exception oid] AS Expr1, a.role AS Expr2, Count(*) AS Cnt1
FROM tblassociatedpartylinks AS a
WHERE (role like "*BORROWER*" or role like "*COLLATERAL OWNER*") and role
not like "*CO-COLL*"
GROUP BY a.[exception oid], a.role
HAVING 3 > 1
ORDER BY 3, 1;

Brenda
 
It looks as if you are trying to use column positions rather than their names
in the HAVING and ORDER BYclauses and I was not aware that Access supported
that particular construct.

Try
HAVING Count(*) > 1
ORDER BY Count(*), a.[exception oid]

The HAVING clause is similar to the WHERE clause in that it filters the rows
to be displayed but it doesn't take effect until the computations have been
made.

Hope This Helps
Gerald Stanley MCSD
 
Back
Top