Running Sum and Hide Duplicates

Locked lesson.

Upgrade

About this lesson

Define a Running Sum, hide duplicate information and copy formats with the Format Painter.

Quick reference

Running Sum and Hide Duplicates

Application Terminology

Font Italic Property

When you apply italics using the ribbon or , the Font Italic property is set to Yes.

Format Painter

The Format Painter enables you to copy formats.

Hide Duplicates Property

The Hide Duplicates property suppresses values in a control if the value is the same on the current record as the previous value in the same control on the same page.

Running Sum

The Running Sum property for controls sums the value in the Control Source property by group or over all records.

 

If Running sum is Over Group, the sum will start over again when the group value changes..

Steps

Paste a control below another control

  1. To paste a control below another control, select the control you want to paste
  2. Cut using  (Ctrl-X)
  3. or Copy using  (Ctrl-C)
  4. Select the control you want to paste under
  5. Paste using  (Ctrl-V)

Use the Format Painter

  1. To use the Format Painter, select the control you want to copy format from.
  2. Click Format Painter on the HOME ribbon
  3. Click on the control you want to paste the format to.
  4. If you double-click the Format Painter, it will stay enabled, allowing you to click on several controls.
  5. Press  (ESCape) to end the Format Painter mode if you double-clicked to keep the Format Painter enabled.

Define a Running Sum

  1. To define a control to be a Running Sum, specify a field or expression in the Control Source property.
  2. Set the Running Sum property on the Data tab to Over Group or Over All.
  3. If you want to create a running number, use =1 in the control Source.

Select Multiple Controls

  1. To Select Multiple Controls, Click on the first control
  2. Shift-click on subsequent controls. Shift-Click toggles the selection status, so if a control is already selected, shift-click will de-select it.
  3. or Shift-Click and Drag subsequent controls.

 

