UNION in Subquery

Ok, I can do the following


SELECT COUNT(cq.qid)
FROM completed_questions cq

UNION

SELECT cq.qid
FROM completed_questions cq


The problem with this on is taht it will return the count but also the
cq.qids from the other query in the union.

But I can't do the following


SELECT COUNT(cq.cqid)
FROM completed_questions cq
WHERE cq.cqid IN (

SELECT cq.qid
FROM completed_questions cq

UNION

SELECT cq.qid
FROM completed_questions cq)



This query should fix the above problem but it doesn't. Access tells me that
"This operation is not allowed in subqueries."
I could be wrong but by using simple queries like this one, i think i've
come to the conclusion that UNION cannot be used in a subquery when COUNT is
in the main query. How do i get around this. UNION was the only way i could
combine the two queries into one. Once i've combined those queries, I want to
count the number of cqids that appear. I can use COUNT and a subquery, but i
can't use COUNT and UNION in a subquery. Or maybe I just can't use UNION in a
subquery. Either way, i have this big, complex query that i don't want to
start over on.

Any ideas?
.

Relevant Pages

  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)
  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... Using my query names, can you say exactly what my SQL should be ... > my Union Queries short and simple and do the rest of the work elsewhere. ... >>> Subject and Professional Mentors that are allocated to placements. ...
    (microsoft.public.access.queries)
  • Re: Question about Queries with Subqueries
    ... performance reasons to use UNION ALL instead of UNION. ... i have a query i am working on that draws data from 3 different data ... i have a question about queries like this. ... to try "flattening" my queries by using subqueries. ...
    (microsoft.public.access.queries)
  • Re: Help! Union Query has started crashing!
    ... It's tblSupport on the RLR_SUPPORT_INFOTERRA.mdb database. ... I think I have tracked the problem down to the query ... I also tried a UNION ALL, ... the actual structure of the queries as they have been running fine for weeks. ...
    (microsoft.public.access.queries)
  • Re: Change of field name causes application to crash
    ... this all kind of got me searching through all the underlying queries ... have a six-way union query separated from the query in question by an ... Here is the six-way union query: ... I'm assuming that LookupInventoryCombinedNet is the UNION query you ...
    (microsoft.public.access.modulesdaovba)