CrossTab Problems

[drgoodvibe]

Thank you, I'm making a little headway here as I can get the following code to return the first column for "ChartSales " correctly. Now I'm trying to add 2 more columns that sum different product sales.

The working portion of the code for 1 product is this so far. This gives the correct return.

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

Now i'm trying to add the additional 2 more columns for the other products and mucking it up, I'm not exactly sure how to add the second or third where statement.

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

**Admin Edit**
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 :

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

[NeoPa]

This is tricky.

The first post made sense. I felt the second post also made sense, and provided a viable solution to the question posed in the first. The third post makes no sense to me whatsoever. It neither asks another question, nor in any way modifies the earlier question (not in any way I can see at least). It seems to be reflecting some sort of progression on unspecified areas of your project, but expressed in such a way that is not clear what you're trying to achieve even.

I can't see anything I can do to help except reflect back to you the situation as I see it and hope that you can somehow explain why the question isn't answered, or what you require further.

[drgoodvibe]

My apologies for making this overly complicated, what I'm trying to produce is the following chart. The code in post #3 gives me columns 1 and 2, I'm trying to add a 3rd and 4rth column that sums the Sales Person sales and groups it by product like in the 2nd column. however I can't seem to properly add the 3rd and 4rth columns.

Sales results by Sales Person by Product.

Sales_persons   Product1  Product2  Product3 
SalesRep1        $400       $600     0
SalesRep2        $700        0       $800
SaleRep3          0         0        $1000

[NeoPa]

That makes some sense when considered in isolation, but you still give no relationship to this and the question that this thread is based on (Post #1).

[drgoodvibe]

SalesRep table
------------
PersonName SalesPersonNumber


SalesOrders table
----------
SalesPersonNumber   Sales   ProductID

The chart would sum the total sales of each Sales Person and show only their Person Name and the total Sales for that individual by ProductID as per the chart in #5

Thank you for your help in advance, this has been a great learning experience.

[NeoPa]

I think I can sort of guess what you're talking about. The first question (the question for this thread) was about producing a dataset that could be used in the second question. The second question is quite a departure and needs to be split into a separate thread (I'll do that now for you).

The second question is about a cross-tab type query. I'm pretty inexperienced in such things so I'll simply point you in the direction and leave you to wait for some alternative help or play around with the query wizard and see what you can do for yourself with your new understanding.

Standard queries do not have columns based on the current data. This makes Cross-Tab queries somewhat different.