Urge Calculated Field with airSlate SignNow

Urge calculated field

hello again everyone and welcome back to programming in Access 2013 my name is Steve Bishop and today we're going to be continuing our section on reports and today we're going to talk about grouping and sorting and also adding some different calculations to our reports that might be helpful to the user so let's go ahead and go back into our Access database here and just so you can see what a report looks like currently again we've got our three different addresses that are all separated by this nice little line here and I'm going to go back into the design view and let's say that I want to take those users or take those addresses and group them together I probably didn't need to hop out of there but you can see there's kind of this separation here between the smiles incorporated up here and then this smile is incorporated down here and it's separated by this hamster wheels inc and that's kind of inconvenient we want the smiles incorporated ones to be together and this hamster wheels inc to be separate so what we can do is we can go into the design view and then we can click on the design tab and there's this button here called group and sort if you click on that you'll get a panel down here at the bottom where we can add groups and sorts to our report so I'm going to go ahead and click on add a group and now I have the option to select from any one of the fields that our record source has and since I want a group based upon that customer name that's the one I'm going to go ahead and select you'll notice that there we get this customer name header up here up at the top this is a new design area here that will appear at the top of each one of our groups so it's going to group our details together and then we have this header above each one of those groups group rows together so what I'm going to do is I'm going to take the customer name and I'm actually going to move it up to our header and I also want to change the size so that it's nice and clear that hey this is in effect a grouping just to make it a little bit more clear oops I didn't want only want to do that want to move this over here make right size make this the right size and adjust this a little bit alright the other thing I'm going to do is I think I want to move the address type over here as well move it to where the spot was where the customer was and let's see what that looks like right now I don't think this is going to be a good finished product and you'll see that this really does take some tinkering to make it look right yeah see it's not quite how I want maybe I want a little bit more separation between the customer name and that bottom line there so let me let me go in here and I'm going to move this down again I'm just pressing the down arrow and then I'll add a little extra space above our customer name each time so let's see what that looks like that's getting a little better I got the gray back here behind it I want to get rid of that so let's see right-click alternate fill back color is none and remember i just right-clicked on the bar that is of our header so now what do we have I think that looks pretty good I'd say that that looks pretty decent here you can see we've got our hamster wheel zinc is the customer name here's one address and then over here is our smiles incorporated and we have two addresses for them so that looks pretty good now just to show you I can just like as I have on the the page header and the page footer I can also add a customer name footer okay the way you do that is down here in our little panel down here that we have our groups in our sorts you can click on the more' button and this will give us more options of what we can do with our group here and I'm not going to go into each one of these you can tinker around with them and you know figure out what each one of these options are for yourself but for right now I'm just going to click on this option that basically adds a footer section okay and you'll see this is just going to give us a little bit of space here there's some little extra space now below each one of our groups if I just put in something here I don't know let's do a you know what let me go ahead and I'm going to add a count to this and this is going to look kind of ugly but at least you'll get the idea let's say I want to know how many so you know under right now the way this looks we don't really have a count of how many of these smiles incorporated customers addresses that we have you know how many addresses do we have for small incorporated that might be an important thing to know on a report maybe not particularly this report but it's still something that might want to go on a report so in this box that I just created here this is what I'm going to do I'm going to go ahead and make it a count of addresses so I'm going to change the label here to total addresses and let's move it around a little bit here all right and let's get rid of the formatting here no background no border and make sure that it is aligned all the way to the left okay so now when we've got a value in here and a little butt up against the end of the total addresses here all right so now I need to give just like all these other things you'll notice we've got a concatenated string here we've got our addresses in here what we can do for this particular textbox is we can actually do some calculations from our report so I'm going to go into the data tab and in the control source I'm going to click on the ellipsis and we get our fancy expression builder again now in our expression builder and I'm not going to go over everything that's in expression builder you're welcome to explore everything that's in here because there's a lot of functionality that this expression builder presents to us and it's a very very handy tool one of the best parts of access really I'm going to go into the built-in functions and under the built-in functions if I scroll down to the text egg or the sequel aggregate excuse me I have different types of a cow Galatians I can do on some of the data and one of them the one in particular that I want is this count and if I click on it you can see it calculates the number of Records returned by a query so I want to get the number of records returned by a query but even more so since I'm putting this in our grouping since I putting it in our group it's actually an account how many total rows I get or how many total I should say total number a you know basically a count of how many records I have for that particular group I know that's kind of complicated but once I put this in you'll see what I'm talking about I'm going to group it according to the customer I'm going to count the customer name and click OK and you'll see that now I get a total addresses of 1/4 hamster wheels Inc and a total addresses of 2 for smiles Incorporated I could put this information anywhere and really that particular count or the total or like you may have seen there's a min there's an average here I'll go back in here to the expression builder there's really a lot of other types of things we can do for our data built-in functions go down our sequel aggregate so we can do an average so if we were like for example doing a profit and loss type of thing you may want to have some sort of averages like what's the average amount that you had on sales you know how was the average number of total sales per month so maybe we're grouping according you know maybe we're doing a group according to month you know all the sales in January all the sales in February and then you may want to have an average price of you know an average sale per you know for that particular month you could do all sorts of different types of what they call aggregates and count is the one that we did here but average max-min sum all those are really something that we're probably going to get on when we do the VBA because these are actually calculations you can do in VBA you can also do them within a query in order to get the same type of information so I really urge you to look and investigate what these different sum average count max mins what these all mean because they will become very important when you're trying to to calculate your data and give back your customer or your your user the right kind of information that they're going to want all right so let's go back and account put in customer name click OK I'm going to also drop the alternate background here I'm going to save it and I'm going to do one more thing I'm going to say on the customer footer I'm going to go under where is it that here we go under the format tab what I can say is I want to force a new page after each footer so what this is going to do is I'm going to force access to make and the next grouping its own separate page that's essentially what I'm doing I'm saying after the footer section okay after this particular section force a new page to start for our data and what we'll see now is only one customer shows up per page so that I go to this page - and there's our smells incorporated see how that works so I can actually separate out our report according to the customer name and give the total addresses and they would all print out maybe not on one particular page they might you know if you get like 50 obviously it'll go to you know page 1 page 2 page 3 and then after that it will be sure that no matter where you're ending up on that page with your information you will automatically start a new page when you get to the next customer name alright I hope that's really important to you and that you've learned something these reports are very very to really get down because they are a very big way that you expressed the data and present it back to your user and if there's anything that you want to learn if there's anything more about reports you would like to see done which I'm sure there probably is please feel free to send me a message right here on YouTube just send me a message and I'll see if I can if I can't answer your question directly via email maybe I'll go ahead and make a video about it alright so thank you very much and we'll see you in the next video