Calulating of groups / categories?

[supportservice]

I'm having a road block on how to go about calculating Net Income and Operating Income with the set of data

 CategoryGroupID	CategoryGroupDesc	CategoryGroupCode	SumOfMTD	SumOfLMTD	SumOfYTD	SumOfLYTD
1	Revenue	R	$740,079.83	$750,968.68	$2,972,403.40	$3,001,001.56
2	Cost of Sales	C	($247,339.60)	($316,744.08)	($1,295,645.13)	($1,313,770.59)
3	Expenses	X	($176,723.25)	($236,938.32)	($714,344.81)	($790,067.17)
4	Income and Expenses	O	($6,413.14)	($5,168.71)	($1,030.49)	($14,644.61)
5	Income Tax	I	$0.00	($1,775.00)	($1,605.22)	($6,348.00)
6	Assets	A	($599,271.27)	($85,740.43)	($1,180,680.38)	($128,331.73)
7	Liabilities	L	$347,723.76	$18,257.63	$654,020.63	($84,228.43)
8	Equity	E	$0.00	($61,428.00)	($81,594.68)	($395,199.39)

To get the Net Income you take the Revenue (R) - (Cost of Sales (C)+Expenses (X)+Income & Expenses (O)+Income Tax (I))

To get the Operating Income you take the Revenue (R) - (Cost of Sales (C)+Expenses (X) )

 

[supportservice]

Was thinking perhaps I need to create sub groups to identify the other two levels?

 

[maxhugen]

I'm thinking that you may need to transform the data. Create a crosstab query, with the CategoryGroupID as the columns. Use a Left Join from your CategoryGroup table, to the data table, to ensure you always get all Categories (even if no data).

Using this query as the base, create a new query, so that in addition to columns 1 to 8, you can add the calculated columns you need.

Max Hugen
Australia

 

[supportservice]

Thanks. I ended up creating another column to identify the sub-group and queried on them.

Appreciate it!