Query where I divide a sum by an amount generated by a subquery
Posted on 2007-08-02
In our accounting system I have a transaction file that contains project transactions, among which are salary and benefit amounts based on different employment categories.
I'd like to come up with a query that calculates by month the ratio of benefits to salaries.
I have a table with the relevant categories which I am using to limit the project transaction file to only those salary categories.
What I can't get to work is pulling the relevant sum of benefits for each period to divide by... perhaps I need a subquery?
My fields are
Project, FY: left([Fiscalno],4), Jan, Feb, Mar, etc.
The month fields would be a test for the month, then the division, e.g. Jan: iif(right([fiscalno],2="01", ... sum of amount / by sum of Benefits Category for period ... , 0). I can easily get the sum of amount, but not sure how to get the divisor, which would be sum of the amount of the 'Benefits" category for the fiscal period.