Calculated Controls and Control Names

Locked lesson.

Upgrade

About this lesson

Learn calculation to Sum Field in Group Footer/Header, Page Header/Footer, or Report Header/Footer and calculation to get percent of a total in another control.

Quick reference

Calculated Controls and Control Names

Application Terminology

Calculated Control

Any control with a Control Source can be a Calculated Control.  Calculated Controls cannot be edited because they are bound to the expression in the control Source.  Calculated Controls can be used on Forms and Reports.

The most common type of control to use for a calculation is a textbox. 

The Control Source for a Calculated Control starts with =

Concatenate

Concatenate is a fancy term meaning to combine.  For instance, this equation might go into a group footer for customer to show the number of records for that customer:

="Summary for " & [Customer] & " (" & Count(*) & " records)"

Control Name

It is always a good idea to give controls a logical Name as this is how values will be referred to in calculated controls and by code.

Steps

Calculation to Sum Field in Group Footer/Header, Page Header/Footer, or Report Header/Footer

  1. Create a textbox control
     
  2. Set the Name property to SumFieldnameQualifier

    WHERE
    Fieldname is the Fieldname you are summing
    Qualifier is something to specify the section that the calculation is in, or some other way to distinguish it if it is used in other places
     
  3. Set the Control Source property to
    =Sum([Fieldname])
     
  4. The same equation can be used in different sections.  Access will calculate it right.

Calculation to Get Percent of a Total in Another Control

  1. Create a textbox control
     
  2. Set the Name property to PercentFieldQualifier
    WHERE
    Field is the Fieldname you are summing
    Qualifier is something to differentiate this control from a control in another section
     
  3. Set the Control Source property to
    =[Controlname1]/[Controlname2]
    WHERE
    Controlname1 is the name of a control with a calculation (or an expression)
    Controlname2 is the name of a control with a calculation (or an expression)
     
  4. Set the Format property to Percent
     
  5. Set the Decimal Places property to whatever you like

Show the Number of Records in a Section

  1. Create a textbox control
     
  2. Set the Name property to something logical, especially if this control will be used in another calculation
     
  3. Set the Control Source property to
    =Count(*)
