S
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.
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.