Solved

Need help with special Find Duplicates Access Query

Posted on 2014-03-27
4
296 Views
Last Modified: 2014-03-27
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
0
Comment
Question by:Tom Winslow
  • 2
4 Comments
 
LVL 15

Assisted Solution

by:unknown_routine
unknown_routine earned 250 total points
ID: 39958838
Select IELITM, count(IEITM)
from  
PRODDTA_F30026
Group by IELITM
Having  count(IEITM)=2
0
 

Author Comment

by:Tom Winslow
ID: 39959017
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)
0
 
LVL 35

Accepted Solution

by:
PatHartman earned 250 total points
ID: 39959346
First you need a list of distinct IEITM/IELITM pairs.

Select Distinct IEITM, IELITM from your table;

Then create a find duplicates query on that query.
0
 

Author Closing Comment

by:Tom Winslow
ID: 39959633
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
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now