Duplicate field search

[Kenny1943]

I have a table that contains task information to be worked. There are 2 task ID's that are similar. I'm trying to create a report that will give any account number that has both of these task ID's. I'm would like to have the code all in the report. So it doesn't "junk up" the database.

This what I have so far:

SELECT OpenTasks.AcctNum, OpenTasks.ReceiveDate, OpenTasks.TaskID

FROM OpenTasks

WHERE (((OpenTasks.AcctNum) In (SELECT [AcctNum] FROM[OpenTasks] As Tmp GROUP BY [AcctNum] HAVING Count(*)>1 )) AND ((OpenTasks.TaskID)="ru01" Or (OpenTasks.TaskID)="ru02&quot)
ORDER BY OpenTasks.AcctNum;

This does give me account numbers with the 2 task ID's, but it also gives some account numbers that do not. Please help!

 

[r937]

that's a tough one, no question

you had it almost right

[tt]select AcctNum
, ReceiveDate
, TaskID
from OpenTasks
where AcctNum in
( select AcctNum
from OpenTasks
where TaskID in ('ru01','ru02')
group
by AcctNum
having count(*)>1
)
order
by AcctNum[/tt]

rudy

http://r937.com/

SQL Consulting

 

[Kenny1943]

r937,

Thanks for the quick response. It works great!!