Microsoft Office Microsoft Access 2013 Office Access 2013

Microsoft Office Microsoft Access 2013 Office Access 2013 Courseware # 3255 Lesson 5: Designing Reports

Microsoft Office Access 2013 Lesson Objectives • Modify page size and orientation • Work with report headers and footers • Add logos, titles, date and time, and page numbers • Add images to reports • Align, space, move and size controls • Filter and sort records on a report • Preview and print reports © CCI Learning Solutions Inc. • Use the Report Tool • Use the Report Wizard • Create a blank report in Layout view and Design view • Work with controls in Layout view and Design view • Add bound and unbound controls • Group and sorting report records • Add calculated controls to reports 2

Microsoft Office Access 2013 What is a Report? • Reports are designed to be printed on paper • Usually they are designed to be visually appealing and they often summarize data © CCI Learning Solutions Inc. 3

Microsoft Office Access 2013 Report Sections • Header sections – Report header – Page header – Group headers (if the report includes grouping) • Detail section • Footer sections – group footers, page footers, report footer © CCI Learning Solutions Inc. 4

Microsoft Office Access 2013 Designing a Report • • Begin by deciding on the data you want to include Decide how the data may be organized into sections Decide how to arrange the data in each section Plan the report layout © CCI Learning Solutions Inc. 5

Microsoft Office Access 2013 Report Layouts • Tabular layouts resemble spreadsheets – Best for when you need to present data in a simple list format – Always span two sections of a report. • Stacked layouts display data arranged vertically with a label to the left of each control – Best for when a report contains too many fields to display in tabular form – Always contained within a single report section. © CCI Learning Solutions Inc. 6

Microsoft Office Access 2013 Controls and Properties • Reports are comprised of controls which may be bound or unbound – Bound controls display data from an underlying table or query – Unbound controls are not tied to fields in an underlying table or query • Each control on a report has properties that affect its behavior and appearance • Sections of a report and the report itself also have configurable properties • Properties can be viewed and modified in the Property Sheet © CCI Learning Solutions Inc. 7

Microsoft Office Access 2013 Report Views • The four views for a report are: – – Report view Print Preview Layout view Design view © CCI Learning Solutions Inc. 8

Microsoft Office Access 2013 Report View • Report view allows you to view the data, but you cannot edit it • You also cannot modify a report’s design in Report view © CCI Learning Solutions Inc. 9

Microsoft Office Access 2013 Print Preview • Print Preview allows you to preview exactly how the printed output will look • You can view one, two, four, eight, or twelve pages on one screen • In Print Preview, you can – – – Specify paper size and page orientation Adjust margins Specify to print only the data Direct output to a specific printer Export the report output to one of several file formats © CCI Learning Solutions Inc. 10

Microsoft Office Access 2013 Layout View • Table-based • Fields and their labels are positioned within cells in the layout • Layouts are guides that align controls • Each cell within the layout is either empty or contains a single control • Allows you to view real data while giving you the ability to modify the design of the report © CCI Learning Solutions Inc. 11

Microsoft Office Access 2013 Design View • Design view gives you a detailed view of the structure of a report, but not the data • You can use Design view to add a wide variety of controls, resize report sections, and adjust the size of individual controls © CCI Learning Solutions Inc. 12

Microsoft Office Access 2013 Creating Reports • To create a report in Access, you can – Use the Report tool – Use the Report wizard – Create a report from scratch in either Layout or Design view – Create a report using Application Parts © CCI Learning Solutions Inc. 13

Microsoft Office Access 2013 Using the Report Tool • The Report tool generates a report based on a single data source and automatically includes all the fields in the data source • To use the Report tool, click a table or query in the Navigation Pane, then click Report in the Create tab of the Ribbon © CCI Learning Solutions Inc. 14

Microsoft Office Access 2013 Creating a Report Using the Report Wizard • When you use the Report wizard, you can specify multiple data sources for a report • You can also define how data is grouped and sorted • On the Create tab in the Ribbon, click Report Wizard, then follow the steps to define the data source, specify fields to include on the report, add grouping and sorting, and specify a report layout © CCI Learning Solutions Inc. 15

Microsoft Office Access 2013 Creating a Blank Report • You can create a blank report in either Layout view or Design view • Click the Create tab on the Ribbon, then click either the Blank Report or the Report Design button • Depending on which view you use to work with the report, you can drag a variety of controls from the Controls group in the Ribbon onto the report • You can also open the Field List pane and drag fields from existing tables onto the report • In Design view, you can specify a query as the record source for a report © CCI Learning Solutions Inc. 16

