asked on

Need help with special Find Duplicates Access Query

In a table (PRODDTA_F30026) I have two (2) fileds:
IEITM - Short Item Number
IELITM - Long Item Number

I have discovered that, for some IELITM - Long Item Numbers there are multiple IEITM - Short Item Numbers.

EXAMPLE:
IEITM      IELITM
15579887      8500-502
15579924      8500-502

I cannot have both 15579887 and 15579924 for one 8500-502.

IEITM >>>-----> IELITM Correct
IEITM >>>-----> IELITM Correct
IEITM >>>--\
>---> IELITM Incorrect
IEITM >>>--/

How do I wite an Access Query that will show only the IELITM's that have duplicate IEITM's? The table has 1.36 million lines and I need a report that shows only occurance like the example above.

SELECT PRODDTA_F30026.IEITM, PRODDTA_F30026.IELITM
FROM PRODDTA_F30026
GROUP BY PRODDTA_F30026.IEITM, PRODDTA_F30026.IELITM
ORDER BY PRODDTA_F30026.IELITM;

Any suggestions? The basic Find Duplicates Query does not work in this situation.

tw

SOLUTION

unknown_routine

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tom Winslow

ASKER

This does not work because there are legitimate occurrences where I can have more than one occurrence of IEITM >>>-----> IELITM where the duplicates are the same IEITM and the same IELITM.

The problem is that I have MORE THAN ONE IEITM paired to the same IELITM.

EXAMPLE - This is correct:
IEITM        IELITM
924246      APCB-10106-5
924246      APCB-10106-5

EXAMPLE - This is NOT correct:
IEITM        IELITM
15579887      8500-502
15579924      8500-502
(Note two different IEITMs)

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tom Winslow

ASKER

I used both suggestions to make the query work.

SELECT DISTINCT PRODDTA_F30026.IEITM, PRODDTA_F30026.IELITM INTO [tblF30026(FindDupes-1)]
FROM PRODDTA_F30026
ORDER BY PRODDTA_F30026.IELITM;

SELECT [tblF30026(FindDupes-1)].IELITM, [tblF30026(FindDupes-1)].IEITM INTO [tblF30026(FindDupes-2)]
FROM [tblF30026(FindDupes-1)]
WHERE ((([tblF30026(FindDupes-1)].IELITM) In (SELECT [IELITM] FROM [tblF30026(FindDupes-1)] As Tmp GROUP BY [IELITM] HAVING Count(*)>1 )))
ORDER BY [tblF30026(FindDupes-1)].IELITM;

Thanks very much for your help.

tw