Query to get output from 2 other queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi.

I have 2 queries: q1 and q2. They both produce output with the same format.

I want to build a third one (if this is the appropriate element) that
provides, as output, the output from q1 and q2, just one after another.

Is there any way to do this ?

Should I use another Access element ?

Any solutions and/or suggestion would be greatly appreciated.

Thanks a lot in advance.

Toto
 
Without more details, it sounds like you want a UNION query. Look in the
help fiels on UNION QUERY.

Rick B
 
Toto said:
Hi.

I have 2 queries: q1 and q2. They both produce output with the same format.

I want to build a third one (if this is the appropriate element) that
provides, as output, the output from q1 and q2, just one after another.

Is there any way to do this ?

Should I use another Access element ?

Any solutions and/or suggestion would be greatly appreciated.

Thanks a lot in advance.

Toto
Click to expand...

(SELECT * FROM q1, "q1" AS Sequence) UNION (SELECT * FROM q2, "q2" AS
Sequence) ORDER BY Sequence

Note that you cannot build or view a UNION query in the query design view:
you'll need to use the SQL view.
 
Thanks a lot, Rick, for your answer.

However, a UNION QUERY is exactly what I'm trying to avoid, since all I want
to do is APPEND the output of the second query after the output of the first
in one single output.

Toto
 
Brian, thanks for your answer.

I tried your suggestion on Access 2000 but keep on getting an error message
stating that the query is incomplete. Could it be possible ?

Toto
 
Toto said:
Brian, thanks for your answer.

I tried your suggestion on Access 2000 but keep on getting an error message
stating that the query is incomplete. Could it be possible ?

Toto
Click to expand...

Doh! My fault! Try this:

(SELECT *, "q1" AS Sequence FROM q1, ) UNION (SELECT *, "q2" AS Sequence
FROM q2) ORDER BY Sequence
 
Brian, thanks again.

I've got it now. This works just fine, but I'm trying to avoid the use of a
"union" operation, since the outputs from both q1 and q2 are pretty big and
then, this takes quite some time to run.

I'd just like to build one output from q1's and q2's, the second appended to
the first.

I'm not sure whether a query is the right construct to use in such a case.

Toto.
 
Toto said:
Brian, thanks again.

I've got it now. This works just fine, but I'm trying to avoid the use of a
"union" operation, since the outputs from both q1 and q2 are pretty big and
then, this takes quite some time to run.

I'd just like to build one output from q1's and q2's, the second appended to
the first.

I'm not sure whether a query is the right construct to use in such a case.

Toto.
Click to expand...

You've lost me completely now. As far as I can see, the output from the
UNION query is exactly what you are asking for. If not, then you are going
to have to explain better. As for it taking some time to run, that is down
to q1 and q2, and you have told us nothing about those.
 
What I have been doing is to create the first query (q1) as a maketable
(which does get replaced during the run of q1. Then I make q2 an APPEND
query.

When you run q1, it puts in the first set of data.
When you run q2, it just APPENDS the second set of data.

Now, if it is your intent to continuously append data, and not use q1 to
START the table over again, then I make both queries, APPEND type.

What is your intent with q1? Is it always to start the set of data?

MichaelM
 
Brian: My apologies to you, since you're right, my explanation was far from
complete.

As you state, the output from the union operation is exactly what I want.
However, what I want to avoid is the "cost" of that operation. I don't
consider myself an expert but, with this operation every record in the output
from the first query is matched against every record of the second one, am I
correct ?

Well, just to give you an idea of the sizes I'm talking about, the output
from q1 contains nearly 700,000 rows and the output from q2 something like
200 (perhaps a little too much to handle in one operation, but I'm only using
Access as a front-end, having all this information stored in another
database).

From the internals of q1 and q2 I'm completely sure that their outputs are
complementary, meaning that I'm sure that no record in q1's output will be in
q2's and viceversa.

Due to this reason I'd like to avoid to use the union operator, since all I
need is to "concatenate" the two outputs in one for presentation purposes. My
main problem with that is that Access doesn't seem to allow me to put more
than one SELECT statement in one query, so I haven't been able to do
something like:

select * from "q1's output"
select * from "q2's output"

and have this 2 outputs redirected to a third query's one.

Have I put it a bit more clear now ? I'm still looking for a solution to
this, so I'll be thankful for any ideas.

Thanks again.

Toto
 
Michael: thank you very much. That's been a very useful idea.

When you say that q1 "puts in the first of data" and that q2 "appends the
second" one, you mean they both write on a separate table, correct ? (I'm
assuming so, since you stated that q1 should be a make-table query).

The whole output (q1's + q2's) is generated from scratch every time they are
run, so the fact that q1 is a make-table query is just perfect.

What i'd like to do is to define 1 query (or some other Access element which
I'm not aware of) so that, when run, does it all together (run q1 and create
a table with it's output, run q2 and append it's output to the newly created
table, and show the contents of this new table).

Any idea on how can I (if I can) do something like this ?

Thanks in advance.

Toto
 
Try using UNION ALL vice UNION

A UNION ends up doing a distinct operation against the entire set of records. A
Union All does not do that.
 
John Spencer (MVP) said:
Try using UNION ALL vice UNION

A UNION ends up doing a distinct operation against the entire set of records. A
Union All does not do that.
Click to expand...

Yes, that's it. Please note though, Toto, that if the two queries return
some identical rows, then with UNION ALL the duplicates will appear in the
output.
 
Back
Top