The working portion of the code for 1 product is this so far. This gives the correct return.
Expand|Select|Wrap|Line Numbers
- SELECT sales_persons.Name, SUM(orders_table.Quantity) AS ChartSales
- INNER JOIN sales_persons
- ON sales_persons.PersonID = orders_table.PersonID
- WHERE orders_table.ProductID = 1
- GROUP BY sales_persons.Name
Expand|Select|Wrap|Line Numbers
- SELECT sales_persons.Name, SUM(orders_table.Quantity) AS ChartSales, SUM(orders_table.Quantity) AS GaugeSales, SUM(orders_table.Quantity) AS MAPSales
- FROM orders_table
- INNER JOIN sales_persons
- ON sales_persons.PersonID = orders_table.PersonID
- WHERE orders_table.ProductID = 1
- ??????????????
- ON sales_persons.PersonID = orders_table.PersonID
- WHERE orders_table.ProductID = 2
- ???????????????
- ON sales_persons.PersonID = orders_table.PersonID
- WHERE orders_table.ProductID = 3
- GROUP BY sales_persons.Name
This question was posted as a response to a previous thread SQL Query Showing Total Sales by Each Person and Each Product.
This starts from a position where the following SQL is available as the source for a CrossTab :
Expand|Select|Wrap|Line Numbers
- SELECT tSR.Name
- , tSR.Region
- , tP.Name
- , SUM(tS.TotalSales) AS TotalProdSales
- FROM ([Sales] AS tS INNER JOIN
- [SalesRep] AS tSR
- ON tS.PersonNum = tSR.PersonNum) INNER JOIN
- [Products] AS tP
- ON tS.ProductNum = tP.ProductNum
- GROUP BY tSR.Name
- , tSR.Region
- , tP.Name