Hello..I made a query that produces a cumulative column. Than I calculated the commission of the running totals. My query to calculate the running totals looks like that:
SELECT Turnoversumpermonth.Year, Turnoversumpermonth.Month, Turnoversumpermonth.SumOfTotalPrice, (SELECT Sum(Q.[SumOfTotalPrice]) FROM Turnoversumpermonth As Q WHERE Q.[Month] <= Turnoversumpermonth.[Month] AND Q.[Year] = Turnoversumpermonth.[Year] ) AS RunningTot
FROM Turnoversumpermonth;
The next query calculates the commissions of the running totals with my vba function CommissionP:
SELECT calculaterunningtotals.Year, calculaterunningtotals.Month, calculaterunningtotals.SumOfTotalPrice, calculaterunningtotals.RunningTot, CommissionP([RunningTot]) AS Commission
FROM calculaterunningtotals;
The only thing that I need to do to get the monthly commission is to substract the previous calculated commmission, for example:
Year Month CalcCommissionofRunningTotals
2005 1 100
2005 2 200
2005 3 300
2005 4 400
100 – 0, 200 - 100, 300-200, 400-300 aso.
I don`t know how to do that..Can someone help me out?
SELECT Turnoversumpermonth.Year, Turnoversumpermonth.Month, Turnoversumpermonth.SumOfTotalPrice, (SELECT Sum(Q.[SumOfTotalPrice]) FROM Turnoversumpermonth As Q WHERE Q.[Month] <= Turnoversumpermonth.[Month] AND Q.[Year] = Turnoversumpermonth.[Year] ) AS RunningTot
FROM Turnoversumpermonth;
The next query calculates the commissions of the running totals with my vba function CommissionP:
SELECT calculaterunningtotals.Year, calculaterunningtotals.Month, calculaterunningtotals.SumOfTotalPrice, calculaterunningtotals.RunningTot, CommissionP([RunningTot]) AS Commission
FROM calculaterunningtotals;
The only thing that I need to do to get the monthly commission is to substract the previous calculated commmission, for example:
Year Month CalcCommissionofRunningTotals
2005 1 100
2005 2 200
2005 3 300
2005 4 400
100 – 0, 200 - 100, 300-200, 400-300 aso.
I don`t know how to do that..Can someone help me out?