"Group wise" aggregation in CrossTab Queyr

[psatkar]

Hi,
I have a table with following column headings and data:
Trans_Date Expr1 Category_Extended Trans_Type
14/03/2009 -1000 Caretrol Payment
14/03/2009 -1500 Car:Repair Payment
27/02/2009 -1000 Caretrol Payment
25/02/2009 -800 Food:Grocceries Payment
23/02/2009 -1300 Food : Dining Out Payment
20/02/2009 -1200 Food:Grocceries Payment
I have written a crosstabe query which gives me monthly totals for each Category_extended. Following is the code for that:

TRANSFORM Sum([Qry_Transaction_Details_Select].Expr1) AS Amount
SELECT [Qry_Transaction_Details_Select].Category_Extended AS Cat_Ext1
FROM Qry_Transaction_Details_Select
GROUP BY [Qry_Transaction_Details_Select].Category_Extended
PIVOT "Col_" & DateDiff("yyyy",[trans_date],Date());

This works perfectly fine...and the out displayed will be as follows:
Category_extended Col_00
Caretrol -2000
Car:Repair -1500
Food:Grocceries -2000
Food : Dining Out -1300

In addition to above, now I want to display "group wise" total at the above of the categories e.g.:
Category_extended Col_00
Car -3500
Caretrol -2000
Car:Repair -1500
Food -3300
Food:Grocceries -2000
Food : Dining Out -1300

I have written a code to identify "Group" of the categories from various Category_extended field as follows:

left([Qry_Transaction_Details_Select].category_extended,instr(1,[Qry_Transaction_Details_Select].category_extended,":")-2

Is there any way to modify my existing query to display such "group wise" totals from within the category_extended columns displayed from my query?

ps: I do not wish to use Union of two different queries. I have already verfied with Union and it works but I have got some limitations while using Union option...

Thanks in advance.

--psatkar

 

[wiklendt]

have you tried putting this into a report and having a =Sum() textbox in the group headers? i don't think you can do what you desire just by one query.

....i could be wrong.

 

[boblarson]

ps: I do not wish to use Union of two different queries. I have already verfied with Union and it works but I have got some limitations while using Union option...

I'm afraid that's your only option if you want this in a query (that's the only way I know of doing it in Access).

 

[boblarson]

What "limitations" are you experiencing? What is it that you need to be able to do that you can't do with a Union query?