Microsoft Office Access 2013 The Field List Pane • The Field List pane displays the tables in the database • You can expand any table in the Field List pane and then drag fields into a report © CCI Learning Solutions Inc. 17

Microsoft Office Access 2013 The Property Sheet • The Property Sheet includes the following tabs: – – – Format Data Event Other All • To display the Property Sheet, click the Property Sheet button or press F 4 © CCI Learning Solutions Inc. 18

Microsoft Office Access 2013 The Record Source Property • The Record Source property controls which fields are available for a report because it ties a table, a query or a SQL statement to the report – This flexibility allows you to specify multiple data sources for the fields on the report • The Record Source property applies to the report itself, and is visible in the Property Sheet only when the entire report is selected © CCI Learning Solutions Inc. 19

Microsoft Office Access 2013 Creating a Report Using Application Parts • Databases created by using Application Parts often include reports • If you like the layout of a report generated by an application part, you can reuse it in other databases by importing it • After importing the report, – Change the Records Source to an appropriate table or query in your database – Adjust the Control Source property for individual report controls as required © CCI Learning Solutions Inc. 20

Microsoft Office Access 2013 Working with Reports in Layout View • In Layout view, you can modify reports by: – – Formatting controls Moving controls Inserting, deleting, merging and splitting rows and columns Adding controls from the Ribbon © CCI Learning Solutions Inc. 21

Microsoft Office Access 2013 Adding Controls from the Ribbon • You can add unbound controls to a report from the Ribbon • Added controls can be bound to a field by setting the control’s Control Source property © CCI Learning Solutions Inc. 22

Microsoft Office Access 2013 Adding Controls from the Ribbon Select Clicking this button allows you to select one or more controls on the report. Text Box Inserts a text box control which are often used on reports for displaying calculated values. Inserts a label control which is an unbound control typically used to display titles and other descriptive text on the report. Label Button Inserts a command button. Command buttons on reports function only in Report view. Tab Control Inserts a tab control. Not applicable to reports. Hyperlink Inserts a hyperlink control. Hyperlinks on a report function only in Report view. © CCI Learning Solutions Inc. 23

Microsoft Office Access 2013 Adding Controls from the Ribbon Combo Box Inserts a combo box (combination text box/drop-down list) control. Not applicable to reports. List Box Inserts a list box control. Not applicable to reports. Check Box Inserts a check box control. Not applicable to reports. Attachment Inserts an attachment control. Image file attachments can be viewed in Report view, Print Preview, or Layout view. Document file attachments can be opened and viewed only in Report view. Image Inserts an image into the report. When you add an image to a report, the image does not change as you move from one record to another. © CCI Learning Solutions Inc. 24

Microsoft Office Access 2013 Working with Reports in Design View • While Layout view uses layouts for positioning controls, Design view uses a design grid • You can add controls to a report in Design view by dragging them onto the report from the Field List pane, or by adding controls from the Ribbon • Design view offers more granular control over where to place objects and you can use the Property Sheet to modify the properties for the report and its controls and sections • You can use Design view to adjust object size and orientation independent of other controls in the same column or row • You can also use Design view to view and manipulate report sections such as the header and footer sections © CCI Learning Solutions Inc. 25

Microsoft Office Access 2013 Adding Controls from the Ribbon • The controls that appear in the Controls group in the Ribbon in Design view differ from those available in Layout view © CCI Learning Solutions Inc. 26

Microsoft Office Access 2013 Adding Controls from the Ribbon Option Group Insert Page Break Adds a type of control containing a set of related buttons that provide a limited set of choices for the user. Option groups can include toggle buttons, option buttons or check boxes. Option groups are designed to receive user input and are more suited for use on forms, than reports. Inserts a page break control. Generally, page breaks are used to separate groups of data. Chart Inserts a chart control. Line Inserts a line as a graphic element. It is used primarily for aesthetic value, such as visually separating one area of a form or report from the rest. Toggle Button A graphic element that appears either pushed in or not pushed in. Like the check box, it is used for binary values: yes/no, on/off, 0/1. Toggle buttons are designed to receive user input and are more useful on forms than on reports. © CCI Learning Solutions Inc. 27

