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

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

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
Asked:
deedub84
  • 5
  • 2
1 Solution
 
mcorrenteCommented:
Can you post some example data from your table?
0
 
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)

Thx,

Deedub84
0
 
tonydemarcoCommented:
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
mcorrenteCommented:
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
 
mcorrenteCommented:
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
 
deedub84Author Commented:
Thanks mcorrente!

Never did a cross tab query before.  Can these be the source for a report easily?
0
 
mcorrenteCommented:
sure can.
0
 
mcorrenteCommented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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