SQL Jet Access, Insert Column and Order by

Question

I use Microsoft.ACE.OLEDB.12.0 connection via excel to do an internal query within my workbook.

How can I insert a column Year with the years I have in my query and order them so they are not together. So they will repeat one after the other?

The Year Column added I have, but now ordering is the issue.

Here is my current query

select [Data Type], [Currency], [Book Name], [1987] as Value
from [stage2$]
UNION ALL
select [Data Type], [Currency], [Book Name], [1990]
from [stage2$]

Current Data:

+--------------------------------+----------+-------+---------------+
|           Data Type            | Currency | Name  |   value       |
+--------------------------------+----------+-------+--------------
| Missing Non Historical Results | BSD      | Carib | (279)         |
| Missing Non Historical Results | BSD      | Carib |   18          |
| Missing Non Historical Results | BSD      | Carib |   898         |
| Missing Non Historical Results | BSD      | Carib |  (50)         |
+--------------------------------+----------+-------+---------------+

Expected result:

+--------------------------------+----------+-------+---------------+---------+
|           Data Type            | Currency | Name  | Year          |  value  |
+--------------------------------+----------+-------+---------------+---------+
| Missing Non Historical Results | BSD      | Carib |          1987 |  (279)  |
| Missing Non Historical Results | BSD      | Carib |          1990 |  898    |
| Missing Non Historical Results | BSD      | Carib |          1987 |  18     |
| Missing Non Historical Results | BSD      | Carib |          1990 |  (50)   |
+--------------------------------+----------+-------+---------------+---------+

Answer 1

You can just add another column to your select statement:

select [Data Type], [Currency], [Book Name], '1987' as Year, [1987] as Value
from [stage2$]
UNION ALL
select [Data Type], [Currency], [Book Name], '1990' as Year, [1990]
from [stage2$]
order by Year