SOLVED

Variance by Month

Steel Contributor

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 tblDraws.Amount vs tblDrawsFCModel.AmountFC but summed by month.  The number of months are different between the tables meaning that tblDraws has more data than tblDrawsFCModel (this is not material though...just FYI).  I have 2 queries and these are the data sets I need to compare. 

 

I tried making a Union but I dont think this is the solution. 

 

Grateful for the help. 

Let me know if there are any questions. 

 

4 Replies

@Tony2021 

It doesn't sound like you think of the mathematical variance - as explained, for example, at Scribbr

So, could you provide some sample data and the expected results, please?

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

DateForecastActualVariance
Sep-21$339,081,951.99$472,600,000.00133,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.007,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.00969,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.0027,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.0011,070,691.80
Dec-22$99,828,733.11$162,600,000.0062,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
    
best response confirmed by Tony2021 (Steel Contributor)
Solution

@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-09339.081.952,00472.600.000,00133.518.048,00
2021-10245.781.440,0052.300.000,00-193.481.440,00
2021-1195.818.376,0090.800.000,00-5.018.376,00
2021-12118.605.588,00110.800.000,00-7.805.588,00
2022-0170.590.278,0018.000.000,00-52.590.278,00
2022-0236.230.702,0015.800.000,00-20.430.702,00
2022-0356.966.827,0016.200.000,00-40.766.827,00
2022-0442.533.517,0010.700.000,00-31.833.517,00
2022-0562.181.186,0025.000.000,00-37.181.186,00
2022-0689.889.856,0083.400.000,00-6.489.856,00
2022-0749.974.639,0019.100.000,00-30.874.639,00
2022-08159.237.465,0062.000.000,00-97.237.465,00
2022-09118.466.128,00145.900.000,0027.433.872,00
2022-10179.382.175,0085.600.000,00-93.782.175,00
2022-1160.929.308,0072.000.000,0011.070.692,00
2022-1299.828.733,00162.600.000,0062.771.267,00
2023-01130.116.940,0058.000.000,00-72.116.940,00
2023-02144.821.454,0063.000.000,00-81.821.454,00
2023-03102.897.205,0077.400.000,00-25.497.205,00
2023-04105.321.485,0048.000.000,00-57.321.485,00
2023-0535.378.753,0094.400.000,0059.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.
1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel Contributor)
Solution

@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-09339.081.952,00472.600.000,00133.518.048,00
2021-10245.781.440,0052.300.000,00-193.481.440,00
2021-1195.818.376,0090.800.000,00-5.018.376,00
2021-12118.605.588,00110.800.000,00-7.805.588,00
2022-0170.590.278,0018.000.000,00-52.590.278,00
2022-0236.230.702,0015.800.000,00-20.430.702,00
2022-0356.966.827,0016.200.000,00-40.766.827,00
2022-0442.533.517,0010.700.000,00-31.833.517,00
2022-0562.181.186,0025.000.000,00-37.181.186,00
2022-0689.889.856,0083.400.000,00-6.489.856,00
2022-0749.974.639,0019.100.000,00-30.874.639,00
2022-08159.237.465,0062.000.000,00-97.237.465,00
2022-09118.466.128,00145.900.000,0027.433.872,00
2022-10179.382.175,0085.600.000,00-93.782.175,00
2022-1160.929.308,0072.000.000,0011.070.692,00
2022-1299.828.733,00162.600.000,0062.771.267,00
2023-01130.116.940,0058.000.000,00-72.116.940,00
2023-02144.821.454,0063.000.000,00-81.821.454,00
2023-03102.897.205,0077.400.000,00-25.497.205,00
2023-04105.321.485,0048.000.000,00-57.321.485,00
2023-0535.378.753,0094.400.000,0059.021.247,00
2023-06 85.300.000,00 
2023-07 66.500.000,00 

View solution in original post