Use a sub-qry to combine all three queries

  • Thread starter Thread starter JoJo Sharp via AccessMonster.com
  • Start date Start date
J

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;
 
JoJo said:
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;
Click to expand...

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 said:
So Smart! It worked perfectly. Thank you so much.
Click to expand...

Also try it without

A.plan <> "pc" AND

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

James A. Fortune
 
Back
Top