getting fewer records from a union query than in individual table

[JeffL]

I have a union query set up, and I end up with FEWER records than one of the
individual tables it is composed of, which, I think, should NEVER happen.
What am I doing wrong?

SELECT Table1.RECORDNumberItem, Table2.RECORDITEM,
Table1!YTDCIRC+Table1!TOTRENEW-Table2!YTDCIRC-Table2!TOTRENEW AS [MONTH
CIRC], Mid(Table1!ILOC,1,2) AS LOC, Mid(Table1!ILOC,3,3) AS CAT
FROM Table1 INNER JOIN Table2 ON Table1.RECORDNumberItem = Table2.RECORDITEM

UNION SELECT Table3.RECORDNumberItem, "x" AS RECORDITEM,
[Table3]![YTDCIRC]+[Table3]![TOTRENEW] AS [MONTH CIRC],
Mid([Table3]![ILOC],1,2) AS LOC, Mid([Table3]![ILOC],3,3) AS CAT
FROM Table3, Table1 INNER JOIN Table2 ON Table1.RECORDNumberItem =
Table2.RECORDITEM;

 

[John W. Vinson]

I have a union query set up, and I end up with FEWER records than one of the
individual tables it is composed of, which, I think, should NEVER happen.
What am I doing wrong?

Try changing UNION to UNION ALL. A UNION query will remove all duplicates
amongst the fields included in its component SELECT clauses. You're probably
seeing the result of this purging of dups.

 

[Bob Barrows]

I have a union query set up, and I end up with FEWER records than one
of the individual tables it is composed of, which, I think, should
NEVER happen. What am I doing wrong?

SELECT Table1.RECORDNumberItem, Table2.RECORDITEM,
Table1!YTDCIRC+Table1!TOTRENEW-Table2!YTDCIRC-Table2!TOTRENEW AS
[MONTH CIRC], Mid(Table1!ILOC,1,2) AS LOC, Mid(Table1!ILOC,3,3) AS CAT
FROM Table1 INNER JOIN Table2 ON Table1.RECORDNumberItem =
Table2.RECORDITEM

UNION SELECT Table3.RECORDNumberItem, "x" AS RECORDITEM,
[Table3]![YTDCIRC]+[Table3]![TOTRENEW] AS [MONTH CIRC],
Mid([Table3]![ILOC],1,2) AS LOC, Mid([Table3]![ILOC],3,3) AS CAT
FROM Table3, Table1 INNER JOIN Table2 ON Table1.RECORDNumberItem =
Table2.RECORDITEM;

Run each query separately. How many records does each return?
You do realize that using UNION without ALL causes the query engine to
eliminate duplicate records ... If you want all the records, duplicate or
not, you have to use UNION ALL.

 

[JeffL]

query> Try changing UNION to UNION ALL. A UNION query will remove all
duplicates

amongst the fields included in its component SELECT clauses. You're probably
seeing the result of this purging of dups.

I mispoke earlier, instead of "individual table" I meant "individual query".
I definitely don't want the duplicates. Even if there are duplicates, if
query1 gives me 3.8 million records, shouldn't a union between query1 and
another query give at least 3.8 million records? I'm only ending up with 3.4
million in the union query.

 

[JeffL]

Run each query separately. How many records does each return?
You do realize that using UNION without ALL causes the query engine to
eliminate duplicate records ... If you want all the records, duplicate or
not, you have to use UNION ALL.

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

First, I should have written "individual query" instead of "individual
table" in my first post. Sorry if this muddled things.

I broke the query into two parts and the second query gave an "out of memory
on temporary table" error, so it looks like that's where the problem is.

I expected the result of the second part to be the same size as table 3,
about 140,000 records. The first part query results in 3.8 million records,
without a problem.

 

[JeffL]

It looks like I've got duplicate records within the first query, which I
totally didn't expect, but explains the problem. Thanks for the help.