[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 491

# 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.

Thanks!

Deedub84
0
deedub84
• 5
• 2
1 Solution

Commented:
Can you post some example data from your table?
0

Author 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      ShopFloor      110      200706
Research      Benefits      267.3      200706
Research      ShopFloor      110      200707
Research      Manager      242      200707
Research      Executive      330      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)

Thx,

Deedub84
0

Commented:
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"));
0

Commented:
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).
0

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)
0

Author Commented:
Thanks mcorrente!

Never did a cross tab query before.  Can these be the source for a report easily?
0

Commented:
sure can.
0

Commented:
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.
0

## Featured Post

• 5
• 2
Tackle projects and never again get stuck behind a technical roadblock.