Union query is bringing fewer results that each individual query

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;
 
G

Guest

Try adding ALL to the second one ---
UNION ALL SELECT QTYCurrentYear1Dist.Description, .....
 
J

John Spencer

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
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Losing Records Between Union Query and Select Query 3
getting fewer records from a union query than in individual table 5
UNION query Challenges 2
Union Query Access 2007 SP2 MSO 7
Union Query Truncates Results? 2
Union Query 1
union query of complex queries 1
Join on a UNION query 2

Top