I have two tables that connected with the field "Sub-Department"
Expenses table:
Department
Sub Department
Expense Type
Expenses$
Production
Production-A
Salaries
100
Production
Production-A
Maintenance
399
Production
Production-B
Energy
488
Production
Production-Management
Salaries
400
Development
Development-A
Salaries
100
Development
Development-A
Maintenance
2454
Development
Development-B
Energy
450
Output Table:
Sub Department
Output
Production-A
300
Production-B
400
Production-C
500
Production-D
600
Development-A
700
Development-B
500
Development-C
567
I want to display the following table:
Department
Expense Type
Expenses$
Output
Output-result
Production
Salaries
500
sum(expense on salaries)/sum(output by Sub-Department whose name begins with Production )
500/1800
Production
Maintenance
399
sum(expense on Maintenance)/sum(output by Sub-Department whose name begins with Production )
399/1800
Production
Energy
488
sum(expense on Energy)/sum(output by Sub-Department whose name begins with Production )
488/1800
Development
Salaries
100
sum(expense on salaries)/sum(output by Sub-Department whose name begins with Development )
100/1767
Development
Maintenance
2454
sum(expense on Maintenance)/sum(output by Sub-Department whose name begins with Development )
2454/1767
Development
Energy
450
sum(expense on Energy)/sum(output by Sub-Department whose name begins with Development )
450/1767
In the column "Output" I'd like to divide the sum of expenses type by department, with the total value of "output" by the sum of all output of the Sub-departments that start with the beginning of the department that displayed in the first column.
