help with embedding sql into vba

[ArielZusya]

I've got a query that queries another query. I can't seem to figure out how
to properly embed the second query's SQL in the first as a subquery. I was
thinking it might be easier to refer to the two as sql statements in vba but
I'm now running into the same trouble. I was thinking I could use

CurrentDb.OpenRecordset(stMySQL1).Fields(0)

assigned to a string variable like stMyOutPut1 and then call that from the
second SQL statment but I'm struggling. Any help would be greatly
appreciated. In case it's useful here's the SQL from my two queries (note
that I'd like to embed that first in that second instead of calling to a
named query and dynamically fill in that refnum in vba but I can't figure out
how to do that):

qryShowAllInFirstRefNumGroup:

SELECT tblSeat.Seat, tblMain.ID_Main
FROM tblSeat RIGHT JOIN tblMain ON tblSeat.Seat = tblMain.Seat
WHERE (((tblMain.RefNum)="205CBV609"));

qryAllSeatsUnusedInFirstRefNumGroup:

SELECT Min(tblSeat.ID_Seat)
FROM qryShowAllInFirstRefNumGroup RIGHT JOIN tblSeat ON
qryShowAllInFirstRefNumGroup.Seat=tblSeat.ID_Seat
WHERE (((qryShowAllInFirstRefNumGroup.Seat) Is Null));

 

[John Spencer]

SELECT Min(tblSeat.ID_Seat)
FROM (
SELECT tblSeat.Seat, tblMain.ID_Main
FROM tblSeat RIGHT JOIN tblMain ON tblSeat.Seat = tblMain.Seat
WHERE tblMain.RefNum="205CBV609")
) as qryShowAllInFirstRefNumGroup
RIGHT JOIN tblSeat
ON qryShowAllInFirstRefNumGroup.Seat=tblSeat.ID_Seat
WHERE (((qryShowAllInFirstRefNumGroup.Seat) Is Null));

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[ArielZusya]

HA! That's so simple and exactly what I was wanting to do! Thank you so
much for your help! That's so cool. Though you can't see me, know that I am
currently dancing the happy-dance and getting funny looks from my coworkers.
Thanks for your help!