Calculated Field in Access - How to add a calculated field in an Access Table Access 2016 2013 2010

you in today's video I want to show you how to deal with the calculated field data type in Microsoft Access here we have the orders table and I'd like to add some fields to that so I'm gonna right click on the orders table and a pickle in design view this will work in Access 2000 think the calculated fields started in Access 2013 and Excel TX is 2016 I'm going to scroll down now you can put the field anywhere within the table here I'm just putting it at the bottom but you could put it anywhere here we have an order amount field let's say I like to have a sales tax and I let the sales tax to be 6% of the order amount I'm using 6% because I'm here in Pennsylvania in the United States and our sales tax is 6% alright so we'll call the field sales tax I'm going to tab over to the data type column and I'm going to click on the pulldown and notice how we have the calculated data type I'm pretty sure it started in Access 2010 maybe 13 but I know it's been around at least since 2013 I'm gonna go ahead and click on the calculate a data type you're going to get a really powerful window that's called the expression builder now this formula can be as simple or as complicated as you needed to be notice in the second column you have your field names so I'm going to make this one nice and simple double click on order email now when you double click on a field here it's gonna put it into the expression builder now you could have typed that word order amount in but I find it quicker to double click in the field and also the brackets have to be there and when you double click on it it puts the brackets down otherwise you'd have to type those in yourself so then I'll say times point zero six which is going to be 6% like I said mathematically this could be as simple or as complicated as you need to be and you could use other fields in the table to get to the other fields if that they're not there then I'll pick on the orders table there's some built in the function you can use there's all a lot of the access built-in functions there's a constant you can use and there's operators like + and minus and greater than or less than these kind of things okay so you can you know build all the formula based on all those different components and make it as simple or as complicated as you need it to be I'm gonna click on OK now we'll give that formula a format and we'll make that a currency data type good let's try another calculators field so up here I have a field that's called order date see it and maybe the due date is going to be 30 days beyond the order date so I'll type in due date for the field name and I'm going to go ahead and make that a calculated field good now we're back to the expression builder so I'll double click on order date and I'll say plus 30 now when you add a number to a date it's going to assume that it's going to be in a days so I'm gonna say plus 30 it's going to be 30 days I'll click on OK so let's do another one we'll have the aligned total for the line total we'll make that a calculated field now in this case we'll take the freight plus the order amount plus now I want to use the sales tax field that I built before notice how it's not in there yet so let me show you what you do I'm gonna pick on the word cancel right now and we'll come back to that field a little bit later on in fact I'm going to delete that field what we're going to do is we're going to save the table this will be important I'm going to save the table by saving the table it'll make these other fields available for us so this time I'll type in line total we'll make that a calculator now I'll double-click on free plus the order amount plus the sales tax notice how the sales tax and the due date are there now because I had saved the table so you can certainly use other calculated fields inside of a new calculated field I'll click on OK there so when I go into the expression builder we can make that comp we can make it as complicated as possible or as simple as you would like as well now let's see the results of what we did I'm gonna go ahead and close out of the the orders table and of course we'll save it now those fields would also be available in a query or on a form or on a report but let's see what they look like so let's come over here here's the order amount now let's do some simple math here so I'll type in 6 had been $600 now $600 times 6% that should give me $36 CR and you can see it recalculate it when I typed in the number for the order amount and then it took the order amount plus the sales tax plus the free let's see what the freitas 32:38 plus 600 plus 36 and that's why we have 668 38 over there in the line total so that recalculate it as well let's try it with a round number let's type in $1000 down okay so we have 60 so 10 60 then let's take a look what the freight is the freight is 1161 1161 plus the 10 60 is going to give us 1071 so you can see how the sales tax we calculated the line total we calculate it as well now let's go back I notice how I have to recap we format the line total as a currency field but that's not a big deal if you're watching this video I'm sure you know how to read in your fields now notice the due date let's change the order date as Nicole let's put in a new order so I'll come down here and pick on a new order and we'll pick a customer and I'll pick an employee now let's say for the order date I want the current date a keyboard shortcut to get the current date is ctrl semicolon just like you would use in Excel I'll do ctrl semicolon and that is the date that I'm recording this video now let's come over here and for the afraid I'll put in zero for the order amount I'll type in $100 let's see what happened with our calculator fields the sales tax became six dollars that all right that's six percent of the 100 the order and the due day became 213 that is correct that's 30 days from the current date so the due date came out to be proper and then the line total is the one hundred plus two six hours and we got the one hundred and six dollars so when you go back to design view on a table now you can change a field into a calculated field and then it would bring up the expression builder or when you add a new field you can change the data type to a calculated field and then you would use the expression builder to to build your calculation and it could be a really simple formula or a more advanced formula hopefully you got some insight on how to build a calculated field here in Microsoft Access you