crosstab query calculation problem

[mikarsen]

I have a problem with crosstab calculation, hope someone could help me. Here's my main table:

Office -Project -Performance
Balagtas -OSA -90
Balagtas -Billing Complaints -95
Balagtas -Payments -85
Malolos -OSA -89
Malolos -Billing Complaints -92
Malolos -Payments -90
Balintawak -OSA -95
Balintawak -Billing Complaints -100
Balintawak -Payments -20

I made a crosstab query to show this data:

Office -Billing Complaints -OSA -Payments
Balagtas -95 -90 -85
Balintawak -100 -95 -20
Malolos -92 - 89 -90

btw, here's the sql of this crosstab query:
TRANSFORM First(Table1.Performance) AS FirstOfPerformance
SELECT Table1.Office
FROM Table1
GROUP BY Table1.Office
PIVOT Table1.Project;


Now here's my objective:
I want to add another column to compute the final performance given the equivalent weights for each project
OSA = 50%
Billing Complaints = 30%
Payments = 20%

So I made another Row Heading - Expression and used the following formula
FinalPerformance:[OSA] * .5 + [Billing Complaints] *.3 + [Payments] *.2

but when I ran the query, access prompts:

"The Microsoft Jet database engine does not recognize '[OSA]' as a valid field name or expression"

Advance thanks to someone who could help me with this...=(

 

[namliam]

2 ways to do this:
1)
Make a query on top of the crosstab do it there, the same way you already did.

2)
- make an additional "record" before the crosstab
Select Office, 'Weighted' as Project, iif([Project]="OSA",0.5, iif(project="Billing Complaints", 0.3, 0.2)) as Performance
from table1

Save this query as qryWeighted

- Now make an additional union query:
Select * from table1
Union
Select * from qryWeighted

Save this query as qryUnion

- Now make a 'normal' crosstab on qryUnion.

I would guess that these projects can expand at some time, or change, so make a additional table to store you weighted values in as well.