UNION Query problem

[syed ripon via AccessMonster.com]

Hi There:
I am joining 2 queries with a UNION. I need the result in order of year. But
when I am doing a UNION, I am always ending up with an order based on value
of another column.
Q1:
SELECT Sum(TAB_US_RES.US_CAR_SD) AS Sum1, TAB_US_RES.YEAR, 1 AS SVAL FROM
TAB_US_RES
WHERE (((TAB_US_RES.YEAR)=2004 Or (TAB_US_RES.YEAR)=2005) AND MONTH=10)
GROUP BY TAB_US_RES.YEAR
---The result is OK
SUM1 YEAR SVAL
700 2004 1
500 2005 1

Q2:
SELECT Sum(TAB_US_RES.US_CAR_SD) AS Sum1, TAB_US_RES.YEAR, 1 AS SVAL FROM
TAB_US_RES
WHERE (((TAB_US_RES.YEAR)=2004 Or (TAB_US_RES.YEAR)=2005) AND MONTH <=10)
GROUP BY TAB_US_RES.YEAR
---The result is OK
SUM1 YEAR SVAL
1000 2004 2
1400 2005 2
----But when I am joining them by a UNION the data are sorting by Sum1 with
no order of YEAR.
SELECT Sum(TAB_US_RES.US_CAR_SD) AS Sum1, TAB_US_RES.YEAR, 1 AS SVAL FROM
TAB_US_RES
WHERE (((TAB_US_RES.YEAR)=2004 Or (TAB_US_RES.YEAR)=2005) AND MONTH=10)
GROUP BY TAB_US_RES.YEAR
UNION
SELECT Sum(TAB_US_RES.US_CAR_SD) AS SumOfUS_CAR_SD, TAB_US_RES.YEAR, 2 AS
SVAL FROM TAB_US_RES
WHERE (((TAB_US_RES.YEAR)=2004 Or (TAB_US_RES.YEAR)=2005) AND MONTH <=10)
GROUP BY TAB_US_RES.YEAR
ORDER BY SVAL;
--Result is NOT ok (No order of Year)
500 2005 1
700 2004 1
1000 2004 2
1400 2005 2

---I want :
SUM1 YEAR SVAL
700 2004 1
500 2005 1
1000 2004 2
1400 2005 2

--I would request anyone to help me finding a solution. Thanks in advance.

 

[Jeff Boyce]

I only saw one variable included in your ORDER BY clause. Wouldn't you need
to include YEAR in there too?

By the way, Access treats "Year" as a reserved word. If that is the actual
name of your field ("Name" is a reserved word, too!), you may be confusing
Access about which "year" you are referring to.

 

[syed ripon via AccessMonster.com]

Hi Jeff:
I have a prob. The year is coming from 3different tables in my real query.
Say tab1.year,tab2.year and tab3.year. When I am joining 2 queries based on
same table(say tab1) UNION gives be correct result for ORDER BY SVAL, tab1.
year.
But when I am joing 3 queries from 3 different tables, Can I put at the end:
ORDER BY SVAL,tab1.year
--Can I just put ORDER BY SVAL,tab1.year at the end. Is it OK
---------------------------------------------

I only saw one variable included in your ORDER BY clause. Wouldn't you need
to include YEAR in there too?

By the way, Access treats "Year" as a reserved word. If that is the actual
name of your field ("Name" is a reserved word, too!), you may be confusing
Access about which "year" you are referring to.

Hi There:
I am joining 2 queries with a UNION. I need the result in order of year. But

[quoted text clipped - 45 lines]

--I would request anyone to help me finding a solution. Thanks in advance.

 

[Jeff Boyce]

If you use a UNION query, you will need a "year" field in each of the SELECT
clauses. A UNION tries to line up what it assumes are "identical" fields
from two/more SELECT clauses. If I recall correctly, you wouldn't have to
specify which table the "year" came from.

You might want to read up on how the UNION query works -- check the Access
HELP listings.

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi Jeff:
I have a prob. The year is coming from 3different tables in my real query.
Say tab1.year,tab2.year and tab3.year. When I am joining 2 queries based on
same table(say tab1) UNION gives be correct result for ORDER BY SVAL, tab1.
year.
But when I am joing 3 queries from 3 different tables, Can I put at the end:
ORDER BY SVAL,tab1.year
--Can I just put ORDER BY SVAL,tab1.year at the end. Is it OK
---------------------------------------------

I only saw one variable included in your ORDER BY clause. Wouldn't you need
to include YEAR in there too?

By the way, Access treats "Year" as a reserved word. If that is the actual
name of your field ("Name" is a reserved word, too!), you may be confusing
Access about which "year" you are referring to.

Hi There:
I am joining 2 queries with a UNION. I need the result in order of

year. But
[quoted text clipped - 45 lines]

--I would request anyone to help me finding a solution. Thanks in

advance.

 

[John Spencer]

When you union queries, the first query determines the field names which you
use in the SORT BY clause in the last query. Sort By does not have any
effect unless it is at the end of the UNION.

so your order by clause should probably read
ORDER BY SVAL, [Year]

Hi Jeff:
I have a prob. The year is coming from 3different tables in my real
query.
Say tab1.year,tab2.year and tab3.year. When I am joining 2 queries based
on
same table(say tab1) UNION gives be correct result for ORDER BY SVAL,
tab1.
year.
But when I am joing 3 queries from 3 different tables, Can I put at the
end:
ORDER BY SVAL,tab1.year
--Can I just put ORDER BY SVAL,tab1.year at the end. Is it OK
---------------------------------------------

I only saw one variable included in your ORDER BY clause. Wouldn't you
need
to include YEAR in there too?

By the way, Access treats "Year" as a reserved word. If that is the
actual
name of your field ("Name" is a reserved word, too!), you may be confusing
Access about which "year" you are referring to.

Hi There:
I am joining 2 queries with a UNION. I need the result in order of year.
But

[quoted text clipped - 45 lines]

--I would request anyone to help me finding a solution. Thanks in
advance.