Need Query to perform calculation based on results

[bruce24444]

I have a working database that I designed a couple years ago which we use at work to keep track of the amount of claims that are assigned to each employee. What I am trying to do is now assign a factor to these claims based on complexity so that when I am assigning a new claim I can estimate the workload each employee has and not just how many claims.



Each type of loss has been assigned a factor and I created a crosstab query which produces a list of how many level 1 claims, level 2 claims ...... what I need now, is a total based on these results but this total is to be multiplied by the factor.
IE: Brad has 8 level 1 claims, 1 level 2 claim, 9 level 3 claims, 2 level 4 claims and 1 level 5 claim. I want a total to be 50. (8x1) + (1x2) + (9x3) + (2x4) + (1x5)

If any one can help, it would be greatly appreciated.

[June7]

Query1

SELECT Table1.FirstName, Table1.Factor, Sum(Table1.Factor) AS SumOfFactor
FROM Table1
GROUP BY Table1.FirstName, Table1.Factor;

Query2

TRANSFORM Sum(Query1.[SumOfFactor]) AS SumOfSumOfFactor
SELECT Query1.[FirstName]
FROM Query1
GROUP BY Query1.[FirstName]
PIVOT Query1.[Factor];

All-In-One

TRANSFORM Sum(Query1.[SumOfFactor]) AS SumOfSumOfFactor
SELECT Query1.[FirstName]
FROM (SELECT Table1.FirstName, Table1.Factor, Sum(Table1.Factor) AS SumOfFactor
FROM Table1
GROUP BY Table1.FirstName, Table1.Factor) AS Query1
GROUP BY Query1.[FirstName]
PIVOT Query1.[Factor];

[bruce24444]

Thanks for the quick response. Once I created Query 1, it returned the results I needed