Absolute Value
By Richard Rost
3 years ago
Finding Absolute Value with the ABS Function
In this Microsoft Access tutorial, I'm going to show you how to construct a budget variance analysis using the Absolute Value (ABS) function.
Gavin from Norwalk, Connecticut (a Silver Member) asks: Hi, I'm currently working on a project where I need to create a budget variance analysis in Microsoft Access. My task is to compare our company's actual monthly expenses against our planned budget. For instance, in November, our actual spending was $5,000, but we had budgeted $4,500. I need to calculate the variance to understand the magnitude of deviation from our budget, regardless of whether we spent more or less than planned.
Members
There is no extended cut, but here is the database download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access 2016, Access 2019, Access 2021, Access 365, Microsoft Access, MS Access, MS Access Tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, budget variance analysis, ABS function, absolute value, calculate variance, deviation analysis, budget analysis using Access, variance calculation, comparing planned vs. actual, magnitude of variance, budget discrepancy, expense variance, constructing variance reports, managing budgets, analyzing financial data
Subscribe to Absolute Value
Get notifications when this page is updated
Intro In this video, I will show you how to use the absolute value function in Microsoft Access to calculate budget variance, helping you analyze the difference between actual and budgeted amounts regardless of whether you were over or under budget. We'll walk through how to set up your table, create calculated fields in queries and forms, and properly display variance values for easy reporting. I'll also cover the best ways to aggregate these values for totals in your forms and discuss common pitfalls to avoid when using calculated controls.Transcript In today's video, I'm going to show you how to construct a budget variance analysis using the absolute value function in Microsoft Access.
Today's question comes from Gavin in Norwalk, Connecticut, one of my silver members. Gavin says, Hi, I'm currently working on a project where I need to create a budget variance analysis in Microsoft Access. My task is to compare our company's actual monthly expenses against our planned budget. For instance, in November, our actual spending was $5,000, but we had budgeted $4,500. I need to calculate the variance to understand the magnitude of deviation from our budget regardless of whether we spent more or less than planned.
Gavin, this isn't that hard to do. We have to use something called the absolute value function, and you can do it in a query, a form, or a report. Let me show you how.
Before we do that, though, if you don't know how to make calculated fields in Access, either in a query or a form, go watch this video first. It's free. It's on my YouTube channel and my website. It will teach you what you need to know before we get started.
Let's say I have my budget data in a table, and I've got my budget ID, an auto number. I like to store information in actual date fields. For budgeting, for example, I would use month start dates. Make that a date value. Then you'll put in there, like for this year, January 1st, 2023, then February 1st, 2023, and so on. I don't like breaking those down into different month and year fields. I know a lot of people do.
Here we'll put the budget amount, a currency value, and then the actual amount, the actual amount that you spent. Now you might have your data stored in tables different ways. You could have individual transactions, and you have to bring them all together with an aggregate query to get totals for each month. I've got whole separate videos on that. I'll put some links down below for those.
But let's assume you get your data in this format. Let's save this as our budget T, the budget table primary key. Let's put a few bits of data in here.
The month start date will be 1, 1. Let's say we budgeted 2000, and we spent 3000. Next month, we have 2, 1. Let's say we budgeted 2400, and we came in under budget that month. We spent 1800. And one more, we'll do 3, 1. Let's say our budget was 2600, and we spent 3500.
For those of you who don't know, that's the ISO date standard here, month day. I am on a personal mission to get the entire world to convert to this format. Go watch this video for details.
Now, what we want to do is make a calculated field in a query that takes a look at the difference between these two. If that's negative, just ignore the negative part. For that, we use the absolute value function.
Let's close this. If they changed it, sure. Let's create a query. Create. Query Design. We're going to bring in the budget table we just created.
Bring in all the fields. Right here, we're going to make a difference value. I'm going to zoom in so you can see it better. Shift F2.
I have to be able to see the fields back here. We're going to call this difference, or just diff is fine. Difference is going to be the budget amount minus the actual amount. Since I don't have spaces in my field names, I don't need to put the brackets in there. Access will add them for me. If you come back, you'll see that it does that.
Now if I run this query, you'll see there's my difference right there. Under budget, over budget, under budget. It actually is the other way around because I subtracted this one minus that one, but it doesn't matter for the purposes of this video for what we're trying to do.
Let's go back to Design View. Now, in the next column, I'm going to take the absolute value of that. Absolute value for those who don't know is just a math term meaning it's the distance on the number line from that value to zero. Whether it's positive or negative doesn't matter. It basically just chops off the negative sign.
I'll zoom in again. This is going to be the actual variance. Let's call it that, and that's going to be the absolute value ABS of diff, that field we just calculated. Run it, and there's your actual variance, or just your variance. They are all positive values. Now you can just use that in your forms and reports.
If you want to skip a step, you can. I like to, when I'm teaching this stuff, especially for beginners, show the individual steps in the query because it makes it easier for beginners to comprehend. But if you want to skip a step, you can get rid of this. You can come right into here, change to variance or just V, and this will be the absolute value ABS of that whole thing. It's just shorthand. You get the same values.
I will save this as my budget Q. Now you can go ahead and use this value. You can use the budget Q in your forms and reports, or you can put this value directly in a form too.
Let's say, for example, you've got a budget form. Let's make it a continuous form. I've got a continuous form, it's just basically a blank form over here that I have formatted the way I like it. Watch my blank template video if you want to learn more about how this database was built. I'll put a link to that down below as well. I'll just use this guy. I'll copy and paste it. Ctrl C, Ctrl V. We'll call this my budget F.
Come into the budget F. Right click, Design View. Change the record source by double clicking right there with that little boxes. We're going to go to Data and make this guy bound to the budget Q, the budget query, or let's go right to the budget table. It doesn't matter. Go to the query, then you'll have that field V that will show up in the form, which is one way to do it. That's certainly fine. Or you could do it straight in the budget table.
You could put a calculated field right in the table. Let me show you. You can do this in reports too. It works the same exact way in reports. Budget T. So, I don't have the V field that shows up in budget Q, but we can add it in here.
Now that we have this bound to the record source, let's go to Form Design, Add Existing Fields. Let's take these guys, shift, click on the last one, and then click and drag. We're going to drop them all in here. I just keep these fields around for formatting because I can click on that, and then I can go to the Format Painter, which is right up there, paint that. See how it brings in the color? That's all. That's the only idea. Then I delete these guys.
Get rid of the labels. We can arrange these like so. The ID, if you care to see it, sometimes you do, sometimes you don't. The month start date right there, the budget amount, the actual amount, and then we get our labels across the top: Month.
What I do sometimes is I go cheap. I just make one big label here and then put some spaces in there: Budget, Actual. It's just easier than copying and pasting all these different labels, and it looks the same.
Get rid of all that extra space. We're going to put the variance over here. Save that. Close it. Let's see what it looks like. Looks good. A little formatting, left to align that, etc.
Now we want to put that calculated value over here. Design View. Make it a little bit bigger, and we can close this field list now. This is a value that's not actually in the underlying table. Again, we could bind the form to budget Q, but let's do it right in the form.
I'm just going to copy and paste this. Copy, paste, slide it up here. Open it up. Now let's go to the All tabs so we can see everything. First, let's give it a good name. Let's call this variance. The Control Source is where we're going to put our function now.
I got rid of what's in there. Zoom in again, Shift F2, so you can see it. This is going to be =ABS and then our two fields. What are they? They're right there: Budget Amount minus Actual Amount. That's it.
Hit OK. You can extend your label if you want to. Variance. Maybe that's a little bit too big. Bring it back. Save it. We're going to close it. We're going to reopen it: Budget F. And there you go.
Now, the one downside to putting the value directly in the form is that you can't put it in a form total on the bottom. So I wanted to show you both ways.
If you come down here, let me just copy these three things. Copy, paste, put them down here, slide them over. If you want to put totals down here in the form footer, and I have a whole separate video on this, here's that video, I'll put a link to this down below.
Here all you do is, instead of just budget amount, you put in here (zoom in for you): =Sum([BudgetAmount]) using the Sum function. Rename the field; I don't like "text" as a name. I will call it SumBudgetAmount. We'll do the same thing for this field, the actual amount: =Sum([ActualAmount]), name it SumActualAmount.
Now for this guy: you could try a couple of different things. You could, let's say this guy is called variance. In here, you could try =Sum([variance]). Name it SumVariance. Save it, close it, take a peek, and you get an error. That's because this guy is referencing a field that's not in the underlying table or query. It doesn't know what "variance" is at this point.
You could try doing it like this: you could try the Sum of that absolute value function again; instead, put the absolute value of budget minus actual inside the Sum function.
See if that works. Save it, close it, open it, and there you go. That one does work, but it's complicated.
So the easiest way to handle this is to simply base this form on that budget Q. This is much easier. Now, instead of putting the functions in here, you have that V, which you can rename "variance" in the query if you want to. You can put that here. Then, down here, all you have to do is say =Sum([V]). That's a whole lot easier.
Save it, close it, open it, and there you go. I don't personally like putting complex functions in here. Even though sometimes they'll work, sometimes they don't. So if you're going to put something in here where you want form footer totals, put it in the underlying query.
The same technique is going to work for you in reports as well. Exactly the same. Put it in the footer that you want. It has to be either in a group footer or a report footer, not the page footer. Page footer will not display those properly. There's a trick, and I've got a whole video on that one too.
If you want to learn more about this stuff, in the Access Expert 26 class, I cover all kinds of different math functions, absolute value, sign, round, int, fix, val, all kinds of stuff: logarithms, pi, you name it. I have a whole series about the comprehensive guide to Access functions, where I cover all the functions grouped by type: the text functions, the math functions, the conversion functions, all kinds of functions, lookup functions. It's a whole multi-part series, but this is the one that covers the math functions.
So check it out. I'll put a link to it down below.
That's going to do it for today. That's your TechHelp video. Hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main purpose of performing budget variance analysis in Microsoft Access as described in the video?
A. To determine whether the company earned more revenue than expected
B. To understand the magnitude of deviation from the budget, regardless of over- or under-spending
C. To track all individual expenses in real time
D. To automate payroll processing
Q2. Which function in Microsoft Access is used to calculate the variance so that the result is always positive?
A. SUM
B. ROUND
C. ABS
D. FIX
Q3. In the budget table setup, why is it recommended to use a date value field for the month start date?
A. It helps in sorting text values alphabetically
B. It allows for date calculations and groupings within Access
C. It saves storage space in the database
D. It makes reports more colorful
Q4. What is the advantage of breaking the calculation down into individual steps in the query for beginners?
A. It makes the query run faster automatically
B. It helps beginners better understand each step of the calculation process
C. It prevents Access from adding brackets to field names
D. It creates more tables in the process
Q5. When calculating variance directly in a query, which of the following expressions is correct?
A. [BudgetAmount] + [ActualAmount]
B. ABS([BudgetAmount] - [ActualAmount])
C. Sum([BudgetAmount] - [ActualAmount])
D. [BudgetAmount] / [ActualAmount]
Q6. If a form is not bound to the query (budget Q) containing the calculated variance, what is a downside when trying to sum the variance in the form footer?
A. The calculation will double all values
B. The Sum function will result in an error since the field does not exist in the underlying data
C. The calculation only works for the first record
D. The results will be displayed as negative numbers
Q7. Where should you ideally place calculated fields for easier form and report totals in Access?
A. In the table only
B. In the form header
C. In the underlying query to which the form or report is bound
D. In the page footer of the report
Q8. Why does the use of the absolute value function (ABS) matter in budget variance analysis?
A. It removes both positive and negative signs, making all results zero
B. It always rounds values up to the nearest dollar
C. It ensures the focus is on the size of the variance, not the direction
D. It is required to make Access save the query
Q9. What will happen if you try to create a sum of a calculated control in the form footer, where the calculation is not part of the underlying query?
A. Access will sum the displayed values without issue
B. Access will give an error because it cannot sum unbound controls
C. The result will be correct but in reverse order
D. Access will ignore the calculation
Q10. When creating totals in the report footer for variance analysis, where should the Sum function be used for calculated values?
A. In the page footer
B. In the report or group footer
C. In the report header
D. In the table design view
Q11. What general principle did the instructor emphasize for making complex calculations like variance available for forms and reports?
A. Always perform calculations at the table level for maximum speed
B. Favor simple calculations in forms and put complex logic in queries for easier maintenance and reporting
C. Use VBA code whenever possible for calculations
D. Never use queries for calculated fields
Q12. What is the role of calculated fields in Microsoft Access as described in the video?
A. To directly modify data in other tables
B. To display values based on computations of other fields without storing the result in the table
C. To format form colors
D. To create primary keys
Answers: 1-B; 2-C; 3-B; 4-B; 5-B; 6-B; 7-C; 8-C; 9-B; 10-B; 11-B; 12-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone will guide you through building a budget variance analysis in Microsoft Access using the absolute value function.
The scenario we're working with involves tracking a company's monthly expenses and comparing those actual amounts to planned budget figures. The essential task is to calculate the variance, meaning the size of the difference between what was budgeted and what was actually spent, regardless of whether you spent more or less than planned.
To achieve this, I use the absolute value function, which allows us to measure just the size of the variance, ignoring whether it's over or under budget. You can set up this calculation in a query, on a form, or within a report.
Before getting into the hands-on work, I always recommend that you understand how to create calculated fields in Access. If that is a new topic for you, take the time to watch my free tutorial on calculated fields available on my YouTube channel and website.
Let's move forward, assuming you have your budget data in a table. I like to structure my data with a budget ID as an autonumber, and I prefer to store actual date values for things like month start dates instead of splitting them into separate fields for month and year. For budgeting months, you might use values like January 1st, 2023, February 1st, 2023, and so on. Store both the budgeted amount and the actual amount spent as currency fields.
Your data structure might differ; some people store individual transactions and then total them up with aggregate queries for each month. I have additional tutorials covering those scenarios, and I will include links for those if you need them. For this explanation, let's focus on having a budget table with the fields described above: the primary key, the date, the budget amount, and the actual spending.
After entering some sample data for a few months, you are ready to create your variance calculation.
Set up a query using your budget table, and bring in all the fields. Next, create a calculated field to find the difference between the budget amount and the actual amount. The sign of the result will show whether you're over or under budget, but for the purpose of this analysis, we're interested in the magnitude only.
Add another calculated field to your query using the absolute value (ABS) function and reference the previous difference calculation. The absolute value function simply returns the distance from zero, so negative signs are ignored.
This approach helps you present the variance as a positive number no matter the direction of the difference. Now, you can use this result in your queries, forms, or reports.
If you want to, you can skip showing the difference as an intermediate field and just calculate the absolute value of the difference directly in one query field. While showing both steps is helpful for beginners to grasp the process, more experienced users might prefer the shortcut.
Once you've created this query (let's call it BudgetQ), you can base your forms and reports on it so the variance field is always available. In your forms, you have the option to display this variance directly. If you base your form on a table instead, you can still show the variance as an unbound calculated control, but that field does not exist in the underlying table.
To display the calculated variance in a form, you can simply enter a formula using the absolute value of the difference between budget and actual in the control's Control Source. Remember, though, that when you create a calculated control on a form, it cannot be used in a form total in the footer. That functionality requires that the calculation is performed in the underlying query, not just on the form.
For adding summary totals, such as the sum of variances at the bottom of your form, use the Sum function in the form footer. You can sum fields that come from your query, but you will run into problems if you try to sum calculated controls that only exist on the form. Therefore, the best practice is to handle all your core calculations in the query and keep your form controls simple.
The same principles apply to reports in Access. Calculated values and summaries must reside in group footers or the report footer, rather than the page footer, to display correctly. I have separate tutorials covering those details.
If you want to expand your understanding of math functions in Access, my Access Expert Level 26 course covers not only absolute value, but also sign, round, integer, fix, value conversion, logarithms, pi, and much more. In addition, there is a comprehensive series that covers Access functions grouped by category, including text, math, conversion, and lookup functions.
For more on this topic, including step-by-step instructions, you can find my complete video tutorial on my website at the link below.
Live long and prosper, my friends.Topic List Building a budget variance analysis in Access
Setting up a budget table with fields for date, budget amount, and actual amount
Entering monthly budget and actual data using date fields
Creating a query to calculate differences between budget and actual amounts
Using the ABS function in Access to calculate absolute variance
Adding calculated fields to a query for difference and variance
Saving and naming queries for later use
Binding a form to a query to display calculated variance
Adding fields to a continuous form in Access
Formatting and arranging fields and labels on a form
Creating and naming calculated controls in a form
Using the ABS function directly in a form control source
Displaying calculated variances in Access forms
Adding footer totals in Access forms using the Sum function
Calculating and displaying budget, actual, and variance totals in form footers
Troubleshooting errors with summing calculated controls in forms
Best practice of calculating fields in queries for summary totals
Displaying calculated variances and totals in Access reports
Distinguishing between group, report, and page footers for calculations in reports |
