Forum Discussion
Solved
Variance by Month
Hello Experts, I have attached a pared down db with 2 tables and 2 queries. What I want to do is show the variance between the Amount for each month. For example, show the variance between t...
Is that you, Tony with the big numbers?
Anyway, first create a query, qryDrawsMonth:
SELECT DateSerial(Year([FundingDate]),Month([FundingDate])+1,0) AS FundingMonth, Sum(tblDraws.Amount) AS AmountActual FROM tblDraws WHERE (((tblDraws.Type)="Loan" Or (tblDraws.Type)="LDC") AND ((tblDraws.Amount)<>0)) GROUP BY DateSerial(Year([FundingDate]),Month([FundingDate])+1,0);Then use this in yet a query, qryVarianceMonth:
SELECT qryDrawsMonth.FundingMonth, tblDrawsFCModel.AmountFC, qryDrawsMonth.AmountActual, [AmountActual]-[AmountFC] AS Variance FROM qryDrawsMonth LEFT JOIN tblDrawsFCModel ON qryDrawsMonth.FundingMonth = tblDrawsFCModel.DrawDtFC;to obtain your variance:
qryVarianceMonthFundingMonth AmountFC AmountActual Variance 2021-09 339.081.952,00 472.600.000,00 133.518.048,00 2021-10 245.781.440,00 52.300.000,00 -193.481.440,00 2021-11 95.818.376,00 90.800.000,00 -5.018.376,00 2021-12 118.605.588,00 110.800.000,00 -7.805.588,00 2022-01 70.590.278,00 18.000.000,00 -52.590.278,00 2022-02 36.230.702,00 15.800.000,00 -20.430.702,00 2022-03 56.966.827,00 16.200.000,00 -40.766.827,00 2022-04 42.533.517,00 10.700.000,00 -31.833.517,00 2022-05 62.181.186,00 25.000.000,00 -37.181.186,00 2022-06 89.889.856,00 83.400.000,00 -6.489.856,00 2022-07 49.974.639,00 19.100.000,00 -30.874.639,00 2022-08 159.237.465,00 62.000.000,00 -97.237.465,00 2022-09 118.466.128,00 145.900.000,00 27.433.872,00 2022-10 179.382.175,00 85.600.000,00 -93.782.175,00 2022-11 60.929.308,00 72.000.000,00 11.070.692,00 2022-12 99.828.733,00 162.600.000,00 62.771.267,00 2023-01 130.116.940,00 58.000.000,00 -72.116.940,00 2023-02 144.821.454,00 63.000.000,00 -81.821.454,00 2023-03 102.897.205,00 77.400.000,00 -25.497.205,00 2023-04 105.321.485,00 48.000.000,00 -57.321.485,00 2023-05 35.378.753,00 94.400.000,00 59.021.247,00 2023-06 85.300.000,00 2023-07 66.500.000,00
Hello there Mr Gustav, good to hear you chime in.
You are correct. It is not the variance as described by Scribbr.
Its just simply A - B.
Let me know if you have any other questions.
thank you Gustav.
here is an example using the data in the access file:
grouped by month and show difference ("variance")
| Date | Forecast | Actual | Variance |
| Sep-21 | $339,081,951.99 | $472,600,000.00 | 133,518,048.01 |
| Oct-21 | $245,781,440.49 | $52,300,000.00 | -193,481,440.49 |
| Nov-21 | $95,818,376.19 | $90,800,000.00 | -5,018,376.19 |
| Dec-21 | $118,605,587.96 | $126,600,000.00 | 7,994,412.04 |
| Jan-22 | $70,590,277.95 | $18,000,000.00 | -52,590,277.95 |
| Feb-22 | $36,230,702.49 | $37,200,000.00 | 969,297.51 |
| Mar-22 | $56,966,826.73 | $30,200,000.00 | -26,766,826.73 |
| Apr-22 | $42,533,517.28 | $10,700,000.00 | -31,833,517.28 |
| May-22 | $62,181,186.06 | $25,000,000.00 | -37,181,186.06 |
| Jun-22 | $89,889,856.37 | $83,400,000.00 | -6,489,856.37 |
| Jul-22 | $49,974,638.96 | $19,100,000.00 | -30,874,638.96 |
| Aug-22 | $159,237,465.37 | $80,000,000.00 | -79,237,465.37 |
| Sep-22 | $118,466,128.45 | $145,900,000.00 | 27,433,871.55 |
| Oct-22 | $179,382,175.25 | $85,600,000.00 | -93,782,175.25 |
| Nov-22 | $60,929,308.20 | $72,000,000.00 | 11,070,691.80 |
| Dec-22 | $99,828,733.11 | $162,600,000.00 | 62,771,266.89 |
| Jan-23 | $130,116,940.03 | $58,000,000.00 | -72,116,940.03 |
| Feb-23 | $144,821,454.02 | $63,000,000.00 | -81,821,454.02 |
| Mar-23 | $102,897,205.00 | $34,900,000.00 | -67,997,205.00 |
Is that you, Tony with the big numbers?
Anyway, first create a query, qryDrawsMonth:
SELECT DateSerial(Year([FundingDate]),Month([FundingDate])+1,0) AS FundingMonth, Sum(tblDraws.Amount) AS AmountActual
FROM tblDraws
WHERE (((tblDraws.Type)="Loan" Or (tblDraws.Type)="LDC") AND ((tblDraws.Amount)<>0))
GROUP BY DateSerial(Year([FundingDate]),Month([FundingDate])+1,0);
Then use this in yet a query, qryVarianceMonth:
SELECT qryDrawsMonth.FundingMonth, tblDrawsFCModel.AmountFC, qryDrawsMonth.AmountActual, [AmountActual]-[AmountFC] AS Variance
FROM qryDrawsMonth
LEFT JOIN tblDrawsFCModel ON qryDrawsMonth.FundingMonth = tblDrawsFCModel.DrawDtFC;
to obtain your variance:
qryVarianceMonthFundingMonth AmountFC AmountActual Variance
|
- Hi Gustav, indeed it is me with the big numbers.
Perfect! It works great. I follow what you did. Makes sense. thank you very much sir! have a good night.