Locked lesson.

Upgrade

About this lesson

Create a report from scratch using Report Design, add field to Record Source and align controls.

Exercise files

Download this lesson’s related exercise files.

Services_23_Start ReportFromScratch.accdb
1.6 MB Services_23_ReportFromScratch.accdb
1.7 MB

Quick reference

Design Report from Scratch, Add Field to Record Source

Application Terminology

Caption Property

The Caption property for a control defines what will be displayed in that control when viewed.  The Caption property of a form or report is what will be displayed in the title bar when the object is opened or previewed.  For a label, the Caption property shows what will be displayed in the control.  Users see captions.

Left Property

The Left property determines how far from the left margin a control will be displayed.

Name Property

Every control has a name that will be used to refer to the control.  Users do not see the control names.  It is a good idea the change the Name property to be logical.  For bound controls, you can set the Name to the same as the Control Source.

Record Source

The Record Source of a form or report is the name of a table, query, or SQL statement.  The Record Source specifies the fields that will be available.

Report Design Command

The Report Design command creates a blank report with a Page Header section, Detail section, and a Page Footer section. It opens in Design View.

Top Property

The Left property determines how far down from the top of a section that a control will be displayed.

Width Property

The Width property determines how wide a control will be.

Steps

Plan a report

  • To plan a report, first sketch out what you want on a piece of paper.

Create a Report using Report Design

  1. To create a new report, click on the CREATE ribbon
  2. Choose Report Design in the Reports group
  3. If the Property Sheet is not showing, press  (Alt-ENTER) to turn it on
  4. Select the report object by clicking the square in the upper left where the Rulers intersect
  5. On the Data tab of the Property Sheet, choose a Record Source
  6. Click Add Existing Fields from the REPORT DESIGN TOOLS, DESIGN ribbon
  7. Drag fields to the design surface
  8. Customize by setting properties, resizing, moving, and deleting controls

Add Field(s) to a Report Record Source

  1. From the Design View of a Report, select the report by clicking in the upper left where the rulers intersect. You will see a black square.
  2. On the Data tab of the Property Sheet, click on the Builder Button  (...) for the Record Source
  3. Make desired changes
  4. Save and Close

Align Controls

When 2 or more controls are selected in the Design View of a Form or Report, they can be aligned with each other.  Either:

  1. Right-click on the selection and choose Align and then Left, Right, Top, Bottom, or To Grid from the shortcut menu

     
  2. or, if you have customized the QAT to add Align object commands, click on one of the Align icons

Move a Control without Moving its Associated Label

  1. To move a control without moving its associated label (or vice-versa), click on the big square in the upper left and drag.
  2. You can also set its Left and Top properties on the Property Sheet when it is selected.
