Inconsistent Behaviour of Union Select Query

Inconsistent Behaviour of Union Select Query

Post by Doug Robbi » Fri, 09 Sep 2005 19:34:34


Using Access 2003, I have a Union Select query to combine information from
two separate tables. In one of the tables there are two records that in one
field have the same value that happens to be the number 10,000. The Union
Select query ignores one of these records. However, if I change the value
of the entry in that field for the record that is ignored to 10,000.01, the
record is then included in the result of the Union Select Query.

Can anyone explain why the record would be ignored when it contains the
value of 10,000?

--
Thanks and regards,

Doug Robbins - Word MVP
 
 
 

Inconsistent Behaviour of Union Select Query

Post by Allen Brow » Fri, 09 Sep 2005 20:20:27

Hi Doug. Good to hear from you in this neck of the woods. :-)

By default, a UNION query will de-duplicate.
To get all records, use UNION ALL, e.g.

SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://www.yqcomputer.com/
Reply to group, rather than allenbrowne at mvps dot org.

 
 
 

Inconsistent Behaviour of Union Select Query

Post by Olli Kinnu » Fri, 09 Sep 2005 20:23:59


UNION is distinct, use UNION ALL

Olli
 
 
 

Inconsistent Behaviour of Union Select Query

Post by Doug Robbi » Fri, 09 Sep 2005 20:39:09

Thanks.

Live and Learn.

--


Doug Robbins - Word MVP
 
 
 

Inconsistent Behaviour of Union Select Query

Post by Doug Robbi » Fri, 09 Sep 2005 20:39:34

Thanks, Allen.

Live and Learn.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP