Use a sub-qry to combine all three queries

[JoJo Sharp via AccessMonster.com]

Hello,

I often find myself to have to create multiple queries to get the final tbl I
want. What's the best way to handle such situatuation-a sub-qry? Specifically
in this situation, can someone show me if it is possible to combine my three
queries into one? The tbl structure is as following. The query is to get such
records as following. Thank you very much in advance.

If an id has "pc" (no pc pos) as its plan, select it and nothing else;
If an id has no "pc" as its plan, select any plan, one record only.

My tbl and three separate queries is as following:

id plan
1 pq
1 pc
2 pc
3 hp
3 pq
4 pq
4 hp
5 pc
5 pc pos

SELECT TblPlan.id, TblPlan.plan INTO plan1
FROM TblPlan
WHERE (((TblPlan.plan)="pc"));

SELECT TblPlan.id, First(TblPlan.plan) AS FirstOfplan INTO plan2
FROM TblPlan LEFT JOIN plan1 ON TblPlan.id = plan1.id
GROUP BY TblPlan.id, plan1.id
HAVING (((plan1.id) Is Null));

SELECT id, plan
FROM plan1
UNION SELECT id, FirstofPlan
FROM plan2;

--
Message posted via http://www.accessmonster.com

[James A. Fortune]

This might work:

qryPlan:
SELECT ID, plan FROM tblPlan WHERE plan = "pc" UNION SELECT A.ID, A.plan
FROM tblPlan AS A WHERE A.plan <> "pc" AND A.ID NOT IN (SELECT ID FROM
tblPlan WHERE plan = "pc") AND A.plan IN (SELECT First(plan) FROM
tblPlan WHERE ID = A.ID);

It gave the results you wanted with the data you gave but I didn't test
it beyond that.

James A. Fortune

[JoJo Sharp via AccessMonster.com]

So Smart! It worked perfectly. Thank you so much.

[James A. Fortune]

JoJo Sharp via AccessMonster.com wrote:

> So Smart! It worked perfectly. Thank you so much.
>

Also try it without

A.plan <> "pc" AND

That part may be redundant. I'm glad it worked for you.

James A. Fortune

[JoJo Sharp via AccessMonster.com]

Thank you. It worked wonder as well.