Using 'AS' Statement in MS QUERY SQL with 'UNION ALL'

Question

My company has a dual company structure which requires that data be kept separate. We have two datasets which are identical in structure. I commonly use MS Query to write SQL using one dataset, and when I have what I want, I simply add a UNION ALL statement and repeat the SQL but replace any dataset names with the second one. It always works fine tp give me a single query combining the data from both datasets.

However, I am retrieving some date fields using the datepart function to get the year and month of the records, and I use the AS statement to name the columns. Example: Select datepart(yyyy,receiptdate) as "Year"

While it works in the original query with one dataset, when I create the 'UNION ALL' and add the additional SQL, the column name becomes blank. The same goes for some fields that I am summarizing and renaming to have a more concise name than SUM(QUANTITY_ORDERED) -- they turn blank after the UNION ALL.

Is there a trick to renaming columns when using UNION ALL, or other suggestions?

Thanks, Mark

Answer 1

If you are referring to SQL SERVER (I'm honestly not sure what ms-query is), then it should be that your column aliases are done in the first statement of your union

For example the first example below leaves column name blank while the second example provides the correct column name;

-- Example 1
Select datepart(yyyy,CURRENT_TIMESTAMP) 
UNION ALL 
SELECT datepart(yyyy,DATEADD(YY, -1, CURRENT_TIMESTAMP)) as "Year"

-- Example 2
Select datepart(yyyy,CURRENT_TIMESTAMP) as "Year"
UNION ALL 
SELECT datepart(yyyy,DATEADD(YY, -1, CURRENT_TIMESTAMP))