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
It doesn't sound like you think of the mathematical variance - as explained, for example, at https://www.scribbr.com/statistics/variance/
So, could you provide some sample data and the expected results, please?
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 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 - 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.