missing records in Union Query

J

Jeff

I have two queries contructed called "AllBuybacks-
SpecialCharges" and "AllSpecialCharges-Buybacks". They
have the same column headings arranged in the same order.
The first query contains all Buyback records and those
Special Charges that match. The second query is the
opposite--containing all Special Charges records and only
the Buybacks that match. Now I want to combine the two
using a union query. The "AllBuybacks-SpecialCharges"
query has 32 records, and the "AllSpecialCharges-Buybacks"
has 840 records. When doing a Union All query I get 872
records as expected, with 17 duplicates. This tells me
that a regular union query that takes out duplicates
should result in 855 records(872-17). However when I
replace "UNION ALL SELECT" with "UNION SELECT" I only get
747 records. How can this be? I'm new to union queries
but it seems to me that you should never get less records
form a union query than the origianal query or table with
the most records. Here is my union query:

SELECT [CX_CUST_SHIPTO_ID], [SumOfBUYBACKS],
[tblBuybacksMYr.MONTH], [tblBuybacksMYr.YEAR],
[SumOfSPECIAL_CHARGES], [tblSpecialChargesMYr.MONTH],
[tblSpecialChargesMYr.YEAR]
FROM [qryAllSpecialCharges-Buybacks]
UNION
SELECT [CX_CUST_SHIPTO_ID], [SumOfBUYBACKS],
[tblBuybacksMYr.MONTH], [tblBuybacksMYr.YEAR],
[SumOfSPECIAL_CHARGES], [tblSpecialChargesMYr.MONTH],
[tblSpecialChargesMYr.YEAR]
FROM [qryAllBuybacks-SpecialCharges];

This has been frustrating. Any help would be appreciated.

Thanks,

Jeff
 
C

ChrisJ

The only logical answer is that one or both of your source
queries has duplicates, which the union is removing.
 
Top