Login to download
  • 00:04 This is lesson 23 of Access 2013
  • 00:09 It is important to have accurate reports with a professional look.
  • 00:13 You know what you want,
  • 00:15 and I am going to show you how to get it.
  • 00:17 Hi, this is Crystal
  • 00:19 In this lesson, you will learn how to make a report from scratch.
  • 00:23 I will also show you how to modify the data source.
  • 00:27 When you are going to create a report,
  • 00:29 first design what you want on paper.
  • 00:32 Year, Company, and Service Type will be in Group Headers.
  • 00:36 The Detail is service name, rate for service, time spent,
  • 00:41 quantity if the service is not a fixed price, time type,
  • 00:46 which specifies fixed, each, or rate type such as hourly,
  • 00:51 the work date, and calculated amount.
  • 00:54 In a later lesson, we will lean how to use running sums.
  • 00:58 The detail records are shown with blue lines.
  • 01:01 At the end of each company, there will be calculations.
  • 01:05 There will also be calculations at the end of each year.
  • 01:08 At the bottom of the report, grand totals will be calculated.
  • 01:12 Normally, Service would also be a grouping,
  • 01:15 but because I want to show you how to do running totals,
  • 01:18 we will simply sort by Service.
  • 01:21 Between each year, there should be a page break
  • 01:24 Each Company should also start on a new page.
  • 01:28 You could rearrange this report to show work by Service
  • 01:32 so that you can see which services you do the most.
  • 01:35 Click on the Create ribbon and choose Report Design.
  • 01:40 Maximize the report design if you are not already maximized.
  • 01:45 Turn on the Property Sheet if it is not showing,
  • 01:47 and click on the Data tab.
  • 01:50 To select the report object,
  • 01:52 click in the upper left corner, where the rulers intersect.
  • 01:56 In the RecordSource property, click the drop-down arrow
  • 01:59 see a list of tables and select queries.
  • 02:02 Choose q_CustomerServicesByYear
  • 02:07 Turn on the fieldlist and select all the fields
  • 02:11 except the three (3) key fields at the bottom,
  • 02:13 which we will add later when we talk about hiding sections.
  • 02:18 Drag the selected fields to the design surface.
  • 02:22 Delete the labels for YearWork, Customer, and ServType.
  • 02:26 The remaining information will stay in the detail section
  • 02:30 and be arranged horizontally.
  • 02:32 Drag the labels and textboxes
  • 02:34 by the big handle in the upper left
  • 02:37 so they move independently of their associated control.
  • 02:41 Create more space on the design surface
  • 02:43 by dragging the right border of the report design area
  • 02:47 when the mouse shape changes
  • 02:48 to a vertical line and double-headed horizontal arrow.
  • 02:52 Select the detail controls and their labels.
  • 02:56 Set the width on the Property Sheet to 1 inch (2.5 cm).
  • 02:59 Click on the QAT icons to bottom align controls
  • 03:03 and to right-align controls.
  • 03:06 Select the service name (textbox) and associated label.
  • 03:09 Make them wider by dragging
  • 03:11 one of the sizing handles on the left side.
  • 03:15 Use the Property Sheet to set the width to 1.5 inches.
  • 03:20 Set the width of the quantity textbox
  • 03:23 and its label to be 0.7 inches.
  • 03:28 Select all the detail controls and labels.
  • 03:31 Use the QAT icon to left-align them.
  • 03:34 Oops! The QAT needs to be modified.
  • 03:38 Instead of the icon to left align controls with each other,
  • 03:41 we have the icon to left align content within a control.
  • 03:46 Click the drop-down arrow on the end of the QAT
  • 03:49 and choose More Commands to customize it.
  • 03:52 When the dialog box pops up,
  • 03:54 drop the list to see All Commands.
  • 03:58 From the list on the left, add the object Align Left icon
  • 04:03 below the content Align Left icon in the list on the right.
  • 04:08 Once you position the active selection on the right,
  • 04:11 double-click the icon on the left to move it over.
  • 04:14 Then click on the content Align Left icon in the list on the right
  • 04:18 and press the Delete key on the keyboard to get rid of it.
  • 04:22 Click OK to dismiss the Customize dialog box.
  • 04:26 Left align the selected controls and drag the selection
  • 04:30 to the left as far as it will go.
  • 04:33 Select just the labels and press Ctrl-X to cut them.
  • 04:38 Click on the PageHeader section bar and press Ctrl-V to paste.
  • 04:43 Make the Page Header section taller
  • 04:45 by dragging its bottom border down.
  • 04:48 On the Property Sheet,
  • 04:50 set the Top property of the labels to 0.25".
  • 04:55 We are missing the time type
  • 04:57 so let's add it to the RecordSource.
  • 05:00 Select the report by clicking in the upper left
  • 05:02 where the rulers intersect. You will see a black square.
  • 05:06 On the Data tab of the Property Sheet,
  • 05:09 click on the Builder Button ... for the RecordSource.
  • 05:13 Right-click in a gray area of the query designer
  • 05:16 and choose Show Table from the shortcut menu.
  • 05:19 Double-click the TimeTypes table
  • 05:22 then close the Show Table dialog box.
  • 05:25 There are 2 relationships to the TimeTypes table.
  • 05:29 The one we want to use is from Work Services.
  • 05:33 The Services table has a time type to use as a default
  • 05:36 but that can be overridden in WorkServices,
  • 05:40 so delete the relationship from Services to TimeTypes.
  • 05:44 Click on the relationship line to select it
  • 05:47 and press the Delete key on your keyboard.
  • 05:50 Drag the TimeTy field to the grid over the CalcAmt column.
  • 05:55 When you look at the Datasheet View, you can see
  • 05:57 that time type has been added.
  • 06:00 Click the X in the upper right to close the designer
  • 06:03 and then click Yes to save the changes.
  • 06:06 Drag TimeTy from the fieldlist to the design surface.
  • 06:10 Sometimes Access gets confused
  • 06:13 if a RecordSource has just changed.
  • 06:15 Use the Property Sheet to ensure that TimeTy
  • 06:18 is the ControlSource of the textbox.
  • 06:21 Set the width of the label and textbox for TimeTy to 0.7 inches.
  • 06:28 Make space for time type between Qty and WorkDate.
  • 06:32 Select the labels and textboxes for WorkDate and CalcAmt
  • 06:36 by clicking and dragging up and over.
  • 06:39 Anything the imaginary rectangle touches becomes selected.
  • 06:43 Let go of the mouse, click on the selection
  • 06:46 and move it to the right.
  • 06:48 Click on the big handle in the upper left
  • 06:50 to move the TimeTy textbox.
  • 06:53 You cannot drag the label to the header area
  • 06:55 since it is still associated with the textbox.
  • 06:59 Cut it by pressing Ctrl-X.
  • 07:02 Click on the Page Header section bar and paste, Ctrl-V.
  • 07:06 Move the label between Qty and WorkDate.
  • 07:11 Select all the labels and click Align Top (or Align Bottom),
  • 07:15 then Align Left from the QAT.
  • 07:18 Select the textboxes and choose Align Top and Align Left.
  • 07:23 Move the textboxes up.
  • 07:26 Close the extra space in the detail section below the controls.
  • 07:31 Scroll to see the bottom border and drag it up.
  • 07:33 Change the labels to have friendlier captions.
  • 07:36 Double-clicking on a word selects it.
  • 07:39 Typing replaces what is selected.
  • 07:42 Save the report and call it r_CustomerServicesByYear.
  • 07:48 Look at the Print Preview for the report.
  • 07:51 When the right border of the report needs to move,
  • 07:54 you will get a message that the section width
  • 07:57 is greater than the page width.
  • 07:59 In the next lesson, we will add Grouping and Sorting.

Lesson notes are only available for subscribers.

Gift this course