Weird Behavior of UNION

  • Thread starter Thread starter Herbert Chan
  • Start date Start date
H

Herbert Chan

Hello,

I need to send some data out daily to all current construction projects and
I have recently collected all the emails for the projects. As the projects
come under different departments, I create one email table for each
department and there are 4 departments.

Then I create a query to collect all the distinct emails from the tables:

SELECT Email FROM CED WHERE Email IS NOT NULL
UNION SELECT Email FROM WSD WHERE Email IS NOT NULL
UNION SELECT Email FROM EMSD WHERE Email IS NOT NULL
UNION SELECT Email FROM HD WHERE Email IS NOT NULL;

Today I received a phone call from one of the projects from WSD and they
told me that their project was completed and they did not need to receive
the email any more. So I create a new field under WSD [Finished] and entered
YES for this project.

Then I amend the query to look like the following:

SELECT Email FROM CED WHERE Email IS NOT NULL
UNION SELECT Email FROM WSD WHERE Email IS NOT NULL AND Finished <> 'YES'
UNION SELECT Email FROM EMSD WHERE Email IS NOT NULL
UNION SELECT Email FROM HD WHERE Email IS NOT NULL;

I expect that only the project who has called me will be taken out of the
result returned. However, this is not the case. Around 20 emails were
removed from the results (the original returned number is aroun 150).

I was very puzzled. After some juggling, I changed the field to [Send] and
made all the ohter projects to be 'YES' while leaving it blank for the
project who has called me. The query was then changed to:

SELECT Email FROM CED WHERE Email IS NOT NULL
UNION SELECT Email FROM WSD WHERE Email IS NOT NULL AND Send = 'YES'
UNION SELECT Email FROM EMSD WHERE Email IS NOT NULL
UNION SELECT Email FROM HD WHERE Email IS NOT NULL;

This way, the query worked as expected and only that project was left out.

Could anyone generalize the behavior of UNION for me so that I can know what
to expect next time?? It's actually not that neat that I was required to
put a value to all the records who didn't actually need to be affected. My
first approach seemed to be most intuitive to me.

Herbert
 
The problem was with this test:

Finished <> 'YES'

You created a new "Finished" field and set one record to 'YES'. The problem
is this field in all the other rows had no value - actually, a special value
called NULL. A Null is an unknown value, and the rule is that any
comparison with a null value can never be true. So, your criteria did
remove the one row marked 'YES', but it also will not include any row that
had a Null in the Finished field. If you had created the field, run an
Update query to set all fields to No and then set the one record to Yes, it
would have worked just fine.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Herbert Chan said:
Hello,

I need to send some data out daily to all current construction projects and
I have recently collected all the emails for the projects. As the projects
come under different departments, I create one email table for each
department and there are 4 departments.

Then I create a query to collect all the distinct emails from the tables:

SELECT Email FROM CED WHERE Email IS NOT NULL
UNION SELECT Email FROM WSD WHERE Email IS NOT NULL
UNION SELECT Email FROM EMSD WHERE Email IS NOT NULL
UNION SELECT Email FROM HD WHERE Email IS NOT NULL;

Today I received a phone call from one of the projects from WSD and they
told me that their project was completed and they did not need to receive
the email any more. So I create a new field under WSD [Finished] and entered
YES for this project.

Then I amend the query to look like the following:

SELECT Email FROM CED WHERE Email IS NOT NULL
UNION SELECT Email FROM WSD WHERE Email IS NOT NULL AND Finished <> 'YES'
UNION SELECT Email FROM EMSD WHERE Email IS NOT NULL
UNION SELECT Email FROM HD WHERE Email IS NOT NULL;

I expect that only the project who has called me will be taken out of the
result returned. However, this is not the case. Around 20 emails were
removed from the results (the original returned number is aroun 150).

I was very puzzled. After some juggling, I changed the field to [Send] and
made all the ohter projects to be 'YES' while leaving it blank for the
project who has called me. The query was then changed to:

SELECT Email FROM CED WHERE Email IS NOT NULL
UNION SELECT Email FROM WSD WHERE Email IS NOT NULL AND Send = 'YES'
UNION SELECT Email FROM EMSD WHERE Email IS NOT NULL
UNION SELECT Email FROM HD WHERE Email IS NOT NULL;

This way, the query worked as expected and only that project was left out.

Could anyone generalize the behavior of UNION for me so that I can know what
to expect next time?? It's actually not that neat that I was required to
put a value to all the records who didn't actually need to be affected. My
first approach seemed to be most intuitive to me.

Herbert
Click to expand...
 
Back
Top