Can anyone give any guidance on how 1

[Cage]

Can anyone give any guidance on how to use a weighted function using it for the following:

I have the following tables:

Table 1

SalesPerson Company Method
Blue A Phone
Blue A Email
Blue B Email
Blue B Email
Blue C Phone
Blue C Meeting
Blue C Letter
Red A Phone
Red B Phone
Red B Meeting
Red C Phone
Red C Letter
Red C Letter
Red C Email


Table 2
Method Points
Phone 10
Email 5
Letter 50
Meeting 100

I can create a cross tab to give the following

SalesPerson Company PointsTotal Phone Email Letter Meeting
Blue A 15 10 5
Blue B 10 10
Blue C 160 10 50 100
Red A 10 10
Red B 110 10 100
Red C 115 10 5 100


But what I really want is the weighted Points expressed as a per SalesPerson Total points e.g

SalesPerson Company PointsTotal %perSalesPersonTotal
Blue A 15 8.1%
Blue B 10 5.4%
Blue C 160 86.5%
Red A 10 4.3%
Red B 110 46.8%
Red C 115 48.9%

e.g the %perSalesPersonTotal add to 100%. In this example there are only two salesPersons, however in reality there are numerous SalesPersons each with numerous Companies.

Does anyone know if this is even possible (in either Access or SQL) and maybe suggest an alternative function if weighted is not suitable.

TIA

Cage

 

[Remou]

Perhaps:
[tt]SELECT TableX.SalesPerson,
TableX.Company,
Sum(TableZ.Points) AS SumOfPoints,
(SELECT Sum(B.Points) AS Expr1
FROM TableX A INNER JOIN TableZ B ON A.Method = B.Method
WHERE A.SalesPerson = TableX.SalesPerson) AS PointsTotal,
[sumofpoints]/([pointstotal]/100) AS PointsPercent
FROM TableX INNER JOIN TableZ ON TableX.Method = TableZ.Method
GROUP BY TableX.SalesPerson, TableX.Company;[/tt]

 

[Cage]

Great thats was just what I was looking for. thanks.