Multiple subqueries in a union query

[Steve]

I have a union query (pasted at the end of this message) in Microsoft
Access 2003 that works as long as a second subquery is not added to the
union part of the query.

When the query runs, an error message comes up telling me Access
encountered a problem and needs to close.

I know the subquery itself is the correct syntax (as I have tested it
separately), so I am leaning toward this being either a bug or
limitation in Access 2003. However, I have been unable to find
documentation on either.

The steps I've taken so far in proving this could be a limitation or a
bug are:
---------------------------------------------------------
1. Remove the second subquery from both parts of the query (the main
select and the union select). By "second subquery" I mean the subquery
that is selecting from TableD.
2. Run the query and it works.
3. Add the second subquery to the first part of the query (to the main
select). Do not add it to the Union select yet.
4. Run the query and it works.
5. Add the secondsub query to the union select part of the query.
6. Run the query and the error pops up and Access closes.

Any insight is appreciated. Is this a limitation, a bug, or is there
some syntax I don't have correct?

Query:
--------

SELECT test_id
FROM TableA
WHERE test_id NOT IN (SELECT test_id FROM TableC)
AND test_id NOT IN (SELECT test_id FROM TableD WHERE flag="N")

UNION SELECT test_id
FROM TableB
WHERE test_id NOT IN (SELECT test_id FROM TableC)
AND test_id NOT IN (SELECT test_id FROM TableD WHERE flag="N")

 

[Conan Kelly]

Steve,

I'm not quite as knowledgeable as some of these other guys are, but I would try eliminating the WHERE clause from the UNION query.
Make 2 queries (and save them--names could be something like "qryTableA" and "qryTableB"), one that gets the results you want from
TableA and one that gets the desired results from TableB, and then use these queries in the UNION query in place of your table
names:



SELECT test_id
FROM qryTableA

UNION

SELECT test_id
FROM qryTableB



I hope this helps,

Conan Kelly

 

[Allen Browne]

As you found, it is *very* easy to crash Access when using subqueries. One
good workaround is to stack queries on top of each other instead of trying
to do in all in one, as Conan Kelly suggests.

Another possiblity:
1. Save this as (say) QueryD:
SELECT test_id FROM TableD WHERE flag="N";

2. Create a query that uses TableA, TableC, and QueryD.

3. Join TableA to the other two on test_id.

4. Double-click the join line, and choose:
All records from TableA, and any matches from ...
so you get outer joins.

5. Add criteria under TableC.test_id of:
Is Null

6. Same criteria for QueryD.test_id

Test. If that's the right records, switch to SQL View, and UNION the similar
statement for TableB.

 

[Steve]

Thank you for your suggestion about creating another query. I will try
this today.

For people googling this problem, if there's no response from me, that
means this solution worked.

 

[Steve]

Thank you for your suggestion about creating another query that way. I
will try this today.