Sub Query [?] Help.

[Robeen]

* This is an offshoot [progression] of an earlier Query/post that I had help from June on.

Here is the Table:

FName	Plan	Rate
Bob	A	100
Bob	D	100
Bob	C	100
Bob	W	25
Bob	X	25
Charles	X	25
Charles	A	100
Charles	D	100
Charles	W	25

I need a query that will search the above table for anyone who:


1. HAS a 'W' or 'X' Plan
- AND -
2. DOES NOT HAVE a 'B' or a 'C' Plan.

What I would like to get back from the query is:

Charles	W	25
Charles	X	25

Thanks in advance!

[JoeM]

This will return what you want (substitute your actual Table name in place of "Table1"):

SELECT FName, Plan, Rate
FROM Table1
WHERE Plan In ("W","X")
AND FName in
(SELECT Table1.FName
FROM Table1
GROUP BY Table1.FName
HAVING Sum(IIf([Plan]="B" Or [Plan]="C",1,0)>=1));

[Robeen]

Thanks, Joe!
Is it possible to have an IN statement instead of the 'Or' here: '...IIf([Plan]="B" Or [Plan]="C",1,0 ...' ?
Reason I ask is that where I have provided W, X - and B, C - there are actually about 15 - 20 Plan types that I have to account for on both sides.

[June7]

AFAIK, the IN parameters cannot be dynamic in query object.

VBA can build the parameter string and apply to form or report Filter property. Review http://allenbrowne.com/ser-50.html

However, not sure that will be helpful for your situation.

[Robeen]

Thanks, June!
Appreciate your help . . . again!!

[JoeM]

Reason I ask is that where I have provided W, X - and B, C - there are actually about 15 - 20 Plan types that I have to account for on both sides.

Might make sense to try to create a table with those lists, and link to them.