Variance by Month

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

SELECT qryDrawsMonth.FundingMonth, tblDrawsFCModel.AmountFC, qryDrawsMonth.AmountActual, [AmountActual]-[AmountFC] AS Variance FROM qryDrawsMonth LEFT JOIN tblDrawsFCModel ON qryDrawsMonth.FundingMonth = tblDrawsFCModel.DrawDtFC;

Tony2021

Iron Contributor

Jul 17, 2023

Gustav_Brock

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

Variance.xlsx12 KB

Gustav_Brock

Iron Contributor

Jul 18, 2023

Tony2021

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

Tony2021
Iron Contributor
Jul 18, 2023
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.

Forum Discussion

Resources