Unmatched in same(1) table

[LindaSD]

I have one table with three fields. Part#, BasePart#, Description. Where
the BasePart# is a duplicate, I want to know when the Descriptions are
different. I've been going through a lot of work to do this and hoping there
is something easy.
Example:
Part# BasePart# Description
10100 100 BusyBee 20
20100 100 BusyBee 50x50x20
30100 100 MyBusyBee 20
20300 300 MeMyself
40300 300 MeMyself

In the example, I would want to know that BasePart# 100 had three different
descriptions. I would NOT want to see results for 300 because the
descriptions are the same. Some of the basepart#s could have 20 different
records with either all the same or with multiple variations on the
descriptions field.

Currently running a duplicate query to find only those with multiple
basepart#s and using group by on this so it only shows one record for those
all the same and another for each that is different. This still leaves me
with a mess and many more steps.

This project resulted in different people entering different descriptions
for what is basically the same part just packaged in different quantities.
I'm trying to get a list of those parts that need to have their descriptions
changed so they are all the same, and view each variation so I can decide
which is correct.

Any suggestions appreciated. Hope this is enought info.
Linda

 

[ghetto_banjo]

Well the below query will work, but there is one issue. Say BasePart#
100 has 5 descriptions, 2 of which are the same as one another, then
this will ONLY return the other 3 that are all different. I have a
feeling that issue will occur...


SELECT Count(Table1.[BasePart#]) AS [CountOfBasePart#], Table1.
[BasePart#], Table1.Description
FROM Table1
GROUP BY Table1.[BasePart#], Table1.Description
HAVING (((Count(Table1.[BasePart#]))=1));

 

[John Spencer MVP]

You should be able to do that with the following query. It will return all
records where BasePart# has more than one description - the only exception I
can think of is if a description is NULL in one record and has a value in
another record.

SELECT *
FROM SomeTable
WHERE BasePart# In
(SELECT [BasePart#}
FROM [SomeTable] as Temp
GROUP BY [BasePart#]
HAVING Min(Temp.[Description]) <> Max(Temp.[Description]))
ORDER BY [BasePart#], [Part#]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[LindaSD]

A little tweeking got Ghetto_Banjo's sql to work. Here is what worked:

SELECT Count(Part.[BasePart#]) AS [CountOfBasePart#], Part.[BasePart#],
Part.[Desc]
FROM Part
GROUP BY Part.[BasePart#], Part.Desc
HAVING (((Count(Part.[BasePart#]))>=1))

I proved it out and am a happy camper! Thanks!!!
Linda

 

[LindaSD]

None of the suggestions work. John - I get a syntax error on your code after
"Where BasePart# In" (even after I fixed the } to a ]. The other suggestions
gave me every basepart# with the same description back. So if there are 4
that have identical desc it gives them all 4 back even though they are the
same and I only want 1 returned -it's because the part# is in the query and
the part number are different causing that issue. Every time I play with
John's query to get the part# field out, it still shows.

 

[John Spencer MVP]

I missed some square brackets. Your field names don't follow the naming
guidelines and therefore require square brackets.

Naming Guidelines:
Use ONLY letters, numbers, and the underscore character to name fields and
tables. Also, avoid the use of reserved words if you can - description is a
property of many objects in access and therefore can (usually won't) cause an
error when used in a query.

Hopefully, I've fixed the errors in this.

SELECT *
FROM SomeTable
WHERE [BasePart#] In
(SELECT [BasePart#]
FROM [SomeTable] as Temp
GROUP BY [BasePart#]
HAVING Min(Temp.[Description]) <> Max(Temp.[Description]))
ORDER BY [BasePart#], [Part#]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

None of the suggestions work. John - I get a syntax error on your code after
"Where BasePart# In" (even after I fixed the } to a ]. The other suggestions
gave me every basepart# with the same description back. So if there are 4
that have identical desc it gives them all 4 back even though they are the
same and I only want 1 returned -it's because the part# is in the query and
the part number are different causing that issue. Every time I play with
John's query to get the part# field out, it still shows.

I have one table with three fields. Part#, BasePart#, Description. Where