Combining Queries

  • Thread starter Thread starter LG
  • Start date Start date
L

LG

I have 2 queries from 2 different tables asking for the same information.
The fields are Date, LName, Batch_ID, and CountBatch_ID. The 2 tables are
different TBL_ALL and TBL_Com.
The query provides each batch number and how many items were completed in
each batch. How would I go about showing all the batches from both queries
and combine the number from both queries.
Thank you for any help!
 
You have to use the SQL window and write something like this:

Select Date, LName, Batch_ID, and CountBatch_ID
from TBL_ALL
UNION
Select Date, LName, Batch_ID, and CountBatch_ID
from TBL_Com;
 
KenSheridan said:
2. A UNION ALL operation returns all rows from both parts of the operation,
whereas a UNION operation suppresses duplicate rows. Unless there is a
specific need to suppress duplicates use a UNION ALL operation, even if no
duplicate rows are possible, as it performs faster.
Click to expand...

That's excellent advice.

James A. Fortune
(e-mail address removed)
 
KenSheridan said:
You modestly don't mention that it was you who reminded me a week or two back
of the advantage of a UNION ALL operation over a UNION operation wherever
possible.
Click to expand...

I believe you are referring to:

http://groups.google.com/group/microsoft.public.access/msg/ff4e2a0a48264c72

That post might have reminded you of what you already knew, but I made
no mention of SELECT ALL in any post in this NG within the past several
weeks. Your advice seems to be a logical extention of the original
idea, abstracting it into an elegant general principle. I take little
credit for my part in it, nor for being modest :-). It's more like,
"Doh!, I should have been able to put it all together like that."

James A. Fortune
(e-mail address removed)
 
Back
Top