SQL QUERY IN MS ACCESS To Filter Duplicate Values in one column but have distinct values in another

I have two columns for example, I want to list all the rows where the items in ROW A appears more than once in Column A, but have distinct values in Column B. So far I have not figured out an answer.  i tried the one below but in MS Access you can't use distinct and count at the same statement i get undefined distinct function error.

SELECT ColumnA
FROM xyz table
GROUP BY ColumnA
HAVING COUNT(DISTINCT ColumnB) >= 2;

Column A     Column B
Apple             x
Banana         a
Orange         b
Orange         b
Banana         x
Avocado       d
kbryant8243Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
what output are you expecting from the query

SELECT X.ColumnA, X.ColumnB
FROM xyzTable X
Inner join
(SELECT Count(xyzTable.[ColumnB]) AS [Count], xyzTable.[ColumnB]
FROM xyzTable
GROUP BY xyzTable.[ColumnB]
HAVING (((Count(xyzTable.[ColumnB]))>=2))) As Y
ON X.ColumnB=Y.ColumnB
0
Nick67Commented:
SELECT Distinct  xyz.ColumnA, xyz.ColumnB from
(SELECT ColumnA, COUNT (ColumnA) as Thecount
FROM xyz
GROUP BY ColumnA
HAVING COUNT (ColumnA) > 1) as derived
INNER JOIN xyz ON derived.ColumnA = xyz.ColumnA;

Will be close.
The derived table gets only those values of ColumnA with a count of two or more.
The rest might not get what you want.
You are looking to exclude not only duplicates, but both halves of the duplicate?
0
Rey Obrero (Capricorn1)Commented:
Forgot the Group By

SELECT X.ColumnA, X.ColumnB
FROM xyzTable AS X INNER JOIN
 (SELECT Count(xyzTable.[ColumnB]) AS [Count], xyzTable.[ColumnB] FROM xyzTable GROUP BY xyzTable.[ColumnB] HAVING (((Count(xyzTable.[ColumnB]))>=2)))  AS Y
ON X.ColumnB = Y.ColumnB
GROUP BY X.ColumnA, X.ColumnB;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nick67Commented:
Select  xyz.ColumnA, xyz.ColumnB from
 (SELECT ColumnA, COUNT (ColumnA) as Thecount
 FROM xyz
 GROUP BY ColumnA
 HAVING COUNT (ColumnA) > 1) as derived
 INNER JOIN xyz ON derived.ColumnA = xyz.ColumnA
Group by xyz.ColumnB
HAVING COUNT (ColumnB) = 1

might do it
0
kbryant8243Author Commented:
I want to identify the records where there are duplicate values in Column  A, but only want to list those with distinct values in another column.  for the example above, i would like to only display the values below


Banana         a
Banana         x
0
Nick67Commented:
Yeah,
I figured the columnA part.
That's what

(SELECT ColumnA, COUNT (ColumnA) as Thecount
  FROM xyz
  GROUP BY ColumnA
  HAVING COUNT (ColumnA) > 1) as derived

is going to give us, is only multiple values in Column A

My question is

Banana         x
Banana         x

happens; what do you want to return
Banana         x
or
NULL
0
kbryant8243Author Commented:
null if the values in B  is the same
0
Nick67Commented:
Ok,  have you tried this then

Select  xyz.ColumnA, xyz.ColumnB from
  (SELECT ColumnA, COUNT (ColumnA) as Thecount
  FROM xyz
  GROUP BY ColumnA
  HAVING COUNT (ColumnA) > 1) as derived
  INNER JOIN xyz ON derived.ColumnA = xyz.ColumnA
 Group by xyz.ColumnB
 HAVING COUNT (ColumnB) = 1

The first HAVING should get rid of all single values of ColumnA
The second HAVING should get rid of all multiple values in ColumnB

But is this possible?

Banana         a
Banana         b
Orange         b
Orange         c

because what's posted will return

Banana         a
Orange         c
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kbryant8243Author Commented:
thanks for all the help guys learned some new SQL today
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.