Microsoft Office Access 2013 Adding Controls from the Ribbon Rectangle Inserts a rectangle as a graphic element into a report. This is also used primarily for aesthetic value to visually separate one area of a form or report from the rest. Unbound Object Frame Inserts an object such as a spreadsheet, chart, slide or document into a report. The object is not tied (bound) to any particular record in the database. Option Button A graphic element previously called a radio button. Like the check box and toggle button, it is used for binary values: yes/no, on/off, 0/1. Option buttons are designed to receive user input and more useful on forms than on reports. Subform / Subreport Embeds a subform or subreport into a report. A subform/subreport is used to show related data from another form or report within a “main” report. Subforms/subreports are used to show related data from the “many” side of a relationship. Bound Object Frame Inserts a frame that will contain a bound object, such as a picture, associated with a particular record in a database. © CCI Learning Solutions Inc. 28

Microsoft Office Access 2013 Aligning Controls • You can align controls with the Horizontal and Vertical rulers • You use the snap to grid option to align controls to the design grid • You can align controls relative to one another © CCI Learning Solutions Inc. 29

Microsoft Office Access 2013 Sizing Controls • You can size controls relative to one another, specifying to size all of them to the same size as the – – Tallest Shortest Widest Narrowest © CCI Learning Solutions Inc. 30

Microsoft Office Access 2013 Spacing Controls • The vertical and horizontal spacing between report controls affects the overall appearance • You can select multiple controls and then select an option to increase, decrease, or equalize the vertical or horizontal spacing between the selected controls © CCI Learning Solutions Inc. 31

Microsoft Office Access 2013 Part I to pg. 259 © CCI Learning Solutions Inc. Pause… 32

Microsoft Office Access 2013 Working with Subform/Subreport Controls • If you want to view both summary information and its related detail information in a report, use a subform/subreport control • To add a subform/subreport control to a report, open the report in Design view, ensure that the Control Wizards are turned on, then drag the control into the report to launch the Subreport Wizard – You can specify to create a subreport based on an existing table or query – You can specify to use an existing form or report as a subreport © CCI Learning Solutions Inc. 33

Microsoft Office Access 2013 Adding Calculations to Reports • Calculations on a report help you analyze and summarize data • Calculations you can add include: – Calculated controls – Totals • Calculated controls use an expression as a Control Source property • Totals insert aggregate functions into a report © CCI Learning Solutions Inc. 34

Microsoft Office Access 2013 Grouping and Sorting Data in Reports • Grouping the data on a report can help make the data meaningful • You can add up to 10 grouping levels • You can display subtotals and grand totals on grouped reports © CCI Learning Solutions Inc. 35

Microsoft Office Access 2013 Adding Grouping Levels with the Shortcut Menu • In Layout view, right-click the field you want to group by, then click an option in the shortcut menu • You can easily sort report items in Layout view by right-clicking the field by which you want to sort, then clicking a sorting option in the shortcut menu © CCI Learning Solutions Inc. 36

Microsoft Office Access 2013 Using the Group, Sort and Total Pane • Open the report in Layout view • Click Group & Sort to open the Group, Sort, and Total pane • In the Group, Sort, and Total pane, click Add a group, then select the field by which you want to group the data • For each grouping level, you can specify options such as displaying headers, footers or totals • You can also sort items within a group • To add sums or calculations, click a grouping level, click more, then click one of the choices © CCI Learning Solutions Inc. 37

Microsoft Office Access 2013 Previewing and Printing Reports • Print Preview allows you to see exactly how the printed output will look • Print Preview also allows you to make last-minute adjustments to the report • Print Preview is the only report view that provides access to the Print command © CCI Learning Solutions Inc. 38

Microsoft Office Access 2013 Working with Page Size, Orientation and Report Width • The paper size setting controls the amount of space available in the design grid • The two available page orientation settings are Portrait and Landscape – This setting controls how the output is printed, but does not control the width of the report or its sections • The Report width setting sets the left and right boundaries of the report and is equal to the width of the design grid • Margins affect the printable area in a report as well as the overall report width • You can set the report width using the Property Sheet, and you can manually drag the right edge of the report design grid to adjust the size of the report sections © CCI Learning Solutions Inc. 39

Microsoft Office Access 2013 Formatting Reports into Multiple Columns • By default, a report prints as a single column which spans the width of the page • Use the settings on the Columns tab of the Page Setup dialog box to format a report to print in multiple columns © CCI Learning Solutions Inc. 40

Microsoft Office Access 2013 Report Headers and Footers • Report header and footer sections usually contain a logo, report title, date stamp, time stamp and page numbering • Access includes preformatted placeholders for these items • Click the buttons in the Header / Footer group to insert them into a report © CCI Learning Solutions Inc. Button Control type Default location Logo Auto_Logo Left-most position in the Report header section Title Auto_Header Left-aligned in the Report header section Date and Time Auto_Date Auto_Time Right-aligned at the right side of the Report header section Page Numbers Text box At the left, center or right of the Page header section 41

