Finding Duplicates across 2 fields

[DaveT99]

I have a list from an inventory management system of the manufacturers part nos (MPN), stock item nos and area locations, the data set shows that a unique MPN is used in different area locations, resulting in many records with the same MPN
I want to check if a unique MPN has more than one stock item no, i.e real duplicate stock items


I have tried and failed to achieve this using duplicate queries,
but I eventually achieved the result by using a find duplicate query, then using this query in a report (grouping records by MPN) and reviewing the list manually (very tiresome)
Can anyone tel me how to find duplicate records i.e. a unique record will be a MPN + Stock Item No

[ranman256]

did you try the Query Wizard to make a FIND DUPLICATE query using your criteria?

[DaveT99]

yes, the dataset is a download from SAP. There is no unique value in either Area Location, Stock No or MPN.
as a Stock Item No can be in one or more locations and a stock item should be unique to a MPN.
I wanted to identify if a MPN has more than 1 Stock Item No, as this would be a duplicate record
As indicated I checked for duplicates for MPN and then used this query to create a report grouping on the MPN which gave me something like
MPN = 6306ZZ
347689 Bearing Area 1
347689 Bearing Area 2
347689 Bearing Area 3
456721 Bearing Area 4
so this means I have a dulicate stock No for Bearing MPN 6306ZZ
but many didnt have this duplicate and I had to review a long list manually, so looking for a auto solution
in the small sample of Bearings I did find 20 duplicated stock Nos, and this was only for a sample so impossible to check all manually
Hope this helps and thanks

[Micron]

I'm thinking I must be missing something, because this looks like a simple totals query, grouped by MPN having a count >1 for Stock No, but there seems to be a contradiction in your statements:

the data set shows that a unique MPN is used in different area locations

There is no unique value in either Area Location, Stock No or MPN.

otherwise, something like

SELECT tblMyTable.[MPN], Count(tblMyTable.[Stock Item No]) AS [CountOfStock Item No]
FROM tblMyTable
GROUP BY tblMyTable.[MPN]
HAVING (((Count(tblMyTable.[Stock Item No]))>1));

[DaveT99]

First of all I didnt get an error I just wasnt finding the duplicated stock items. I can see why you say there was a contradiction.
Actually with your help I have obtained the list of duplicates
But by using the 1st query to run a 2nd query for duplicates on MPN, i.e this is an example of a duplicated item from the 1st duplicate query
Thanks again for your help, fanstatic

MPN	Stock_ID	CountOfStock_ID
62052Z	4-054-67-0480	2
62052Z	4-063-67-0218	3

[Micron]

glad to have been of help