Filter only for Instances where "Y" and "N" apply

[Elysynn]

Hello all,

It's been a few months since I've worked in Access and used SQL and I feel like I've lost almost everything =( I am probably missing something very simple with this issue, so I'm hoping you can kick-start my brain...

Here is my dilemma:

I have a table of data in which there can be several records for each member. In the instances where there is more than one record, i need to filter for where the CoverageActive field is both "Y" and "N"

Example:

Member# CovActive
123456 Y
123456 N
654321 Y
654321 Y
987654 N

I want the query to only return member 123456.

This is the SQL I have that is not working:

SELECT qryTermed4.MCN, qryTermed4.[COVG_ ACTIVE _FLAG]
FROM qryTermed4
WHERE (((qryTermed4.[COVG_ ACTIVE _FLAG])="Y" And (qryTermed4.[COVG_ ACTIVE _FLAG])="N"));

Thanks,
Elysynn

 

[gmmastros]

Does this work for you?

SELECT A.MCN
FROM   qryTermed4 As A
       Inner Join qryTermed4 As B
          On A.MCN = B.MCN
          And A.[COVG_ ACTIVE _FLAG] = 'Y'
          And B.[COVG_ ACTIVE _FLAG] = 'N'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Elysynn]

Indeed it does. Thank you!