Finding Duplicates Using HAVING clause

[melost]

Hi,
Basically, I'm trying to pull duplicates that have the same id, sub_date, and code. If you notice below id 000022331 is returning different codes (not dupes.) Therefore, its not a 'true' duplicate. id 000012311 would be (dup id, sub_date, AND code). Here's my Access's SQL statement below. I know I need to ask specifically for the code, but not quite sure how. Perhaps there's an easier way?

Tables are:
tbl_1
CRN (Primary Key), id, date,

tbl_2
line_num (Primary Key) CRN, code

SELECT tbl_1.id, tbl_1.date, tbl_2.code, tbl_1.CRN, tbl_1.othr_num, tbl_1.status
FROM tbl_1 INNER JOIN tbl_2 ON tbl_1.CRN = tbl_2.CRN
WHERE (((tbl_1.date) In (SELECT [date] FROM [tbl_1] As Tmp GROUP BY [date],[id] HAVING Count(*)>1 And [id] = [tbl_1].[id])) AND ((tbl_1.status)<>"Duplicated"))
ORDER BY tbl_1.id;

qry_Duplicate returned rows:

id date code CRN othr_num status
000012311 4/20/05 d4110 051250900020 009349 Pending
000012311 4/20/05 d4110 051250900019 009349 Pending
000022331 5/1/05 d6211 051250900025 009349 Pending
000022331 5/1/05 d0111 051250900024 009349 Pending
000052274 4/20/05 D4110 051250900037 523979 Pending
000052274 4/20/05 D4110 051250900038 523979 Pending
000066666 4/20/05 d4110 051250900028 550724 Pending
000066666 4/20/05 d4110 051250900029 550724 Pending

Thanks,
melost

 

[ByteMyzer]

Try:
[tt]
SELECT t1.id, t1.date, tbl_2.code, t1.CRN, t1.othr_num, t1.status
FROM tbl_1 t1 INNER JOIN tbl_2 ON t1.CRN = tbl_2.CRN
WHERE EXISTS
(SELECT t2.id, t2.date, t2.code FROM tbl_1 t2 WHERE t2.status<>'Duplicated' GROUP BY t2.id, t2.date, t2.code HAVING t2.id=t1.id AND t2.date=t1.date AND t2.code=t1.code AND Count(t2.code)>1
ORDER BY t1.id;
[/tt]
See if this works for you.

 

[cmmrfrds]

Depending on the size of your tables it is usually more efficient to avoid the having clause.

Select sub_id, dup_date, code
From
(
Select sub_id, dup_date, code, count(*) as cnt
From yourtable
Group by sub_id, dup_date, code
)
Where cnt > 1

 

[melost]

ByteMyzer,

Whew, finally got it to work. Thanks for the hand. I've never used WHERE EXISTS before.

melost