Unmatached in 1 Table

  • Thread starter Thread starter LindaSD
  • Start date Start date
L

LindaSD

I have one table that I need to find records that are the same BasePart#
field but with different Description field entries, same table.

Example:
Part# BasePart# Description
1100 100 BusyBee
2100 100 Busy Bee
3100 100 BusyBee
4100 100 Busy Bee 20x50
1200 200 Myself 10
2200 200 Myself10
3200 200 Myself10

I want a query that will show me only BasePart# 100 showing the 3 different
descriptions for that. I would NOT want to see BasePart# 200 since all it’s
descriptions are identical.

this is a result of different people entering different descriptions for the
same BasePart# which should not have been allowed. They are the same. The
Part# field designates different packaging options of a BasePart# which is
the same.

I have been using a Find Duplicate Query on the table pulling in only the
BasePart# and Description and doing a Group by. But this still brings in the
one record for basepart# 200 and I have to do yet another query to find a
count or visually review. Too many records for this and this has become too
time consuming.

Any help appreciated.
Linda
 
SELECT *
FROM Parts As P1
WHERE EXISTS (SELECT Parts.[BasePart#],
Parts.Description,
Count(Parts.[Part#]) AS [CountOfPart#]
FROM Parts
GROUP BY Parts.[BasePart#],
Parts.Description
HAVING Count(Parts.[Part#])=1
AND Parts.[BasePart#] = P1.[BasePart#])
ORDER BY [BasePart#], Description;

Warning: There is a flaw in my logic above. If someone entered the same
Description multiple times for a part, it's possible that more than 1 would
show up for all same BasePart# and it would not be returned. You may be able
to see them by changeing the HAVING Count(Parts.[Part#])=1 to HAVING
Count(Parts.[Part#])>1 .
 
Back
Top