missing records in Union Query

  • Thread starter Thread starter Jeff
  • Start date Start date
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
 
The only logical answer is that one or both of your source
queries has duplicates, which the union is removing.
 
Back
Top