Re: Multiple subqueries in a union query

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steve" <steve.dempsen@xxxxxxxxx> wrote in message
news:1152139513.370243.212540@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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")


.

Relevant Pages

  • Re: Jet SQL and Virtual tables/subqueries qiestions
    ... subquery, but it's curious... ... repeated trips through the Query Editor's "graphical" side did ... The Jet Expression Evaluator (what functions & stuff are actually ... within brackets within the query. ...
    (microsoft.public.access.queries)
  • RE: Display most recent comment in report
    ... There are parentheses around the subquery! ... I'm not sure just what you are doing, but the SQL statement is a query; ... FROM Projects INNER JOIN [Communications Log] AS CL1 ... NoteDate and Notes as their ControlSource properties. ...
    (microsoft.public.access.gettingstarted)
  • Re: Mangled, with a difference
    ... My experience with this subquery as a "table" in SQL in ACCESS (where ACCESS ... query will stop working if I create the query, save it, close it (and ACCESS ... I can't say that I've noted that the mangling differs on different PCs, ... inside the square brackets Access added which are themselves inside square ...
    (microsoft.public.access.queries)
  • Re: Multiple AND criteria
    ... A subquery is like a whole SELECT query inside another query. ... Then switch it to SQL view to ... FROM tblPart INNER JOIN tblPartAttrib ...
    (microsoft.public.access.queries)
  • Re: Calculate change from record to record
    ... another query that uses the crosstab query as an input "table." ... subquery in this new query. ... [Event Procedure] ...
    (microsoft.public.access.queries)