Query where I divide a sum by an amount generated by a subquery

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.


Who is Participating?
mcorrenteConnect With a Mentor Commented:
You could also do this if you want to provide for the error I mentioned above:

TRANSFORM Sum(Iif(qrySum.OtherSum=0,1,qrySum.BenefitSum/qrySum.OtherSum)) AS BenefitRatio
SELECT qrySum.Project
FROM qrySum
GROUP BY qrySum.Project
PIVOT qrySum.FiscalNo;

In this case, it would display the BenefitRatio as 1 (100%, meaning all the cost incurred was toward benefits)
Can you post some example data from your table?
deedub84Author Commented:
The base table data would be like

Project      Category      Amount      FiscalNo
Research      ShopFloor      100      200706
Research      Manager      220      200706
Research      Executive      300      200706
Research      AdminAsst      125      200706
Research      AdminAsst      135      200706
Research      ShopFloor      110      200706
Research      Benefits      267.3      200706
Research      ShopFloor      110      200707
Research      Manager      242      200707
Research      Executive      330      200707
Research      AdminAsst      137.5      200707
Research      AdminAsst      148.5      200707
Research      ShopFloor      121      200707
Research      Benefits      275      200707

What I need is a query that would calculate

Project                200706      200707
Research      27.0%             25.3%

Which for each period is the sum of the benefits amount divided by the sum of the other salary categories ( I think I may have said it the other way around in my original question)


Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

This should get you closer:

SELECT Base_tbl.Project, Base_tbl.Category, Base_tbl.FiscalNo, Sum(Base_tbl.Amount) AS MoAmt, Sum(Base_tbl_1.Amount) AS TotAmt
FROM Base_tbl, Base_tbl AS Base_tbl_1
GROUP BY Base_tbl.Project, Base_tbl.Category, Base_tbl.FiscalNo
HAVING (((Base_tbl.FiscalNo) Like "*6" Or (Base_tbl.FiscalNo) Like "*7"));
Do this.  Create a query named qrySum.  The sql statement should be:
SELECT MyTable.Project, Sum(IIf([Category]="Benefits",[Amount],0)) AS BenefitSum, Sum(IIf([Category]="Benefits",0,[Amount])) AS OtherSum, MyTable.FiscalNo
FROM MyTable
GROUP BY MyTable.Project, MyTable.FiscalNo;

Create another query named qrySum_Crosstab.  The sql statement should beL
TRANSFORM Sum(qrySum.BenefitSum/qrySum.OtherSum) AS BenefitRatio
SELECT qrySum.Project
FROM qrySum
GROUP BY qrySum.Project
PIVOT qrySum.FiscalNo;

Of course use your table name in place of MyTable in the first SQL statement.  If you run the second query it should work.  You might get an error if there is a FiscalNo for which no non-benefit cost have been incurred (division by zero).
deedub84Author Commented:
Thanks mcorrente!

Never did a cross tab query before.  Can these be the source for a report easily?
sure can.
The tough thing is knowing the column names that will result from the query, because it generates that at runtime.  If you know there are a limited number of FiscalNo's then you can modify the properties of the crosstab to always show certain fiscalno's, but that doesn't sound like it will work for your setup.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.