Try this UNTESTED --
carl_Crosstab_1 ----
SELECT carl.Product, SalesDate, Sum(carl.Sales) AS [Total Of Sales]
FROM carl
GROUP BY carl.Product, carl.SalesDate;
TRANSFORM Sum([Sales]/[Total Of Sales]) AS Expr1
SELECT carl.SalesDate, carl.Product
FROM carl INNER JOIN carl_Crosstab_1 ON carl.Product = carl_Crosstab_1.Product
GROUP BY carl.SalesDate, carl.Product
PIVOT carl.SalesPerson;
TRANSFORM Sum([Sales]/[Total Of Sales]) AS Expr1
SELECT carl.SalesPerson, carl.Product
FROM carl INNER JOIN carl_Crosstab_1 ON carl.Product = carl_Crosstab_1.Product
GROUP BY carl.SalesPerson, carl.Product
PIVOT carl.SalesDate;
--
KARL DEWEY
Build a little - Test a little
carl said:
Thanks Karl.
I have another variation.
My data records also contain the date.
For a given SalesPerson, I am trying to get the percentage sales for each
product for each date.
For example, for SalesPerson Steve, output would look something like this:
Steve
19-Aug 20-Aug
ABC 29% 22%
DEF 14% 10%
Is this possible ?
KARL DEWEY said:
Use two queries ---
carl_Crosstab_1 ----
SELECT carl.Product, Sum(carl.Sales) AS [Total Of Sales]
FROM carl
GROUP BY carl.Product;
TRANSFORM Sum([Sales]/[Total Of Sales]) AS Expr1
SELECT carl.Product
FROM carl INNER JOIN carl_Crosstab_1 ON carl.Product = carl_Crosstab_1.Product
GROUP BY carl.Product
PIVOT carl.SalesPerson;
--
KARL DEWEY
Build a little - Test a little
carl said:
My data records are like this:
Product Sales SalesPerson
ABC 10 Steve
ABC 1 Jim
ABC 3 Mike
ABC 4 Carol
ABC 8 Todd
ABC 9 Pat
DEF 22 Steve
DEF 11 Jim
DEF 7 Mike
DEF 6 Carol
DEF 90 Todd
DEF 21 Pat
I need a query that will show me the percentage each saleperson sold of each
product.
I am trying to get output like this:
Steve Jim Mike Carol Todd Pat
ABC 29% 3% 9% 11% 23% 26%
DEF 14% 7% 4% 4% 57% 13%
Thank you in advance.