Compound Select

I have a cool new form I'm having trouble with the rowsource
property...primarily because I'm hard-headed... Here's the situation:

I want to select as my row source all SERIALs that are NOT in the
many-to-many relationship with a certain Equipment-class (in a combo box
called cbToolClassID). I think it's a compound query. I've implemented it
using stored queries, and it works. But I want it to be in one SQL statement
in the rowsource property since the stored queries are otherwise stored
outside the form for no other reason.

Row Source:
SELECT DISTINCT tblMICAP.MICAP, tblMICAP.ToolDescription, tblMICAP.ID FROM
tblMICAP LEFT JOIN qryxrefToolClassID ON tblMICAP.ID =
qryxrefToolClassID.MICAPID WHERE (((qryxrefToolClassID.MICAPID) Is Null));

qryxrefToolClassID:
SELECT tblxrefMICAPToolClass.ID, tblxrefMICAPToolClass.MICAPID,
tblxrefMICAPToolClass.ToolClassID
FROM tblxrefMICAPToolClass
WHERE (((tblxrefMICAPToolClass.ToolClassID)=[cbToolClassID]));


Can't I make this into one statement?
--
Jim

You should be able to do it with a union query, BUT, the fields in both
queries must match. Same number of fields, in same order, and same datatype.

Like this --
SELECT DateTime, Text, Number-long integer, memo, Number-single
FROM xxx
UNION ALL SELECT DateTime, Text, Number-long integer, memo, Number-single
FROM yyyy
WHERE DateTime Date();

--
KARL DEWEY
Build a little - Test a little


"JimS" wrote:

I have a cool new form I'm having trouble with the rowsource
property...primarily because I'm hard-headed... Here's the situation:

I want to select as my row source all SERIALs that are NOT in the
many-to-many relationship with a certain Equipment-class (in a combo box
called cbToolClassID). I think it's a compound query. I've implemented it
using stored queries, and it works. But I want it to be in one SQL statement
in the rowsource property since the stored queries are otherwise stored
outside the form for no other reason.

Row Source:
SELECT DISTINCT tblMICAP.MICAP, tblMICAP.ToolDescription, tblMICAP.ID FROM
tblMICAP LEFT JOIN qryxrefToolClassID ON tblMICAP.ID =
qryxrefToolClassID.MICAPID WHERE (((qryxrefToolClassID.MICAPID) Is Null));

qryxrefToolClassID:
SELECT tblxrefMICAPToolClass.ID, tblxrefMICAPToolClass.MICAPID,
tblxrefMICAPToolClass.ToolClassID
FROM tblxrefMICAPToolClass
WHERE (((tblxrefMICAPToolClass.ToolClassID)=[cbToolClassID]));


Can't I make this into one statement?
--
Jim