Finding duplicates among duplicates 2

[jeffshex]

I need some advice.
I know how to do the regular built in duplicates query, but this seems to be more involved than that.
I have a table where you enter what programs people are in.
I'm trying to find out who is in program A and program B.
The catch to it all is that they can be in Program A multiple times, same with Program B. So just doing the regular find duplicates could sometimes show who's in Program A twice.

I thought about doing it this way. Run a query where I group everyone in Program A together. Then do the same but group Program B together. With that, somehow then do a duplicates query amongst those 2 queries...that should give me those who have been enrolled in both programs.

Not sure if this is the best way to do this. So I'm open to ideas!!!

-Jeff

 

[Golom]

Not knowing your field names, I'll just make some up

Select DISTINCT PersonID

From tbl As A INNER JOIN tbl As B
     ON A.PersonID = B.PersonID

Where A.Program = 'A' AND
      B.Program = 'B'

 

[jeffshex]

Thanks for the reply!
I have 2 things.
First, it gets crabby...saying that the PersonID could refer to mulitple tables. Granted I only have the one table, but I tried a few things and it still errors, or prompts an input box.

Second, can the program A have a like operator in there?
For example: Where A.Program = Like Part*
Just curious.
Thanks for all the help and ideas so far.

 

[lespaul]

Select DISTINCT A.PersonID

From tbl As A INNER JOIN tbl As B
ON A.PersonID = B.PersonID

Where A.Program Like 'A*' AND
B.Program = 'B'

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[jeffshex]

Awesome!
Thanks to the both of you!
Greatly appreciated for your help.

-Jeff