New Feature: Playlist Center! Pick a topic and let our playlists guide the way.

Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Calculating fields

From: Access 2010: Forms and Reports in Depth

Video: Calculating fields

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.

Calculating fields

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.

Show transcript

This video is part of

Image for Access 2010: Forms and Reports in Depth
Access 2010: Forms and Reports in Depth

38 video lessons · 14178 viewers

Adam Wilbert
Author

 
Expand all | Collapse all
  1. 1m 27s
    1. Welcome
      1m 2s
    2. Using the exercise files
      25s
  2. 15m 53s
    1. Introducing forms
      2m 41s
    2. Designing for the end user
      45s
    3. Exploring the database
      1m 49s
    4. Creating a form with the Form Wizard
      6m 43s
    5. Refining the form in Layout view
      3m 55s
  3. 24m 33s
    1. Organizing the form elements
      7m 14s
    2. Formatting
      4m 48s
    3. Modifying the form through its properties
      6m 56s
    4. Adding a header and some polish
      5m 35s
  4. 1h 2m
    1. Introducing form controls
      3m 48s
    2. Using lines and rectangles
      2m 48s
    3. Organizing screen space with tabs
      4m 47s
    4. Adding buttons
      5m 3s
    5. Linking to external content
      4m 15s
    6. Entering and selecting data
      5m 8s
    7. Controlling input with option groups
      6m 0s
    8. Attaching documents
      6m 49s
    9. Attaching images
      5m 8s
    10. Understanding the subform control
      4m 13s
    11. Adding charts
      7m 9s
    12. Linking controls
      7m 41s
  5. 21m 42s
    1. Creating the main menu
      8m 49s
    2. Adding a splash screen with startup options
      5m 35s
    3. Creating a customer form
      7m 18s
  6. 45m 20s
    1. Grouping and sorting data
      4m 36s
    2. Understanding report structure
      6m 12s
    3. Building reports from wizards
      5m 0s
    4. Building reports from queries
      6m 34s
    5. Formatting conditionally
      6m 59s
    6. Calculating fields
      4m 35s
    7. Adding the finishing touches
      4m 49s
    8. Populating pre-printed documents
      6m 35s
  7. 15m 8s
    1. Printing reports
      3m 6s
    2. Tweaking the design
      7m 10s
    3. Automating the workflow with macros
      4m 52s
  8. 58s
    1. Next steps
      58s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold

Are you sure you want to delete this note?

No

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.