Calculated Fields in Microsoft Access - How to Perform Calculations in Queries & 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?

BONUS FOR CHANNEL MEMBERS:
Silver Members and up get access to an Extended Cut of this video. 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.

MEMBERS ONLY VIDEO:

BECOME A MEMBER:

LEARN MORE:

LINKS:

LEARN MORE:

FREE TEMPLATE DOWNLOADS:

ADDITIONAL RESOURCES:

KEYWORDS:
microsoft access, ms access, #msaccess, #microsoftaccess, calculated fields, calculated query fields, form footer total, calculated value in page footer report #Error, running sum, report page footer totals, build an expression, create a calculated field in access, calculating field, calculated columns, formulas in queries

QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[coolman]

I'm thankful that you have an entire series based on ACCESS.You have helped me so much and all i can say is thanks you men.Appreciate it :)

[EW-srbl]

Greetings! I really enjoy watching and re-learning from your tutorials. I've been away from computer languages/applications for some time now. My 5th or 6th career change has me back into the game and remembering a lot of what I've forgotten. I'm thinking about becoming a member to enhance my SME status as a CSA contractor. As I build a rather large DB I'll add your resources along with others. I look forward to seeing more of your tutorials. Cheers!

[stevenhenry]

DUUUDEEE!!! I've been on this one homework problem for over 2 hours trying to get this mashugana database to calculate!! Thank YOU!!!!

[jessiebrown]

Your videos have been super helpful! Since starting as a Sales Support Admin last April, I've built two databases for our Sales team. The results have been highly praised and the time to develop the DBs was reduced because of your videos. Thank you!

[captainkeyboard]

Performing calculations in a query and performing footer total in a form is a straightforward process. I like working with queries. It is helpful.

[SouthShoreTrain]

As soon as I heard "tribble bait" I got way more focused. Nice job

[ZahidKhan-mboc]

Very useful and great information. Thanks a lot for guidance.

[mghector]

👍 Simply the best way you explained Access query and formulas

[kevaughnramsay]

Thank you very much for your help. I was struggling with getting the number sign to show for the calculated fields now its working.

[markarnold]

I was able to have a query do a calculation as you describe- thank you! However, how can you update a table field with that calculated query? For example, if a record shows a customers name, a qty of a particular item purchased, to show the total in a table field? If i try to add a lookup field from that query, i get an error.

[arnaldomantillas]

How to store on the table calculated value in the forms like control number or reference number?

[iTayz]

Hello, I was trying to calculate total(or rather sum) of fields, some being empty (null). The problem is that the total can't be calculated without having a value in one of such field. zero value gives a total but i dont want to use zero in my fields, i just want it to be empty. is it possible to have some empty fields and still get your total?

To be more precise:

total: ... and field3 is empty

[thomastravers]

Bit of an unrelated question but I created a calculated query. When finished, I unchecked the "show" box for the fields I did not want to see when it runs. Is there a way to bring them back and show again? I wanted to see them in design view again. Thanks!

[stanTrX]

Thanks Richard, when its not good to create a form from queries? Joined table queries causes problem sometimes for instance

[ijukvek]

Hello Richard, how do you add a calculated field in query design for items in different tables?

[ikxxcft]

how can I make a summing process for aspecific values such as a some value from table products and their prices from pricee table. or any process as this

[AH_World_bh]

Sir I have database for employees, I want to get thier annual leave depending on their service period, like each employee has 40 days leave, and I want to get their service years by subtracting their appointment date from current date. While formating the field as number, but unfortunately I am not getting the correct period years and months. Please let me know how I can I do it.

[constructioncost]

Hello sir, Does the extended cost field also appeared in the ItemT?

[aisha-mimc]

hi, i created a query, but i have no idea how to bind this table+query with c# datagridview. i have no experience in it but i do have to create this project. can you help me with that? what should i do to bind the table with total column in it with datagridview???

[easyspeak]

I wonder the correct way to change unit price while maintaining all the historical unit prices on official reports for IRS and/or display forms showing customer journals or ledger entries.. what's the standard solution calculation with dates or a make table..? Cheers ..