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.