SOLVED

# Variance by Month

Steel Contributor

# 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 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

# Re: Variance by Month

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?

# Re: Variance by Month

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
best response confirmed by Tony2021 (Steel Contributor)
Solution

# Re: Variance by Month

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

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

# Re: Variance by Month

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

# Re: Variance by Month

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