Login to download
  • 00:04 This is Access 2013, Lesson 27.
  • 00:09 In the past few lessons,
  • 00:11 we have been building a grouped report.
  • 00:13 Numbers can be added, averaged, or aggregated in other ways.
  • 00:19 This can be done on each record or for any section
  • 00:23 Hi, this is Crystal.
  • 00:25 Here are the calculations we will do in this lesson
  • 00:29 We will add equations in group footers
  • 00:32 and in the report footer.
  • 00:34 Go to the Design View of r_CustomerServicesByYear.
  • 00:39 Select ServName, WSTime, and CalcAmt in the Detail section,
  • 00:45 and copy them to the clipboard.
  • 00:48 Select the Customer Footer section bar and paste, Ctrl-V.
  • 00:53 The section will expand and the controls will appear.
  • 00:57 Even though we do not want service name here,
  • 01:00 it was copied to keep the spacing between controls the same.
  • 01:04 Select WSTime in the Customer Footer
  • 01:08 and select the Data tab on the Property Sheet.
  • 01:11 Change the Control Source to =Sum(WsTime)
  • 01:20 and the Name to SumWsTimeCompany.
  • 01:26 In hindsight, this should have been SumWsTimeCustomer,
  • 01:30 to keep control names consistent with field names, and
  • 01:35 Company could mean who did the work, not who got the work.
  • 01:39 Select CalcAmt in the Customer Footer
  • 01:42 Change the Control Source to =Sum(CalcAmt)
  • 01:50 and the Name to SumCalcAmtCompany.
  • 01:54 Change the Control Source of ServName
  • 01:56 in the Customer Footer to =""
  • 02:01 which is a zero-length string (ZLS).
  • 02:05 Drop the choices for View on the
  • 02:07 REPORT DESIGN TOOLS, DESIGN ribbon, or the HOME ribbon.
  • 02:12 Choose Print Preview and notice that hours of time
  • 02:15 and the calculated amount have been added for each customer.
  • 02:20 Copy the controls in the Customer Footer section
  • 02:23 to the YearWork Footer section.
  • 02:26 The equations stay the same
  • 02:27 but the control names have changed to generic names.
  • 02:31 Change the control names to
  • 02:33 SumWsTimeYear and SumCalcAmtYear.
  • 02:39 Copy the original control name to copy and paste
  • 02:42 and make renaming faster.
  • 02:45 These controls will also be copied to the Report Footer section.
  • 02:50 To show the Report Footer section,
  • 02:52 right-click in any section that is not a control.
  • 02:56 From the shortcut menu, toggle Report Header/Footer on.
  • 03:01 For now, nothing will go into the Report Header section,
  • 03:04 so close the space by dragging its bottom border up.
  • 03:08 Click on Group & Sort on the
  • 03:10 REPORT DESIGN TOOLS, DESIGN ribbon to toggle it off
  • 03:14 so there is more space to focus on the calculations.
  • 03:18 Copy the controls from one of the group footer sections
  • 03:22 to the Report Footer section.
  • 03:24 Change the names in the report footer
  • 03:26 to sumCalcAmtGrand and SumWsTimeGrand.
  • 03:33 When you press DOWN-ARROW on the keyboard,
  • 03:35 the selected controls moved down.
  • 03:38 Select the calculated controls in each section and press
  • 03:41 DOWN-ARROW a few times to create more space above them.
  • 03:46 Bold the calculations
  • 03:48 and click Save on the Quick Access Toolbar.
  • 03:52 Go to the Print Preview
  • 03:53 and click Last Page in the lower left.
  • 03:56 You can see the calculations for each level.
  • 04:00 They aren't labeled so it is not clear what the numbers mean.
  • 04:04 Go back to the Design View
  • 04:06 and change each of the textboxes with
  • 04:09 zero-length string in the ControlSource to Labels.
  • 04:14 Right-click on a textbox control
  • 04:16 and choose Change To > Label.
  • 04:19 Change the captions to Company Totals,
  • 04:22 Year Totals, and Grand Totals.
  • 04:26 Select the footer label controls
  • 04:28 and set the Shape Outline to Transparent.
  • 04:32 Create more space above the footer controls by selecting them
  • 04:36 and pressing DOWN ARROW a few more times.
  • 04:40 On the REPORT DESIGN TOOLS, DESIGN ribbon,
  • 04:43 click the textbox tool to pick it up,
  • 04:46 then click where you want a textbox
  • 04:48 in the Customer Footer section to create a new textbox.
  • 04:53 An associated label comes with it, which we don't need.
  • 04:57 Select the label and press Delete on the keyboard.
  • 05:01 Resize the textbox by clicking and dragging a side handle.
  • 05:06 In the Customer footer section, change the Control source to
  • 05:10 ="Summary for " & [Customer] & " (" & Count(*) & " records"
  • 05:45 Fieldnames are enclosed in square brackets.
  • 05:49 When this expressions is evaluated,
  • 05:51 the current customer will be substituted.
  • 05:54 Ampersand, &, concatenates expressions together.
  • 05:57 Always put space around ampersands in equations.
  • 06:01 Concatenate means to combine.
  • 06:05 Literal values are enclosed in double-quote marks "
  • 06:09 and don't forget to include spaces inside the quotes.
  • 06:13 Count(*) returns the number of records.
  • 06:19 Close the Print Preview to go back to the Design View.
  • 06:23 Copy the calculated control that describes the results
  • 06:27 at the top of the Customer Footer section
  • 06:29 to the YearWork Footer section.
  • 06:31 Change [Customer] to [Yearwork], bold the controls,
  • 06:38 and change the outline to Transparent.
  • 06:42 Even though the same equation has been used
  • 06:45 for sums in different sections,
  • 06:48 notice that Access correctly reports the results.
  • 06:52 We do need more space before each customer starts.
  • 06:57 Go back to Design View
  • 06:58 and add space above Customer in the Customer section.
  • 07:02 In Print Preview, we can see the data
  • 07:05 is now easier to visually separate.
  • 07:09 Now let's add a calculation for the (average) amount/hour.
  • 07:13 Expand the space in the Customer Footer section
  • 07:16 and add a textbox control.
  • 07:19 Position the label above the textbox.
  • 07:22 Change the ControlSource of the new textbox to
  • 07:26 =[SumCalcAmtCompany]/[SumWsTimeCompany]
  • 07:37 Square brackets are also used around control names.
  • 07:42 Change the caption of the label to Avg $/Hr and
  • 07:47 change the control Name to AvgPerHourCompany.
  • 07:52 When you look at the Print Preview, you can see that Access
  • 07:55 is reporting more decimal places than is needed.
  • 07:59 Go back to Design View and set the Format to Currency.
  • 08:03 Select the label and textbox for average
  • 08:06 in the Customer Footer,
  • 08:08 copy to the YearWork Footer and move it.
  • 08:11 Notice that when you move a textbox,
  • 08:13 its associated label also moves,
  • 08:16 unless you drag by the big handle in the upper left.
  • 08:20 Change the ControlSource to
  • 08:22 =[SumCalcAmtYear]/[SumWsTimeYear]
  • 08:36 Copy the average controls to the report footer section
  • 08:39 and change the ControlSource of the textbox to
  • 08:43 =[SumCalcAmtGrand]/[SumWsTimeGrand]
  • 08:55 Ideally, you will change all control names
  • 08:58 even though we have not done it here, yet.
  • 09:01 Select the label and sum controls in the Customer Footer,
  • 09:05 by clicking and shift-clicking. Copy and paste.
  • 09:09 The pasted controls are positioned below the selection
  • 09:12 since there is one.
  • 09:14 When we pasted before, the controls were positioned
  • 09:17 in the upper left corner of the section.
  • 09:21 Do the same in the YearWork Footer section.
  • 09:23 These new controls will be to report percentages.
  • 09:28 Change the equation in the Time column to
  • 09:32 =Sum([WSTime])/[SumWSTimeYear]
  • 09:47 The first 'Sum' is performing a sum
  • 09:50 on WSTime using a function,
  • 09:53 whereas the second 'Sum' is part of the control name.
  • 09:56 We could have referenced the controlname in the numerator
  • 09:59 as was done in the denominator.
  • 10:02 Notice we are dividing the sum of the time for the company
  • 10:06 by the time for the year, by referencing the controlname
  • 10:10 for the sum of the time in a year.
  • 10:13 In the YearWork Footer, the time for the year
  • 10:16 is divided by the grand total of time.
  • 10:20 For the percentage of amounts, the same is done.
  • 10:24 Format the percentages as Percent
  • 10:27 and set the Decimal Places property to 0 (zero).
  • 10:32 Change the line label to "Percent of Year"
  • 10:35 in the Customer Footer section,
  • 10:37 and "Percent Grand" in the YearWork Footer section.
  • 10:42 Use the Align Top icon on the QAT to line up controls
  • 10:46 in each section and reduce extra space.
  • 10:50 Select the header labels in each of the footer section
  • 10:54 and apply Italics by selecting them
  • 10:56 and clicking I in the Text Formatting group.
  • 11:01 Add a line above the controls in the Customer Footer section.
  • 11:05 Change the Width property to 7 inches,
  • 11:08 the Shape Outline to a medium gray,
  • 11:11 and the Border Width to 1 point.
  • 11:14 Copy the line and paste into the YearWork Footer section.
  • 11:19 Paste the line again.
  • 11:21 Select the first copy of the line in the YearWork Footer section
  • 11:25 and press the DOWN-ARROW key to move it down
  • 11:28 so there is space above it.
  • 11:30 In the Report Footer section, select all the controls,
  • 11:34 and align bottom.
  • 11:36 If there is not much space above them, move them down.
  • 11:40 Paste the line 3 times in the Report Footer Section;
  • 11:44 select each line and move it down
  • 11:46 using the DOWN ARROW on the keyboard
  • 11:48 to close space between them.
  • 11:51 Save the report and look at the Print Preview.
  • 11:55 Select the textbox controls for average
  • 11:58 and their associated labels.
  • 12:01 Click the Left Align button on the QAT,
  • 12:04 then right-click on the selection and choose Size > Widest
  • 12:09 from the shortcut menu.
  • 12:12 The label increases its width to be the same as the textbox.
  • 12:17 On the HOME ribbon, click the Center icon
  • 12:19 in the Text Formatting group.
  • 12:22 Change the Shape Outline to Transparent.
  • 12:26 Now let's check our equations and Control Names.
  • 12:30 In the Customer Footer section,
  • 12:33 SumWSTimeCompany has a ControlSource of
  • 12:37 =Sum([WSTime])
  • 12:46 PercentTimeCustomer has a ControlSource of
  • 12:50 =Sum([WSTime])/[SumWSTimeYear]
  • 13:07 AvgPerHourCompany is
  • 13:09 =[SumCalcAmtCompany]/[SumWsTimeCompany]
  • 13:22 sumCalcAmtCompany is =Sum([CalcAmt])
  • 13:33 PercentSumCalcAmtCustomer is
  • 13:37 =Sum([CalcAmt])/[SumCalcAmtYear]
  • 13:52 As you come across controls without a good name,
  • 13:55 give them one.
  • 13:57 In the YearWork Footer section,
  • 13:59 SumWSTimeYear is =Sum([WSTime])
  • 14:11 PercentWsTimeYear is
  • 14:13 =Sum([WSTime])/[SumWsTimeGrand]
  • 14:28 AverageTimeYear is
  • 14:30 =[SumCalcAmtYear]/[SumWsTimeYear]
  • 14:42 SumCalcAmtYear is =Sum([CalcAmt])
  • 14:53 PercentSumCalcAmtYear is
  • 14:55 =Sum([CalcAmt])/[SumCalcAmtGrand]
  • 15:11 In the Report Footer section,
  • 15:14 SumWSTimeGrand is =Sum([WSTime])
  • 15:26 AverageTimeGrand is
  • 15:28 =[SumCalcAmtGrand]/[SumWsTimeGrand]
  • 15:42 SumCalcAmtGrand is =Sum([CalcAmt])
  • 15:53 Save the report and look at the Print Preview.
  • 15:58 In this lesson, you have learned how to add calculations
  • 16:01 in sections of a grouped report.
  • 16:04 You have seen how the same equation yields different results,
  • 16:08 based on what section the control containing it is in.
  • 16:12 You've also learned about control names.
  • 16:15 Calculated controls always start with an operator
  • 16:20 such as equal sign.
  • 16:22 If your equations doesn't work,
  • 16:24 make sure they don't start with a space.
  • 16:27 The first character must be a mathematic operator
  • 16:30 such as equal sign, plus sign, or minus sign.
  • 16:34 Expressions using the Sum function reference fieldnames.
  • 16:40 Once a control contains a calculated value,
  • 16:42 that control can be referenced in other expressions.
  • 16:47 In the next lesson we will talk
  • 16:49 about shading to differentiate records,
  • 16:51 page setup, and adding page breaks.

Lesson notes are only available for subscribers.

Gift this course