Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
We've seen how report stay connected to a data source through the record source property, which is linked to either a table, an embedded query or in the case of our lifetime sales report a linked query. But we can actually calculate additional values right here inside of our Report using unbound text boxes, let's go ahead and right click on our Lifetime Sales report and open it straight into the Design View. What I would like to do is add some calculated fields that add up the number of customers inside of each state and then adds up a total value that each state is brought to the company, we can do that with two text boxes and we could do that in a site footer section.
We'll go up to our Group & Sort button here on the ribbon, I'll turn that on and for StateName where it say More I'll open that up and over here on the right, right now it says without a footer section I click the down arrow and change that to with a footer section. That'll add a new section here called StateName Footer that will appear after every state. Now I can put my calculated field inside of this box, I'll go up here to Text Box and I'll add two text boxes into the StateName Footer section, I'll click once and add one in and I'll click second time and add a second one over here under Lifetime Sales.
I am going to go ahead and change the labels associated with them now, I'll double click on this first one and this first one I'm going to type in Numbers of Customers followed by colon and in the second one I'll double click and type Total Sales followed by a colon. Let's go ahead and arrange these into position, I'll click on all four and I will go up to the Arrange tab>Align>to Top. Now I will just space these out a little, I'll deselect them and I'm going to move this label over using the handle here and I'll move Total Sales closer to this box there.
Now I'll right align these two labels so that they appear against the text box go to Format>Alignment>Right. And finally I want to align these underneath the values above, so I'll move this over here and this one looks about right there, okay. The next thing I want to do is change the calculation that'll appear here in the box. For each text box we can go to the Data tab of the Property Sheet and type in the value. We can either type the formula right here if we know it or we can use the build button here to open up the Expression Builder that'll help us construct the expression.
So this first box is going to add up the total number of customers, we can do that with the counting function. If you want to find it here we can find it in the Functions folder, Built-In Functions, scroll down to the SQL Aggregate group>Count, I'll double-click to add that to my Expression up here. The next thing you want to know is, well what are we counting? I'll click in the Expression, close that up, I'll go into my report here, I'll do under the Access database Reports> Loaded Reports the Lifetime Sales report and I'll just add up the number FirstName, I'll double click on that to add it up here, we'll go ahead and say OK.
So that adds that calculation here into the box of the Control Source. Let's go ahead and do another one for this one the Total Sales that'll add up the Lifetime Sales across the state, we'll click here and this time we'll just type in the value there is a pretty simple formula it's going to be a sum, so we will say =Sum open parenthesis, open square bracket Lifetime Sales with space closing square bracket and closing parenthesis, go ahead and press Enter to finalize that and we'll take a look at our report. Go Home change our View to Print Preview.
After a moment the report runs and I can see I've got these two calculated fields now, I've got the Number of Customers in each state and the Total Sales for each state before it starts the next state. Let's go ahead and clean this up a little bit I want to make these things align and get rid of this text boxes, so I am going to go back to Close Print Preview, I'm going to highlight both of these go to my Format>Shape Outline and Transparent. Now I'm going to make sure that these are all align properly look like the number is here we are formatted a little bit to the right, we'll just format the Lifetime Sales and summary calculation to the right.
And finally I want to create the size of the footer a little bit we'll change it's background color using the paint bucket here and I'll change it to a little green color, it's Olive Green, Accent 3. And maybe I'll add a line across the top to denote that this is the footer section we have moved on from data, I'll click all four of these boxes by Shift+Clicking moving down a little bit, we'll go to the Design tab and I'll add a line object here. Now I'll add a line right across there and I move it up so it's tight against the section, go to Home change the view to Print Preview and there is our boxes.
So calculating controls can be pretty straightforward and the Expression Builder can help you construct the formula for any of the functions available within Access.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 74284 Viewers
80 Video lessons · 129608 Viewers
52 Video lessons · 63870 Viewers
59 Video lessons · 49659 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.