Union query is bringing fewer results that each individual query

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

Guest

Hi,
I have two queries. The first brings back 76,069 records and the second
brings back 4,954 records. When i combine the two in a union query I was
expecting them to bring back 81,023 which is the combined total but it brings
back 2,586. We have a group of clients that get a different set of pricing.
The first query excludes these clients and then totals things such as
quantity and amount for the year. The second query only has these clients.
Can someone help?

Thanks,

SELECT QTYCurrentYear1.Description, QTYCurrentYear1.Quantity,
QTYCurrentYear1.Amount, QTYCurrentYear1.TotalCost, QTYCurrentYear1.TotalMargin
FROM QTYCurrentYear1


UNION SELECT QTYCurrentYear1Dist.Description, QTYCurrentYear1Dist.Quantity,
QTYCurrentYear1Dist.Amount, QTYCurrentYear1Dist.TotalCost,
QTYCurrentYear1Dist.TotalMargin
FROM QTYCurrentYear1Dist;
 
Try adding ALL to the second one ---
UNION ALL SELECT QTYCurrentYear1Dist.Description, .....
 
Try using UNION ALL to avoid removing the records that are exact duplicates.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top