Help with Query

[Robeen]

I want to write a Query - on a single Table - that will show me all Names for which:
There is a 'B' or 'C' - but no 'A' Plan.

And another Query that will show me if any Name has more than one 'A' Plan.

From the Table below, I would expect to see:
James [no 'A' Plan] - first Query.
Robert [two 'A' Plans] - second Query.

Thanks in advance!!

Name	Plan	Rate
Bob	A	100
Bob	B	50
Bob	C	25
James	B	50
James	C	25
Robert	A	100
Robert	A	100
Robert	C	25
Robert	C	25

[June7]

Why don't you use the query builder to help structure correct SQL statements? These are both really basic queries.

SELECT * FROM table WHERE Plan <> "A";

SELECT [Name] FROM table WHERE [Plan]="A" GROUP BY [Name] HAVING Count([Plan])>1;

[Robeen]

Thanks, June!
Your second suggestion looks like it will work - I will try it later.
Your first suggestion will return all three names because they all have plans other than 'A'. I am looking for names that have 'B' &/or 'C' as well as having no 'A'.
Thanks again!

[Robeen]

I agree about your first suggestion being really basic - except it did not fulfill my requirement.
The second suggestion - with a 'having', a 'group by', and a 'count()' in the group by clause - might be considered by some of us as a little more than 'really' basic. And that's why your help is so greatly appreciated!
: )
Thanks again!

[June7]

Sorry, did not completely read requirement for first query. That does complicate and elevates the query above basic because the selection of a record is dependent on parameters in other records.

SELECT * FROM table WHERE [Name] NOT IN (SELECT [Name] FROM table WHERE Plan="A")

For the other, in query design view, click the Totals button from the ribbon. This will switch the grid to aggregate query design.

[Robeen]

Thanks, June!
Both queries work exactly to my requirements.

May I piggy-back an extra requirement onto this, please?

This query:
SELECT [Name] FROM table WHERE [Plan]="A" GROUP BY [Name] HAVING Count([Plan])>1;
. . . is only showing the Name in each Row.

Is it possible for me to also show the Plan[s] and the Rate[s] for each Name?
For each Name that has more than one 'A' Plan, I want to see exactly what they have.

For example, with the data I showed in my example table - I would like to see the above query return:

Robert	A	100
Robert	A	100

I believe it requires querying the table twice but I am not able to make it work.

Thanks!

[June7]

That would require two queries or nested query or domain aggregate function.

Try:

SELECT * FROM table WHERE [Name] IN (SELECT [Name] FROM table WHERE [Plan]="A" GROUP BY [Name] HAVING Count([Plan])>1);

[Robeen]

Thanks!
Would it be better to just use two [or more] queries and then query the queries to get the desired output?
Is there anything wrong with that approach?
That is what I usually do in Access once queries get too involved for me.

[June7]

Multiple query objects should work. I just show the two queries as all-in-one nested using IN operator.

You could do the inner query as a stand alone object. Then INNER JOIN that query to the dataset by linking on the [Name] fields.