Duplicates Query not showing correct results

[Anne]

I am working with imported data and through sum queries, I have come up with
summaries of 2 tables, estimates and invoices.
I ran the duplicates query and here is the SqL

SELECT [99_3TestlDupItem].ItemID, [99_3TestlDupItem].JobID,
[99_3TestlDupItem].JobNo, [99_3TestlDupItem].FirstOfJobName,
[99_3TestlDupItem].Item, [99_3TestlDupItem].Memo, [99_3TestlDupItem].SumOfAmt
FROM 99_3TestlDupItem
WHERE ((([99_3TestlDupItem].ItemID) In (SELECT [ItemID] FROM
[99_3TestlDupItem] As Tmp GROUP BY [ItemID] HAVING Count(*)>1 )) AND
(([99_3TestlDupItem].JobID)=1380))
ORDER BY [99_3TestlDupItem].ItemID;

Restricting it to one job number, I only did because I know in this job I
have one duplicate ItemID for jobId 1380. The itemID will be duplicated
because it is used for each job, but within one job there should be no
duplicates. for job 1380 item 177 repeats but after the wizard, it shows all
items, probably because it is not doing it with the two variable of Job and
ItemID.

 

[Ken Sheridan]

You need to correlate the subquery with the outer query on the JobID and
ItemID columns so that it only counts the rows for the current job/Item.
Because of this you can't use the IN operator, but either of these should
work:

SELECT *
FROM 99_3TestlDupItem As T1
WHERE EXISTS
(SELECT JobID
FROM 99_3TestlDupItem As T2
WHERE T2.JobID = T1.JobID
AND T2.ItemID = T1.ItemID
GROUP BY JobID, ItemID
HAVING COUNT(*) > 1);

SELECT *
FROM 99_3TestlDupItem As T1
WHERE
(SELECT COUNT(*)
FROM 99_3TestlDupItem As T2
WHERE T2.JobID = T1.JobID
AND T2.ItemID = T1.ItemID) > 1;

Ken Sheridan
Stafford, England