Login to download
  • 00:04 This is Access 2013, Lesson 30
  • 00:09 In this lesson, we will learn how to
  • 00:11 do Running Sums and hide duplicate information.
  • 00:15 Hello, this is Crystal.
  • 00:17 Go to the design view of r_CustomerServicesByYear.
  • 00:23 We are going to add a Running Sum
  • 00:25 after the calculated amount
  • 00:28 Click on the Textbox tool in the Controls group
  • 00:31 of the REPORT DESIGN TOOLS, DESIGN ribbon.
  • 00:35 Put a textbox in the blank space
  • 00:38 of the Customer Header section
  • 00:40 since there is more space there.
  • 00:43 As usual, we get a label and a textbox.
  • 00:47 Select the textbox and press Ctrl-X to cut it.
  • 00:52 Click on the CalcAmt textbox
  • 00:54 in the Detail section and paste, Ctrl-V.
  • 00:58 Notice when you paste, the associated label comes along
  • 01:02 even though it was not specifically selected.
  • 01:06 Select the label and cut it.
  • 01:09 Click on the Amount label in the Page Header and paste.
  • 01:14 When you select a control before pasting,
  • 01:17 the new control is positioned below the selected control.
  • 01:21 Move the label next to Amount
  • 01:24 and change the caption to Running Sum.
  • 01:28 Click I in the Text Formatting group
  • 01:31 on the Home ribbon to put the text into italics.
  • 01:36 On the Property Sheet, copy the Caption, Running Sum,
  • 01:40 to the clipboard by selecting it and pressing Ctrl-C.
  • 01:45 Click on the Other tab in the Property Sheet.
  • 01:48 For the Name, backspace the numbers after Label,
  • 01:52 type underscore, then paste, Ctrl-V.
  • 01:56 Notice on the Format tab, the Font Italic property is set to Yes.
  • 02:02 On the ribbon I is indicated.
  • 02:06 In the Detail section, select the last 2 controls.
  • 02:10 On the QAT, choose Align Top and Align Left.
  • 02:15 Select the label and textbox for Running Sum.
  • 02:20 Right-Click and choose Size > To Widest.
  • 02:26 Select the labels and Left-Align them using the QAT.
  • 02:30 In the Detail section, copy the format from CalcAmt
  • 02:34 to the unbound textbox using the Format Painter.
  • 02:38 To use the Format Painter,
  • 02:40 first select what has the format you want,
  • 02:44 then click Format Painter,
  • 02:47 then click on what you want to copy format to.
  • 02:51 Select the label and the unbound textbox
  • 02:55 to align contents to the right
  • 02:57 using Align Right in the Text Formatting group.
  • 03:01 Using the Property Sheet, copy "CalcAmt" to the clipboard.
  • 03:07 In the Name property of the unbound control,
  • 03:10 select what is there, and paste "CalcAmt",
  • 03:13 then type "RunningSum".
  • 03:16 What you paste replaces what is selected.
  • 03:20 The textbox is unbound because
  • 03:23 it does not have anything in the Control Source property.
  • 03:28 In Control Source, type = and then paste,
  • 03:32 or choose CalcAmt from Intellisense.
  • 03:36 The control is no longer unbound.
  • 03:39 Close the extra space in the Detail section
  • 03:42 by dragging its bottom border up.
  • 03:45 When you look at the report,
  • 03:47 you see that the Running Sum column
  • 03:49 has the same information as the Amount column.
  • 03:52 Of course it does.
  • 03:54 The Control Source is equal to CalcAmt.
  • 03:59 Since we do not need an equation,
  • 04:01 we could simply drop the list
  • 04:03 in the Control Source property and pick the CalcAmt field.
  • 04:08 That doesn't change anything though.
  • 04:10 There is a Running Sum property on the Data tab.
  • 04:15 Choices are No, Over Group, and Over All.
  • 04:19 No is what we have now and the information is the same.
  • 04:24 Choose Over Group in the Running Sum property .
  • 04:28 When you double-click in a property with choices,
  • 04:31 the next option is automatically picked.
  • 04:34 This gives you a quick way
  • 04:36 to make changes without dropping the list.
  • 04:39 When you look at the report,
  • 04:41 the Running Sum column is correct.
  • 04:44 The numbers need to be formatted.
  • 04:46 Go back to the design view
  • 04:48 and set the Format property to Currency.
  • 04:52 The report now shows information
  • 04:54 with the currency symbol and 2 decimal places.
  • 04:59 Look at the Layout View.
  • 05:01 Notice the dotted lines showing where page margins are.
  • 05:05 The information is too wide.
  • 05:08 Click the Rate textboxes and shift-click the Rate label.
  • 05:12 On the Property Sheet, set the Width to 0.8 inches.
  • 05:17 Click and shift-click the Time textboxes and label.
  • 05:22 Also set the Width property to 0.8 inches.
  • 05:27 Do the same for the amount and its label.
  • 05:31 The report is still a little too wide.
  • 05:35 Select the Running Sum controls and label.
  • 05:38 Drag the right resizing handle until they are inside
  • 05:41 the dotted line for the right margin.
  • 05:44 On the property sheet, I see I dragged to 0.8646",
  • 05:50 which I will change to be 0.85 inches.
  • 05:55 In Design View, select the labels
  • 05:58 and use the Align Left icon for objects
  • 06:01 on the QAT to close up the extra space.
  • 06:05 Do the same for the textboxes in the Detail section.
  • 06:09 The controls do not line up in the footer sections anymore.
  • 06:14 Select all the calculated controls based on WsTime
  • 06:18 by pressing shift and lasso-ing them.
  • 06:22 Set the Width to 0.8" using the Property Sheet.
  • 06:28 Shift-click WsTime in the Detail section
  • 06:31 to add it to the selection,
  • 06:33 and click on the Left Align icon on the QAT.
  • 06:37 Select the CalcAmt control
  • 06:39 and all of its controls clicking,
  • 06:41 then shift-clicking, or shift-click-and-drag.
  • 06:45 Use the shortcut menu to Size > To Narrowest,
  • 06:51 then click Left Align on the QAT.
  • 06:54 Close the extra space on the right
  • 06:56 by dragging the right border.
  • 06:59 On the Print Preview,
  • 07:01 we see duplicate information when service is the same.
  • 07:05 Go to the Design View and select ServName.
  • 07:09 On the Format tab of the Property Sheet,
  • 07:11 near the bottom, you will see Hide Duplicates.
  • 07:15 Set Hide Duplicates to Yes.
  • 07:20 The report now hides duplicate values in service name and
  • 07:24 makes it easier to see when the same service was performed.
  • 07:29 When a new customer starts, the Running Sum starts again
  • 07:33 since it was set to Over Group.
  • 07:35 If the Running Sum had been set to Over All,
  • 07:39 it would keep on going.
  • 07:41 For each customer, we see the last Running Sum
  • 07:44 is the same as the total for the customer.
  • 07:48 At the top of page 2,
  • 07:50 we can no longer see who the work was for.
  • 07:53 In the next lesson, we will learn how to repeat sections.
  • 07:57 The running sum is nice,
  • 07:59 but we have no way to see the total by service.
  • 08:02 In the next lesson,
  • 08:03 we will also add a grouping for service.

Lesson notes are only available for subscribers.

Gift this course