calculated field in access 2010 - Access Calculated Field Tutorial 2010 2013 2016

in this video I want to show you how to make a simple calculation in your access report or in your access form by the way the exact same technique works on a form uh even though we're doing on a report right now so here we have a simple Report with a customer ID and the order ID and the order amount I would like to add the sales tax over here so we'll go back to design view and what you want to do is by the way you can add a calculated field to any of these sections on the report um but the way you do it is you're going to pick on this AB up here on the design menu and when you move your master it's say text box so I'll click there and then in this case I'm going to move it into the detail section so I'm going to put it right next to the order amount and click there now it says Unbound there so pretty soon we'll put the formula right there uh now if you look really really closely you'll see that to the left of that you have the the field label well I don't need that right now so I'm going to click there and then delete that okay and I want to make sure that this is aligned with the other field so uh we have that now now I want to get the property sheet up for that field if the property sheet isn't already open what you want to do is you want to click on that field and then come up to the design menu and pick where it says property sheet right there and that'll open up this other window so in this case I'm going to pick on the data Tab and your formula always goes into the data tab on the control Source property so you pick on control source and you pick on the three dots to the right of that and that's going to open up a very powerful part about axis which is called the Expression Builder so here's where you put your formula and the formula could be any kind of mathematical formula it could be very complicated as well uh so I'm going to double click on the order amount field in the Expression Builder notice how the field shows up in the formula and that's really why it's called the Expression Builder uh I want to say times point 7 I live in Pennsylvania here in the United States and the sales tax is uh 7% uh so I'll click on okay but this formula could have been anything really I'll clicking okay though and now notice how the control Source has that formula now you probably want to format that field uh so I'll pick on format within the property sheet and the first property does say format and I'll make the currency type of field and I might as well write justify that as well so if you notice uh here we'll pick on the home menu and I'll make I'll make it right Justified like you would in word or PowerPoint uh so now let's take a look at that I'm going to right click in the tab and then pick on print preview so notice how we have the order amount and now we have the sales tax over here uh now one additional step I might take would be to give that field a name on the column heading and also maybe to make it a subtotal as well so I'll right click click on that field and then do uh design view to give that field a title up there uh there's a couple ways I can do that I'll just make it uh an AB up here uh an a I just call a label I'll click there I'll click right about here on the page header section right above that field and then I'll just type in sales text no problem with that at all anytime you want to add text to your report or your form you add a label like that however when you wanted to add a calculated field like we did you add a text box and then that allows you to put that formula in there like we did uh in fact let's do it again so we can get the sub total so I'll pick in the ab come over here this time it's going to be in the customer ID footer section because we have we're going to have a subtotal and then I'll click there now again look for that little label to the left of that in this case I'm not going to need that so I'll click there and then delete that and I'm just aligning that fueld I'm going to go back to the property sheet so again if the property sheet isn't open already then you click on the word property sheet up here under the design menu I'm going to pick in the data tab we'll pick on control source and we'll go to the three dots which brings up the Expression Builder window now in this case I'll put the word sum and then do that same exact formula so I'll say some order amount times 007 so when I say some here it's going to add them all up for that same uh customer ID because that's that's our grouping field so you put the same exact formula within the sum there I'll click on okay uh and that shows up in the control Source I'm going to pick on format and then I'll change the format to be currency and we're looking good at that so the real Point here is um if I wanted to add a label or some text to my report I use the AA however when you want to use when you want to add a field a calculated field you pick on the a you can move that field anywhere into your section uh into your report I mean with any section uh then once that field is there you go to the property sheet for that field you pick in the data tab you go to the control Source in the control Source you pick in the three dots and that's where you can put your formula which could be anything and that is how you add a calculated field to either a form or a report in Microsoft Access let's take a look at it now you see how we have the the individual uh sales tax for each uh line item and then we have the total for the customer as well and that occurred all the way throughout the a report