Union queries

  • Thread starter Thread starter Oh Carla...
  • Start date Start date
O

Oh Carla...

I have a simple union query:

SELECT
Sum([Engr MH]) AS MECHENGRMH,
0 AS MISCENGRMH,
Sum([YardsConcrete]) AS MECHCONCRT,
0 AS MISCCONCRT,

From [qryComponentParts]
WHERE ((([qryComponentParts].[Miscellaneous])=0))

UNION SELECT
0 AS MECHENGRMH,
Sum([Engr MH]) AS MISCENGRMH,
0 AS MECHCONCRT,
Sum([YardsConcrete]) AS MISCCONCRT,

From [qryComponentParts]
WHERE ((qryComponentParts.Miscellaneous)=-1);

It works just fine when I have items that are equal (three
items where miscellaneous is equal to 0, two items where
miscellaneous equal to -1). The first line will be those
items equal to 0 and the second line will be those items
equal to -1.

The problem comes when one of the two sets is null. For
example, if I have no miscellaneous that is equal to -1 (a
null set), that line becomes the first line of the result
instead of the second. Any reason behind that?
 
Oh Carla,

It seems to me that you have answered your own question. If there are
no matching records, there are no matching records. What are you expecting?
 
Look at the query again. The first line is miscellaneous
equal to 0. If I have a set that has miscellaneous equal
to 0 but nothing in that set is equal to -1, I expect the
result to show the first line of the query equal to 0 and
the second line as the null set, as the query is written.
This is not the case I am running up against. Access is
making the null value the first line, regardless of how the
query is written.


-----Original Message-----
Oh Carla,

It seems to me that you have answered your own question. If there are
no matching records, there are no matching records. What are you expecting?

--
Steve Schapel, Microsoft Access MVP

I have a simple union query:

SELECT
Sum([Engr MH]) AS MECHENGRMH,
0 AS MISCENGRMH,
Sum([YardsConcrete]) AS MECHCONCRT,
0 AS MISCCONCRT,

From [qryComponentParts]
WHERE ((([qryComponentParts].[Miscellaneous])=0))

UNION SELECT
0 AS MECHENGRMH,
Sum([Engr MH]) AS MISCENGRMH,
0 AS MECHCONCRT,
Sum([YardsConcrete]) AS MISCCONCRT,

From [qryComponentParts]
WHERE ((qryComponentParts.Miscellaneous)=-1);

It works just fine when I have items that are equal (three
items where miscellaneous is equal to 0, two items where
miscellaneous equal to -1). The first line will be those
items equal to 0 and the second line will be those items
equal to -1.

The problem comes when one of the two sets is null. For
example, if I have no miscellaneous that is equal to -1 (a
null set), that line becomes the first line of the result
instead of the second. Any reason behind that?
Click to expand...
.
Click to expand...
 
Oh Carla,

Thanks a lot for the further explanation. Understandably, I missed your
meaning in your earlier post.

In Access, data in tables and queries is essentially unordered. If you
need to control the order of the records returned by your query, you
will need to put an ORDER BY clause in there, as applicable.
 
Access is
making the null value the first line, regardless of how the
query is written.
Click to expand...

You don't have an Order By in the second UNION query, hence the order
of records is unpredictable and uncontrolled. Table have no order; and
(without a specific sort instruction) queries will return records in
whatever order is convenient for the query optimizer.
 
Back
Top