Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
interesting query problem
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: region:(All) 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 : region:(All) 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. .. . .. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP/MATCH Query - quite complex....but interesting. | Excel Discussion (Misc queries) | |||
Interesting TIF file problem | Excel Discussion (Misc queries) | |||
Most Interesting Problem I Had Ever Got. | Excel Programming | |||
Interesting Treeview problem. | Excel Programming |