Microsoft Office Access 2013 Adding Images to a Report • Options for adding images to a report include: – Inserting an image as a logo in the report header – Inserting an image in the Page Header, Page Footer, or Detail section – Inserting an image into the report background (background images display behind the report’s controls) • The appearance and behavior of the background image are controlled by the report’s Picture Type, Picture Tiling, Picture Alignment and Picture Size Mode properties. © CCI Learning Solutions Inc. 42

Microsoft Office Access 2013 Working with Colors • You can apply colors to report sections • You can also adjust the colors of individual controls • Select the control or section, then display either the Font Color or Background Color drop-down list and select the color you want to apply © CCI Learning Solutions Inc. 43

Microsoft Office Access 2013 Applying a Theme • Click the Themes button to display the Themes gallery, then click theme you want to apply • Click the Colors button in the Themes group to open the Colors gallery • Click the Font button in the Themes group to open the Font gallery • You can customize a theme by changing the color scheme and font • Only one theme can be applied to a database at any given time © CCI Learning Solutions Inc. 44

Microsoft Office Access 2013 Printing Reports • You can use Print Preview to preview exactly how the printed output will look • You can use Report view and Layout view to control which records are sent to the printer and control the order in which they print © CCI Learning Solutions Inc. 45

Microsoft Office Access 2013 Filtering Printed Output • Filtering enables you to isolate the specific records you want to print • Filters use criteria to test records and determine whether or not they should be displayed or included in report output • When a filter is applied, only a subset of the records display or are printed • You can filter by selection or use an advanced filter/sort © CCI Learning Solutions Inc. 46

Microsoft Office Access 2013 Sorting Printed Output • By default, records are displayed in a report by any grouping or sorting options you have specified • You can change the sort order on the fly using the Sort command in Layout view • To sort the records in a report: – Open the report in Layout view – Click the field by which you want to sort, then click a button in the Sort & Filter group on the Home tab – You can also right-click a field and select a sorting option from the shortcut menu. © CCI Learning Solutions Inc. 47

Microsoft Office Access 2013 Print Options • Aside from sending output to a printer, you can export report output to various types of files • You can also specify to export the report output to a file and attach the generated file to an email message © CCI Learning Solutions Inc. 48

Microsoft Office Access 2013 Lesson Summary • Modify page size and orientation • Work with report headers and footers • Add logos, titles, date and time, and page numbers • Add images to reports • Align, space, move and size controls • Filter and sort records on a report • Preview and print reports © CCI Learning Solutions Inc. • Use the Report Tool • Use the Report Wizard • Create a blank report in Layout view and Design view • Work with controls in Layout view and Design view • Add bound and unbound controls • Group and sorting report records • Add calculated controls to reports 49

Microsoft Office Access 2013 Review Questions 1. Which of the following is a limitation of the Report Tool? a. b. c. d. It generates reports that are connected to a single data source only. It is time-consuming to use. It adds only the first four fields of data to the report. It generates reports that cannot be modified. 2. Phil sees blank pages when he examines his report in Print Preview. Which of the following can cause this situation? a. b. c. d. The report’s margins are too narrow to force the data to display. The report does not have a valid data source. There are empty records in the database. The report is too wide for the paper. © CCI Learning Solutions Inc. 50

Microsoft Office Access 2013 Review Questions 3. Ken has added an unbound text box control to a report and he wants to use the text box to display the total price for each line item in an order. He should enter the expression =[Quantity]*[Unit. Price] in which property for the unbound text box control? a. b. c. d. The Record Source property. The Control Source property. The Row Source property. The Expression Source property. © CCI Learning Solutions Inc. 51

Microsoft Office Access 2013 Review Questions 4. Which of the following statements is true of date and time stamps in reports? a. b. c. d. There are five formats available for each. They must be inserted as a pair of controls. They cannot be automatically inserted into a report footer. You can insert only one or the other; not both. © CCI Learning Solutions Inc. 52

Microsoft Office Access 2013 Review Questions 5. Amanda opens a report on customers in Print Preview and sees that the report will be 135 pages long. Amanda needs information on customers from three states only. Which of the following is the best solution to allow Amanda to obtain her printed output without wasting so much paper on unneeded information? a. b. c. d. Amanda should apply a filter to the report before printing. Amanda should sort the report by state and print only the pages between her first state and her last state. Amanda should use the Find command to wade through the report in Report view, and print each page that contains information that she needs. Amanda should print the entire report and recycle the pages she does not need. © CCI Learning Solutions Inc. 53