Retrieve data that does not match

[JojoKa]

Hi,
I have the following table:

PROVID LASTNAME TYPE VERIFY

196966P TAN-CHU OFFICE 1 N
196966P TAN-CHU OFFICE 3 N
196966P TAN-CHU OFFICE 2 Y
196966P TAN-CHU OFFICE 4 N
113450S GUPTA OFFICE 1 Y
113450S GUPTA OFFICE 2 Y
332490F FAICCO OFFICE 1 N
332490F FAICCO OFFICE 3 N

What I am looking to do is get Providerid where at least one Verify equal Y and others equal N.

Based on the above example the query should give me only:

PROVID LASTNAME TYPE VERIFY

196966P TAN-CHU OFFICE 1 N
196966P TAN-CHU OFFICE 3 N
196966P TAN-CHU OFFICE 2 Y
196966P TAN-CHU OFFICE 4 N

Thanks in advance

Joseph

 

[dhookom]

According to your specification, why would
[tt][blue]
113450S GUPTA OFFICE 1 Y
113450S GUPTA OFFICE 2 Y
[/blue][/tt]
not meet your criteria?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JojoKa]

Thanks.
Because Office 1 and 2 both equal Y. If one of them equal N and the Y then I need that to show in the query result.
I hope this answers your question
Joseph

 

[Golom]

Try

Select A.*

From myTable A INNER JOIN

     (Select X.ProvID, Count(*) As [Y Count]
      From myTable X
      Where X.Verify = "Y"
      GROUP BY X.ProvID
      HAVING Count(*) = 1) As B

      ON A.ProvID = B.ProvID

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[JojoKa]

Perfect. Thank you very much.

Joseph