Problem with the characters of query when alias column

[CuongPham]

Hi everyone.

I have a query: (Test with database NorthWind)

SELECT Employees.FirstName, SUM([ABC].[1994]) AS ['1994'], SUM([ABC].[1995]) AS ['1995'], SUM([ABC].[1996]) AS ['1996'], SUM(([ABC].[1994]) + ([ABC].[1995]) + ([ABC].[1996])) AS ToTalAmount
FROM Employees LEFT JOIN (SELECT Employees.EmployeeID
,YEAR(Orders.OrderDate) AS [YEAR]
,Switch (YEAR(Orders.OrderDate) = 1994, SUM([Order Details].Quantity)) AS '1994'
,Switch(YEAR(Orders.OrderDate) = 1995, SUM([Order Details].Quantity)) AS '1995'
,Switch (YEAR(Orders.OrderDate) = 1996, SUM([Order Details].Quantity)) AS '1996'
,SUM([Order Details].Quantity) AS Total
FROM (Orders
LEFT JOIN [Order Details] ON [Order Details].OrderID = Orders.OrderID)
LEFT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
GROUP BY Employees.EmployeeID,YEAR(Orders.OrderDate)) AS ABC ON Employees.EmployeeID = [ABC].EmployeeID
GROUP BY Employees.FirstName;

When I run query. Instead of, it will show data:

FirtName 1994 1995 1996 TotalAmount
Andrew 766 2748 2541 6055
Anne 550 898 1222 2670
Janet 840 3853 3159 7852
Laura 617 2947 2349 5913
Margaret 1836 5086 2876 9798
Michael 623 1786 1118 3527
Nancy 1084 3830 2898 7812
Robert 485 2243 1926 4654
Steven 580 1616 840 3036

But It shows dialog because Access understood column [] as a parameter.
How can fix it ?

Thanks everyone very much.

[June7]

The [] are not issue. Access can't find a field. Which field triggers the popup?

Why are you using apostrophes in field names?

Don't use Year as field name. Year is a reserved word.

[CuongPham]

Thanks June7 very much.
But I want to print 4 columns which will show data as above. I run them in SQL, It's nice...but in Access
How can I fix it ???

[June7]

Which field triggers the popup?

Try:

,Switch(YEAR(Orders.OrderDate) = 1994, SUM([Order Details].Quantity)) AS [1994]
,Switch(YEAR(Orders.OrderDate) = 1995, SUM([Order Details].Quantity)) AS [1995]
,Switch(YEAR(Orders.OrderDate) = 1996, SUM([Order Details].Quantity)) AS [1996]

[CuongPham]

Wow... It's very nice
Thanks June7 very very much

Ah TotalAmount which dont show data..Can you fix it ??? :|

[June7]

If any term is null the result will be null because arithmetic with null returns null. Handle possible null.

SUM(Nz([ABC].[1994],0) + Nz([ABC].[1995],0) + Nz([ABC].[1996],0))

[CuongPham]

Ok...I fixed it with IIF
thanks June7 very very much
Best Regards