Calculated Fields
By Richard Rost
5 years ago
Calculated Query Fields & Form Footer Totals
In this video, I will show you how to use calculated query fields to perform basic calculations on the fields in your table. We will also see how to use form footer totals.
Harold from Las Vegas, Nevada (a Platinum Member) asks: I'm trying to figure out the value of my inventory. I have the cost of each item and the quantity I have on hand. How do I go about calculating the total value of my inventory?
Members
I will show you how to use calculated table fields (and we'll talk about whether or not you should use them.) We'll also take these calculations over to a report. I'll show you how to use a running sum, and how to create a report page footer total.
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!
 Links
Intro In this video, I will show you how to create calculated fields in Microsoft Access by building a query to calculate inventory value based on quantity and unit cost. You will learn how to add these calculated fields to your queries, format results as currency, and display the results in a continuous form. I will also demonstrate how to create a form footer total to sum up your calculated values for a quick inventory summary.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we are going to talk about calculated fields. I will show you how to perform calculations in your queries. As a bonus, I will show you how to do a form footer total as well.
Today's question comes from Harold from Las Vegas, Nevada, one of my platinum members. Harold says, I am trying to figure out the value of my inventory. I have the cost of each item and the quantity I have on hand. How do I go about calculating the total value of my inventory?
This is pretty straightforward to do. We need to learn how to do a calculated query field and then a form footer total. I will show you them both in this video.
Here I am in my TechHelp blank database template. If you would like to learn how I put this template together, you can find a copy of it down below. I will put a link. You can do this in any database that you want.
Let's create a table to store our values. I am going to start off with my item ID. That will be my auto number. How about a description? That will be short text.
We will need the quantity on hand, so I will just put quantity QTY. That will be a number. I am going to make that a number of type double so I can store fractional components. If you ever have to have half of something, or if you are tracking cases, for example, you might have half a case on the shelf.
There is a unit cost. That will be my currency value.
This is all we need to store in the table. Yes, you can do calculations in tables. But the accepted best practice is to do the calculations in a query. I will talk more about this in the extended cut.
Let's save this table as my item T. Primary key, yes, that will make it my auto number.
Let's put some data in this table. Let's say I have got Trouble Bate. I have got 15 of those at $12 a piece. I have got a Photon Torpedo, maybe three of those at $150 a piece. How about a 3D chess board? There are 12 of those in stock, and they sell for 75 bucks. That is enough for now.
The first thing we have to do is calculate our line total, or our extended cost, whichever you want to call it. It is going to be quantity times unit cost. To do that, we are going to create a calculated query field.
So close the table. Save changes, yes.
Now all the data in your database is stored in the table. Queries allow us to view the data in different ways, including sorting, filtering, and performing calculations. There is no need to store the calculated value in the table if we can calculate it on the fly. You save yourself a lot of space that way too.
So, create query design. If you have never built a query before, I have got videos on Query Design. Go watch those. I will put a link down below in the link section for you.
I am going to add my item T to the table I just created. Close that.
Let's bring in all the fields, the star. That says bring all those fields into the query.
Now, right here, we will put our calculated query field. It is going to look like this: Extended cost, EXT, cost, colon, set this equal to what other two fields? Quantity times unit cost, and then press Enter or Tab.
Since I did not use any spaces in my field names like you should not, I did not have to put these brackets around those fields. If you have spaces like unit space cost in your table field names, then you have to remember to manually put those brackets on there.
That is why I teach in my beginner class: do not put spaces in your table names, in your field names, in your query names, all that stuff. Leave the spaces out of it.
Let's save this query. I am going to hit Control S to save. I am going to call this my item Q, my item query. Now if I run it, you can see there are all my extended costs. 15 times 12 is 180, 3 times 150 is 450.
If you want to format this column to show up as a currency, go back to Design View. On the column, right click, go to Properties, and then right here in Format, you can pick Currency. Then save it and run it again. There you go. There is your extended cost.
Now to calculate the total down here on the bottom for all of your items, we are going to load this data into a form. In my template here, I have created a continuous form that I can bring this data into. If you do not know how to build a continuous form, again, I have got videos on that. Go watch that next, and that will teach you how to make continuous forms. I also show how to do it in the blank template video. I will put a link to both of those things down below.
Now that you know how to make a continuous form, let's go over here. I am going to copy this guy. Copy, paste. We will call this my item F, my item form. Right click, Design View.
Now I just have to tell this form where to get its data from. So open up the form's properties right here, double click. On the All tab, find the Record Source and pick Item Q. Now this form is bound to that query, so I can put those fields right in here.
This guy can now be in the Control Source here, the Item ID. I will copy and paste that up to the name. This guy can be the next field, which will be the description. Copy, Paste. Or you can bring the fields in from the Add Existing Fields.
I have these already formatted. I have the ID gray, and this box is white. I cover that in the template video. Now I will bring over Quantity, UnitCost, and Extended Cost. So I will click on Quantity, hold down the Shift key, click on that last one, then click and drag and drop them right there in that detail section.
I will just delete these labels because we will put new labels above them. Quantity can go here. UnitCost can go next to it, and Extended Cost can go on the end. I am going to make Extended Cost gray, just like this guy is. I will use the Format Painter, and then paste on that guy. It is a calculated field, so gray just tells the user you cannot change that.
Close that up. We could put our labels across the top. Copy, Paste. Just paste those over there and slide them over. If you have never done this before, go watch those videos because I cover this in detail.
There is a Quantity label across the top. Here is the Unit Cost label right there. Then we will put the Extended Cost right out here.
Save that, close it, and then reopen it again, Item F. Look at that. My ID, Description, Quantity, Unit Cost, and the Extended Cost right next to it.
Now, down here on the bottom in the Form Footer, we will put a total field down there. So back to Design View. I am going to copy this Extended Cost, and then paste it down here in the Form Footer.
If your Form Footer is not on, just right-click anywhere in here on one of these bands. See these different bands here? Right-click on any one of them and just make sure your Form Header Footer is on. Not the Page Header Footer, that is different. We do not use that. That is for printing, and we do not print forms. We use forms on the screen. Reports are for printing.
Now I have a copy of that Extended Cost down here. Save it, close it, and take a look at it, and see what you have got. Well, it does not look right. It has got whatever value I happen to be on up top here. See that? So what we have to do is we have to sum these up using the sum function.
So come right here. Close the Field List if it is still open. Open up the properties for the Extended Cost, double-click on it, or you can click on Property Sheet. Right here for the Control Source, instead of just Extended Cost, I am going to put in here equals the sum of the Extended Cost. Just like that, sum of Extended Cost, all in parentheses like that. See that? I will zoom in for you. There you go.
The sum function says sum up all of the Extended Costs on this form. We will change the name, so it is not Text 10 to sum Extended Cost, just like that.
Now save it, close it, open it back up again. There you go. So that is the total, and that is how you get your entire total for all of your items in inventory.
First, you get the Line Item Total, the Extended Cost, in a query. Yes, you can do this calculation here directly in the form too, in addition to the table. The query is the best place because if you want to use this value anywhere else, in a different form or in some reports, you do not have to keep making that calculation over and over again. It exists right here in this query. So, base everything else, other forms, other reports, base them off of this query. Plus, if this calculation ever changes in the future, you only have to change it in one spot – this query. You do not have to go change all your different forms and all your different reports.
So trust me, put your calculations that are based on each record in a query.
So that is it. That is calculated query fields and form footer totals. I hope you learned something today.
Want to learn more? In the extended cut for members, we will talk about those calculated table fields. Yes, you can actually store calculations in your tables and in forms directly. I will talk about what they are, how to do them. I will show you how to do them, and we will talk about whether or not they are good and whether you should stick with queries or not. Then we will bring this over into reports. I will show you how to put a running balance in your report. I will show you how to use a report page footer. Normally you have to do just a report footer. It is a trick to get you to put a total on the bottom of each page. So in case you have a multi-page report, you want to see a total for each page. I will show you how to do that in the extended cut as well.
Reminder: as an up, get access to all of my extended cut videos. We are over 100 TechHelp videos, and we are getting very close to 100 extended cuts as well. So there is lots of material to watch. Well worth your membership.
How do you become a member? Click the join button below the video. If you click the join button, you will see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more.
Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use.
Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more.
Do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more.
If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all.
Make sure you subscribe to my channel, which is completely free, and click the bell icon and select all to receive notifications when new videos are posted. Click on the Show More link below the video to find additional resources and links. You will see a list of other videos, additional information related to the current topic, free lessons, and lots more.
YouTube no longer sends out email notifications when new videos are posted, so if you would like to get an email every time I post a new video, click on the link to join my mailing list.
If you have not yet tried my free Access Level One course, check it out now. It covers all the basics of building databases with Access. It is over three hours long. You can find it on my website or on my YouTube channel. If you like Level One, Level Two is just one dollar. It is also free for all members of my YouTube channel at any level.
Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there.
Click here to watch my free Access Beginner Level One course, more of my TechHelp videos, or to subscribe to my channel.
Thanks for watching this video from AccessLearningZone.com.Quiz Q1. What is the best practice for performing calculations like inventory value in Microsoft Access?
A. Store calculated values in the table
B. Perform calculations in queries
C. Create calculated fields in forms only
D. Only perform calculations in reports
Q2. Why is it generally not recommended to store calculated values directly in tables?
A. It requires more complex queries
B. Calculated values cannot be used in forms
C. It wastes storage space and can lead to data inconsistencies
D. It makes forms run slower
Q3. What is a calculated query field?
A. A field you add to a table for storing totals
B. A field in a query that performs computations based on other fields
C. A special data type only available in forms
D. A hidden column in a report
Q4. How do you create a calculated field for "Extended Cost" in a query when you have fields for quantity and unit cost?
A. Use ExtendedCost: quantity + unitcost
B. Use ExtendedCost = [quantity] & [unitcost]
C. Use ExtendedCost: [quantity] * [unitcost]
D. Use ExtendedCost: [unitcost] - [quantity]
Q5. If your field names have spaces (e.g., "Unit Cost"), what must you do in the calculated query field?
A. End the field name with a semicolon
B. Place brackets around "Unit Cost"
C. Ignore the space
D. Use only quotes around the field name
Q6. When formatting a calculated field in a query to show currency, where do you set the format to "Currency"?
A. In the table design
B. In the property sheet of the query column
C. In the form design only
D. In the report footer
Q7. How can you display a total sum of a calculated field (like Extended Cost) in a form footer in Access?
A. Add all values manually in a text box
B. Use the Sum function in the Control Source of a text box in the footer
C. Use the average function in the detail section
D. Calculate the sum in the table and show it in the form
Q8. What is the advantage of putting your per-record calculations in a query rather than in forms or tables?
A. It slows down data entry
B. It prevents displaying the calculation in reports
C. You only have to change the calculation in one place
D. Queries cannot be reused elsewhere
Q9. What does the "continuous form" layout allow you to do in Access?
A. Show a single record per form view
B. Show multiple records at once in a list-like format
C. Edit only one record at a time
D. Automatically print a summary of all records
Q10. When creating a form total for a calculated field, why should you avoid using the Page Footer for this purpose?
A. Page Footers are not available in forms
B. The Page Footer always prints at the top of the page
C. Page Footers are meant for printed versions only, not on-screen forms
D. It requires extra formulas
Q11. Which part of the video emphasizes the importance of avoiding spaces in field names?
A. Advanced VBA section
B. Beginner class guidance
C. Report design best practices
D. After calculating the running balance
Q12. What is the primary benefit of formatting calculated fields (like Extended Cost) as gray in the form?
A. Gray fields are hidden from the user
B. To visually indicate the field is calculated and not directly editable
C. To reduce file size
D. To make reports print in black and white
Answers: 1-B; 2-C; 3-B; 4-C; 5-B; 6-B; 7-B; 8-C; 9-B; 10-C; 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 video from Access Learning Zone focuses on calculated fields in Microsoft Access. I am going to walk you through how to perform calculations in your queries, and as an added bonus, I will also show you how to create a form footer that displays a running total.
The question we are looking at today comes from a viewer who wants to figure out the total value of his inventory. He already has fields for both the cost of each item and the quantity on hand, but he is not sure how to tie this information together for a total.
This process is relatively simple, involving calculated fields in a query and a total in a form footer. I will guide you through both steps.
We start by setting up a table to store our inventory items. The table will have an Item ID, which is an auto number, a Description as short text, Quantity (labeled as QTY) set as a number with the double data type for fractional quantities, and the Unit Cost as a currency value. That is all you need to store in the table. While Access does allow calculations at the table level, best practice is to handle them in a query. I will discuss reasons for this at length in the video's extended cut.
After saving the table and setting the Item ID as the primary key, you can enter a few sample records. For example, you might enter items such as "Trouble Bate" with a quantity of 15 and a unit cost of $12, or "Photon Torpedo" with 3 in stock at $150 each. With a few items entered, you are ready to perform the calculation.
The next step is to create a calculated field to determine each line's total, also called the extended cost. This is simply the product of the Quantity and Unit Cost fields. To do this, you will build a new query based on your table. All the fields should be added to the query, then you insert a new calculated field for the extended cost, using the syntax Extended Cost: QTY * UnitCost (assuming you have avoided spaces in your field names, which is highly recommended, as it saves you from having to add brackets everywhere).
Once you save and run the query, you will see the extended cost for each item. To make this column display as currency, simply adjust the format property for this column in Design View.
With the calculations in place at the query level, you can now set up a form to display your data. Using a continuous form is ideal for showing multiple records at once. Once you create a new form and set its record source to the query you just made, you can bring in all the fields, arrange them as needed, and adjust formatting details. For example, you might shade calculated fields gray to indicate they cannot be edited by the user.
To display a total for all items in the form footer, copy the Extended Cost textbox down into the form footer area. If the footer is not visible, you can enable it through the form's layout tools. In the Control Source property of the new textbox, use the sum function to add up all the extended costs, which will display the total value of your inventory at the bottom of the form.
Using queries for your record-by-record calculations has several advantages. The calculation only has to be defined once, so if you use the same information elsewhere (like in reports or other forms), you do not have to recalculate it each time. If the calculation ever changes, you only need to update the query, not every form or report.
In summary, you use a calculated field in a query to determine the extended cost for each inventory item, and then display the total in the form footer using the sum function. This approach is efficient, follows best practices, and is easy to maintain as your database grows and changes.
If you found this lesson helpful and want to learn more, I have an extended cut available for members where I talk about calculated fields directly in tables and forms, discuss when and why you might use them, and demonstrate how to build reports that include running balances and page footers for subtotals. I also share a trick for getting a total to appear at the bottom of every page in a multi-page report.
Members have access to a wide and growing library of extended cut videos, database downloads, and sample code. Membership also includes access to my beginner and expert courses for those who want to get more deeply into Access or expand to other programs like Excel or Word.
The free TechHelp videos will keep coming as long as there is interest. If you have a question you would like to see answered, you can submit it through my website. Be sure to subscribe to my channel and join my mailing list if you want updates, since YouTube does not always send out email notifications anymore.
If you are starting out, take a look at my free Access Level One course, which covers all the basics and is available both on my website and my YouTube channel.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Creating a table for inventory items
Setting up table fields: Item ID, Description, Quantity, Unit Cost
Choosing appropriate data types for table fields
Entering sample inventory data into the table
Creating a calculated field in a query
Building a query to calculate line item totals
Formatting a calculated query field as currency
Saving and naming queries for inventory calculations
Creating a continuous form to display query data
Setting a form's Record Source property to a query
Adding and arranging fields in a continuous form
Using the Format Painter for form controls
Adding a form footer section to display totals
Enabling the Form Footer in Design View
Calculating the sum of a field using the Sum function in a form footer
Renaming form footer controls for clarity
Advantages of doing record-level calculations in queries |
