Chapter 9 Exercise 4

4. Create a cube to browse merchandise sales by time, state, employee, and item category.

Query:
SELECT City.State, Format([SaleDate],"yyyy-mm") AS SaleMonth, [Employee].[LastName] & ", " & [Employee].[FirstName] AS Salesperson, Merchandise.Category, Sum([Quantity]*[SalePrice]) AS [Value]
FROM (Employee INNER JOIN ((City INNER JOIN Customer ON City.CityID = Customer.CityID) INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID) ON Employee.EmployeeID = Sale.EmployeeID) INNER JOIN (Merchandise INNER JOIN SaleItem ON Merchandise.ItemID = SaleItem.ItemID) ON Sale.SaleID = SaleItem.SaleID
GROUP BY City.State, Format([SaleDate],"yyyy-mm"), [Employee].[LastName] & ", " & [Employee].[FirstName], Merchandise.Category;