interesting query problem

[linqlin]

The problem is i 'm doing CRU banding, because cru range
is from 1200 to 13000, so i need to give banding, for
example, (1200, 1400), (1401,1600), (1601,1800)....., it
can be done through pivot table if CRU is not the
calculated field. if CRU is a calculated item, we can't
put it into the row of pivot table and group it into
different band. so i need to put cru inside the table.
How to write the query so CRU can be aggregated? thanks.

____________________-
Why not point your pivot table straight at the table and
use a calculated field in the pivot?
cheers
Simon

-----Original Message-----
let us consider the following access table
mod Region rev qty
020 china 20 10
023 india 30 10
020 japan 40 8
020 china 10 2
(database table)

, and we define the query: SELECT [mod], Region, Sum
(rev) AS srev, Sum(qty) AS sqty, srev/sqty AS cru
FROM Table1 GROUP BY [mod], Region; we then use the pivot
table to get CRU , we get the following pivot table result:
regionAll)
mod 020
Data
sum of srev sum of sqty sum of CRU
total 70 20 7.5



the cru is 7.5, which is not correct, if we defined a
calculated item called CRU(srev/sqty), then the cru is
3.5, which is
correct. However, if we define the region to be china, the
result returned by pivot table using database query is
correct. and can be seen as follows in pivot table :
regionAll)
mod 020
Data
sum of srev sum of sqty sum of CRU
total 30 12 2.5

The reason is that CRU can't be aggregated. so how to
design the datbase query in the pivot table, when user
aggragates region, mod or
whatever, we can still return the cru correctly.
..


.

..