asked on

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

mcorrente

Can you post some example data from your table?

deedub84

ASKER

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

tonydemarco

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"));

mcorrente

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

ASKER CERTIFIED SOLUTION

mcorrente

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

deedub84

ASKER

Thanks mcorrente!

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

mcorrente

sure can.

mcorrente

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.