part six of our series on queries in microsoft access databases and we're going to be looking at a calculated field or fields that we can use in queries now what is a calculated field well first of all the little tip that we gave you in the first series on tables was that you shouldn't store any data in your database tables that can be calculated because you're just wasting space anything that can be calculated like normally with the information that you've got don't store it because you can calculate now i said that now how do we calculate well in queries you can calculate the values so we can have a special column which has the calculated value or calculated field and there are three steps to calculated fields if you don't know how to do a calculator fields i would recommend that you follow this three-step program to help you do your calculated fields so step one what do we need to do well in the unused column so a column that's not used in your query design right at the top of the column in the field row the right at the top not in the criteria the field row enter in an equal sign so let's look here so there we can see a query let's be we've selected the first name the surname the pay the number of payments and they're in a new column right at the top not in criteria but right to the top there in the field that's where you put an equal to sign that's step one now you know where you're going to be typing it in step two now you are going to enter in a formula and if you need to use values from other fields you can use those field names but remember you must use them as they are spelt in the database so let's say in this scenario we want to work out the average number of or average paid based on the number of payments so if you've paid 100 rand you made five payments in the average payment would have been 100 divided by five if that makes sense so we want to take that paid field and divide it by the number of payments field so there we go i'll type it in there's my formula i've used the field names um you can use other numbers if you want to work out like the vat you can times about 1.15 or whatever you want or 0.15 if you want to work out bat values and stuff like that um so you can type in normal numbers but if we referring to field names in those calculations use them as they are spelt so there we go so that's the first that's the first step and the second step done what is step three we're nearly there step three of our program step three is you press enter once you've done that and then some text will be added automatically to your calculated field in the front and then it'll be a colon and then it'll be your actual calculation that you just entered so it looks something like that you'll press enter boom and like okay it said exp1 colon and then your formula you'll notice there that the the formula has got uh square brackets around it now the values just a reminder that i recommend that you do not use spaces in your field names um so if you do by sometimes have a database where there are spaces in the field names then in your calculation make sure that you put square brackets around the names because otherwise it's going to think that num space payments would are two different fields so just put square brackets around your fields if there are spaces if there are no spaces just type them as it is and then access will do the rest for you but we are step three here that a exp r1 what is that what is that that is the the label or the title that's going to be put in the column at the top so this calculated field is gonna have the exp r1 right at the top this is what the label for this column this field is going to be so we don't want it to say xperia 1 we want to give it our own value so we're going to say hey i want to call it um i'm going to change that exp r1 i want to change it to average payment that's the title i want and so there we go i put in average payment if you want to put spaces in put anything double i put it in square brackets so that it knows that it's one thing so there we go average payment and so there is my calculated field i've got a calculated field called average payment and the calculation for it is taking the paid field and divided by the number of payments field what does that look like well it looks like this so there we go we've got all our fields and there at the end there's average payment do you notice at the top it's called average payment that's the name that we gave it if we left it exp1 it would say expr1 at the top there so there we go there is our calculated field now the one thing i don't like about this calculator field if you know just like we're dealing with paid which is a currency divided by a number so technically average payment should be a currency and that's just a number i don't like how that looks so if you want to change that you can go back to your calculated field and you can right click on that column for average payment and you'll get the options to change the properties so change the properties and you can change the format to a decimal uh or change it to a currency or something like that if you wanted to do that you could do that okay let's look at this example just let's have a look here so we want to find the average this looks exactly the same but it's not look there then the field's called number of num payments but in the calculator field they use the word number of payments that there is no field called number of payments it's none payment they spelt it wrong now what happens if you do make a mistake and you spell something wrong well a box like this will appear when you go to view the query so when a box pops up and it asks you for a value for a field you go but that should get it from the field that means you've spelted something wrong so that number of payments you know is wrong so that's how you know you must go and correct it so just to recap our three-step program on how to do a calculator field step one in the unused column at the top in the field row enter in an equal to sign step two enter a formula and if you're using fields in your formula make sure that you spell them correctly if they have spaces remember to put them in square brackets and three press enter and then this whole text will appear with a codon in front of it just change that little text that just appeared to whatever name you want to put at the top if you know how to the format of physical field you can just type in as is but if you're not too sure if you forget just follow this lovely three-step program so yeah we've got our database with some lovely data in so let's do some calculations i'm going to go and create a query query design and we're going to add the table boom close this so we want to put the name first name and surname and we want to work out the average number of payments so i'm going to come here to i want how much is paid and i want the average number of payments so if that's the case so look at step one at the top in the field row we're going to put an equal to sign now before i do that just so that you guys can see everything i'm just going to make this a little bit bigger so at the top in the field row seven step one step two type in your formula well we want to take the paid field divided by the number payments field but remember it must be spelled exactly the same oh none payments no oh that's wrong so we must make it exactly like it is none payments step that's step two step three press enter and it will put in this expr one and it'll put the fields in that in square brackets i'm not worried about the square bracket i'm worried about this if i forget step three and i just run the query it'll do the result but you see that xpr one's at the top i don't want that i want that expr one to be the word average payment average payment and then we go run it so we can see it there we go we've changed the name done all three steps run it and there we go there's the average payment so they've made a payment of that amount and there's only one of them so obviously that's going to be the average payment just hit so there we go so that's great now if i don't want to change that that value i want to say you know what i'm going to right click on this go to properties and we're going to change that format to some sort of decimal number or currency currencies better boom so there we go now it looks a little bit better so there we go all the different payments average payments but boom yay so now we can see all our values and that's great maybe i want to add in a that column oh let's go add some vet let's close this property um the vet amount that amount well let's follow the steps equals what do i want to do i want to find the that amount on how much they've paid which is 15 percent of the paid so equals step two fifteen percent that's 15 divided by a hundred percent is times fifty percent of is of means time so fifteen percent of times the paid field so type in the paid field you can press enter or click away we'll change that to the vat amount [Music] and if i run it there you can see 15 of the amount paid and there's the vet amount which i can change to a currency as well if i change pay to paid so it's wrong i'll run it now oh now you can see what that box looks like in there i made a mistake go cancel let's go back oh no it's paid not paid okay so those are the three steps in doing a calculated field in our next video we'll show you some other formulas that you can use in your calculated fields that can have give you some extra options with dates and text and so on so go watch that next video for the other video that i just mentioned as well as other videos on access go to our youtube channel subscribe click on the like button we'd love to hear from you so leave a comment and remember don't do it the long way do it the mr long way