Union Query using AS creates issues with ORDER BY in SQL

Question

I am outputting a report for another department and they require specific headers (Excel cell column headers). I have a union query to output the information.

All of it works fine except the ORDER BY section.

If I use the full tblInventory.[Employee Number] AS [Employee No], I get a "Missing Operator" error and it highlights the AS.

If you just put ORDER BY [Employee No] it has problems with the DISTINCT claus which I need.

Any ideas on what operator it needs or how I can get this to sort?

SELECT DISTINCT tblinventory.[Phone Number] AS [Wireless No],
                tblemployeelist.[Employee Number] AS [Employee No],
                tblemployeelist.[Payroll    First Name] AS [First Name],
                tblemployeelist.[Payroll Last Name] AS [Last    Name],
                tblvendors.[Vendor Name] AS [Wireless Carrier],
                "Company" AS [Acct    Liability]
FROM tblvendors
INNER JOIN (tblemployeelist
            INNER JOIN tblinventory ON tblemployeelist.[Employee Number] = tblinventory.[Employee Number])
AND (tblemployeelist.[Employee Number] = tblinventory.[Employee Number])) ON tblvendors.id = tblinventory.carrier
WHERE (((tblinventory.[Phone Number]) IS NOT NULL)
       AND ((tblvendors.[Vendor    Name]) <>"Roadpost"
            AND (tblvendors.[Vendor Name]) <>"LIVETV Airfone Inc.")
       AND ((tblinventory.[Asset Description]) LIKE "*" & "phone" & "*")
       AND ((tblinventory.disposition) =2)
       AND ((tblinventory.spare) =FALSE)
       AND ((tblemployeelist.[End Date]) NOT LIKE "*"))
ORDER BY ([tblEmployeeList].[Employee Number] AS [Employee No])
UNION
SELECT tblmcpcollated.[Phone Number] AS [Wireless No],
       tblemployeelist.[Employee Number] AS [Employee No],
       tblemployeelist.[Payroll    First Name] AS [First Name],
       tblemployeelist.[Payroll Last Name] AS [Last    Name],
       tblvendors.[Vendor Name] AS [Wireless Carrier],
       "Employee" AS [Acct    Liability]
FROM tblvendors
INNER JOIN (tblemployeelist
            INNER JOIN tblmcpcollated ON tblemployeelist.[Employee Number] = tblmcpcollated.[Employee Number]) ON tblvendors.id = tblmcpcollated.vendor
WHERE (((tblmcpcollated.[Phone Number]) IS NOT NULL)
       AND ((tblmcpcollated.status)="Active")
       AND ((tblmcpcollated.[MCP Program])<>1)
       AND ((tblmcpcollated.[Compensation Amt])>0)
       AND ((tblemployeelist.[End    Date]) NOT LIKE "*"))
  OR (((tblmcpcollated.[Phone Number]) IS NOT NULL)
      AND ((tblmcpcollated.status)="Pending")
      AND ((tblmcpcollated.[MCP Program])<>1)
      AND ((tblmcpcollated.[Compensation Amt])>0)
      AND ((tblemployeelist.[End    Date]) NOT LIKE "*"))
ORDER BY ([tblEmployeeList].[Employee Number] AS [Employee No]);

If I remove the ORDER BY, everything works. I just would like the sort function in there.

Thanks in advance for your awesome knowledge.

Answer 1

You need to remove the column name from the ORDER BY. As you stated it's throwing an error around the keyword AS.

You need:

ORDER BY ([tblEmployeeList].[Employee Number])

not:

ORDER BY ([tblEmployeeList].[Employee Number] AS [Employee No])

Answer 2

As I'm not able de read your SQL, this should do it and will be transparent for the query builder :

SELECT * 
FROM
(
    SELECT DISTINCT tblInventory.[Phone Number] AS [Wireless No], 
    tblEmployeeList.[Employee Number] AS [Employee No], tblEmployeeList.[Payroll 
    First Name] AS [First Name], tblEmployeeList.[Payroll Last Name] AS [Last 
    Name], tblVendors.[Vendor Name] AS [Wireless Carrier], "Company" AS [Acct 
    Liability]
    FROM tblVendors INNER JOIN (tblEmployeeList INNER JOIN tblInventory ON    
    tblEmployeeList.[Employee Number] = tblInventory.[Employee Number]) AND 
    (tblEmployeeList.[Employee Number] = tblInventory.[Employee Number])) ON 
    tblVendors.ID = tblInventory.Carrier
    WHERE (((tblInventory.[Phone Number]) Is Not Null) AND ((tblVendors.[Vendor 
    Name])<>"Roadpost" And (tblVendors.[Vendor Name])<>"LIVETV Airfone Inc.") 
    AND ((tblInventory.[Asset Description]) Like "*" & "phone" & "*") AND 
    ((tblInventory.Disposition)=2) AND ((tblInventory.Spare)=False) AND 
    ((tblEmployeeList.[End Date]) Not Like "*"))
    ORDER BY ([tblEmployeeList].[Employee Number] AS [Employee No])
    UNION SELECT tblMCPCollated.[Phone Number] as [Wireless No], 
    tblEmployeeList.[Employee Number] as [Employee No], tblEmployeeList.[Payroll 
    First Name] as [First Name], tblEmployeeList.[Payroll Last Name] as [Last 
    Name], tblVendors.[Vendor Name] as [Wireless Carrier], "Employee" as [Acct 
    Liability]
    FROM tblVendors INNER JOIN (tblEmployeeList INNER JOIN tblMCPCollated ON 
    tblEmployeeList.[Employee Number] = tblMCPCollated.[Employee Number]) ON 
    tblVendors.ID = tblMCPCollated.Vendor
    WHERE (((tblMCPCollated.[Phone Number]) Is Not Null) AND 
    ((tblMCPCollated.Status)="Active") AND ((tblMCPCollated.[MCP Program])<>1) 
    AND ((tblMCPCollated.[Compensation Amt])>0) AND ((tblEmployeeList.[End 
    Date]) Not Like "*")) OR (((tblMCPCollated.[Phone Number]) Is Not Null) AND 
    ((tblMCPCollated.Status)="Pending") AND ((tblMCPCollated.[MCP Program])<>1) 
    AND ((tblMCPCollated.[Compensation Amt])>0) AND ((tblEmployeeList.[End 
    Date]) Not Like "*"))
) t1
ORDER BY t1.[Employee No];