Combining Queries

[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!

 

[Lynn Trapp]

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;

 

[KARL DEWEY]

Combine the two queries into a union query and follow with a totals query.

 

[James A. Fortune]

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.

That's excellent advice.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

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.

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)