Calculated Fields in Microsoft Access - How to Perform Calculations in Queries & Form Footer Totals

[@coolman2520]

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 :)

[@599CD]

Glad to help!

[@captainkeyboard1007]

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

[@599CD]

Glad it was helpful!

[@captainkeyboard1007]

@@599CD Thank you.

[@stevenhenry75]

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

[@jessiebrown9615]

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!

[@599CD]

Glad it was helpful!

[@SouthShoreTrain]

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

[@HaseebIlyas_Official]

Extended Explanation for new users - Wonderful Style

[@599CD]

👍

[@EW-sr5bl]

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!

[@599CD]

Glad you like the videos. Cheers!

[@mghector6430]

👍 Simply the best way you explained Access query and formulas

[@599CD]

Thank you

[@kevaughnramsay9846]

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

[@599CD]

You're welcome!

[@narx8898]

man you are a exam saver

[@599CD]

I get that a lot. LOL

[@stanTrX]

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

[@599CD]

I only recommend creating forms based on queries when you are solely editing records from one of the tables in the query. If you have calculated fields, for example, quantity times unit price, and that's a non-editable field, then you're fine. However, you don't want to have fields that you can edit from multiple tables because that just asks for trouble. I actually have a video on this topic coming out pretty soon, so good question.

[@markarnold2915]

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.

[@ZahidKhan-mb6oc]

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

[@599CD]

Most welcome!

[@BennyOdhiambo-u5u]

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

[@599CD]

Just specify the table name in front of the field name. CustomerT.FirstName, for example. I actually have a video on this coming out soon for beginners.

[@brettgattinger3338]

Those Proton Torpedoes are a steal

[@599CD]

Ain't it tho? :)

[@jamesdribbling]

Thank you Sir. Love from China.

[@599CD]

Welcome

[@constructioncost]

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

[@599CD]

No. Why would it? It's unit cost multiplied by the quantity you're purchasing.

[@shirkobikas8966]

Very useful thanks a lot

[@599CD]

Welcome

[@easyspeak101]

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

[@599CD]

You need to copy the product price to the order details table. See the extended cut for 599cd.com/Invoicing

[@easyspeak101]

@@599CD .. Cheers.. to preserve old pricing is answered in the extended cut.

[@arnaldomantillas]

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

[@meslianmalika1201]

Hello. How to calculate the difference between 2 rows in access? The difference between 2 cells of the same colunm. for exemple the difference of salary between 2 workers?

[@599CD]

599cd.com/QQ

[@thomastravers6100]

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!

[@599CD]

Uncheck the box.

[@djoneforever]

I suppose this will work for comic books with some more additions and modifications. Thanks.

[@599CD]

Comic books? Sure. :)

[@djoneforever]

@@599CD you should definitely do a tutorial for comic book collecting with Access. Thanks in advance.

[@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: [field1]+[field2]+[field3]+[field4] ... and field3 is empty

[@599CD]

599cd.com/NZ

[@aisha-mi3mc]

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

[@599CD]

Sorry... C# is not my jam.

[@منصورالمنصوب-ض9ص]

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

[@599CD]

SUM function? 599cd.com/FormFooterTotals

[@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.

[@599CD]

Feel free to submit your question at 599cd.com/TH

[@AarefaAbaya]

great sir

[@599CD]

Thanks

[@ayhamlababidi7369]

Thank you I need ti create Calculated field in report, something like invoice Qty - UnitPrice - Amount [=(Qty * UnitPrice)] and Total [= Sum(A,ount)]

[@599CD]

That should work

[@aamirawan8188]

Sir how we can update the currency value in table using vba....

[@599CD]

Format it as currency.

[@Amirkhan-bg3hu]

Sir How i do This? Sir i want to have a calculated field, it also a calculation and i can also add the value of my own Sir Please Reply

[@599CD]

I don't understand

[@Amirkhan-bg3hu]

Hi, Thank you for all of your reply. Let me explain my situation. I have a receipt to charge customer & received payment (No more invoice). I have 4 items to be charged, and each receipt charge one item only. 2 items have a fixed rate (will be revised within a period) & 2 items without fixed rate. So when I select the fixed rate items, the calculation of [itemVaule} * [PayPeriod] can be workable. But in the items without fixed rate, I hope I can put the [Total amount] by myself. In addition, I need all the above to generate a report. Hope the above will give all of you a right director to think of my problem. Thank you again for your help. I had tried to calculate in query by using an expression : [ItemValue] * [PayPeriod], but the result is can't edit the [TotalAmount} field in FORM. When I had tried to use an unbounded test box in FORM and make an equation in ON CURRENT EVENT - Me.TotalAmount = Me.ItemValue * Me.PayPeriod, it can edit the [TotalAmount] field, but it can't save it, I need the [TotalAmount] to generate report.

[@AbdulrahmanWaseem]

Dear Sir, Could you please show how to make approval flow for PR for example . Once it's approved, It turns into PO Thanks in advance AW, Cairo EG

[@599CD]

Yep. Covered that already. 599cd.com/AC328

[@JetteNielsen]

How to get access to the Queries Section, I don't even see this under Create?

[@599CD]

It should be there. What version of Access are you using?

[@JetteNielsen]

@@599CD Microsoft Office Professional Plus 2021

[@JetteNielsen]

haha I got it. My access is Danish language and Querie is called something different. Problem solved

[@ZAKARIAHAMAD-vu1up]

THANKS

[@599CD]

Welcome

[@ShaabanTech]

Hi, how i can perform min, max and average in access, l try this min:([math]![eng]) help me please

[@599CD]

I get a ton of questions every day, and I don't have time to answer them all here on KZbin. Feel free to submit your question on my website at: 599cd.com/AskYT

[@ismaelnesru4330]

HOW I CREAT TABLE AND CROSS CHAKE TABLE IN QUERY DESIGN

[@599CD]

Huh?

[@humayunkhan4012]

can any one help me... how to copy closing balance value to opening balance in reports.

[@599CD]

Feel free to submit your question at 599cd.com/TH

[@abykijana9803]

Perfect

[@599CD]

Thanks

[@synoisgeming6367]

Greeting ..nice tutorial ...i have question how to get AVERAGE 3 item in one table.. example: table Student, field: - Math - English - Science Usually I finish this problem with -> total: ([Math]+[English]+[Science])/3 but i want use-> AVERAGE(([Math]+[English]+[Science])) that still problem and not yet solved, maybe you can help me... thanks

[@599CD]

Average doesn't work that way. You need to add up the 3 fields and divide. Average can only be used in the form footer on a single field (Avg or DAvg).

[@synoisgeming6367]

@@599CD thanks for your answer ..

[@iseleinemarcellus8246]

Hello can you helph me please In Access I want to know all formule

[@599CD]

Sure... I cover them all in my course: 599cd.com/Access

[@santoshadhikari9933]

I have a table as follow: Empl Dates Hour Sum of Hours Emp1 date1 8 Emp1 date2 5 Emp1 date3 4 Emp2 date1 1 Emp2 date2 3 Emp2 date3 5 .... How do I sum hours for employees and place the sum value on each rows of in MS Access Query and by not using SQL or VBA? like Empl Dates Hour Sum of Hours Empl1 date1 8 17 Empl1 date2 5 17 Empl1 date3 4 17 Empl2 date1 1 9 Empl2 date2 3 9 Empl2 date3 5 9 and so on.

[@599CD]

599cd.com/DSum

[@slicqnick35]

the audio on your video as really awful otherwise a helpful video

[@notyet4848]

lol

[@abdelazizbourhriba9741]

Thanks the good explanation

[@599CD]

You're welcome!