Agenda: 03/06/13

1. Agenda: 03/06/13 • Review sample forms • Learn how to create reports in Access • Review for quiz 2

2. Reports (Introduction) • Used to create information from the data stored in a database • Can be based on tables and/or queries • Relies on tables and/or queries being related correctly • Similar to Access Forms • Reports gather and present information from one or more tables or queries • Different from Access Forms. • Not used to modify the data in the database. • Not used to add more data to a database. • Designed to be displayed on paper, rather than on a computer. • Assumption is that more information needs to be displayed together. • Can more easily create groupings of information. • Can more easily create sub-totals.

3. Methods to Create Reports in Access • Simple Report Tool • One table or query • Quick and easy, but relatively few choices • Report Wizard • Multiple related tables and/or queries • More choices; can create format and then modify easily in design view • Quick and easy, but many choices available for modifications • Design • Multiple related tables and/or queries • No predefined format to start from • Build report from scratch • Use to modify reports created using a different method • Predefined styles and themes • Complex well designed forms and reports can be more time consuming and difficult • Can use a wizard to develop an initial design and then modify the design via the use of the design view

4. reports in access • Tabular detailed report. • Grouped report. • Grouped report with summary totals.

5. Report Design – How to design a good report • Both form and report design are important • Change column headings to meaningful names • Print column headings on each page • Make sure columns are spaced evenly about the page • Use adequate margins • Test the report to be sure that column data are not truncated • Numeric data should be right-justified • Make sure columns are aligned • Textual data is typically left justified • Use care to select easy to read fonts • Don’t over clutter the report with unnecessary items

6. Report Views • There are four report views • The report and its elements are created in Design view • In Layout mode, the report layout appears with selected data on a single page • A good way to analyze the report’s design • Report view provides an interactive view of a report • In Print Preview mode, the report appears as the end user will see it and how it will print on a page • All data are reported

7. Use of Report Views • Review the report in report view and print preview, review it frequently • Understand the predefined areas of an Access report and design your report to fit within those areas • Adjust fields from the right

8. Report in Report View

9. Report in Layout View

10. Report in Design View

11. Controls • Controls work the same way for reports as forms • Some controls don’t make sense on a report • It’s possible to create a button on a report but why • Combo boxes don’t make sense either • Typically we use text boxes to display data • The Record Source and Control Source work the same way for both forms and reports • Use to bind the report to a table and a field to a control instance • Use labels for prompts and column headings • Controls may be bound, or unbound • Bound control: Connected or bound to a field in the database • Unbound control: Not connected to a field in the database. Includes labels, lines, pictures, etc. • Calculated controls are commonly used too

12. Report Sections • A report has sections just as a form has sections • Sections are more important related to reports though • A horizontal bar separates each section • The visual height of each section defines how tall the section will be on the printed page • Drag horizontal section bars to make them shorter or taller

13. Report Sections • The Report Header is printed once at the beginning of the report • It may appear at the top of the first page or on a separate title page • A Page Header is printed at the top of every page • Print column titles in page headers • A Page Footer is printed at the bottom of each page • Use to print page numbers, date printed, etc. • The contents of the Detail section print for each row of data in the underlying table or query • A new page is generated when necessary • Microsoft Access controls the logic • It’s possible to display a detail record on multiple lines

14. Report Sections Continued • A Group Header is printed at the beginning of a control group • Multiple Group Headers can appear • The Group Footer is printed at end of a control group • Use to print subtotals • There may be multiple Group Footers • The Report Footer is printed once at the end of the report • Use to print grand totals

15. Report Properties • Caption contains the title appearing in the title bar • Record Source gets the data for the report • It’s meaning is the same for both a report and a form • ForceNewPage causes the section to print on a new page • Typically used in a Header rather than Footer section • Commonly used for a Report Header • KeepTogether causes a section to print on one page where possible • RepeatSection causes a group header to be repeated when the detail data spans a page • SpecialEffect allows for custom formatting (raised, sunken, etc.)

16. Creating Calculated Fields • Create a Text Box in a Group Footer • Note that the calculated fields can appear in the query or in the report • Use a function to calculate a total or average • Example to total the field named fldAmount =Sum([fldAmount])

17. Creating Calculated Fields • Use queries to create calculated fields • Difficult to add calculated fields using pre-formatted reports • Must use text box to add calculated fields • Use queries to create summarized totals • When creating a completely summarized report, it is easier to use a query to create the aggregated totals and then base the report on the query

18. Calculated Fields (logic) • Totals are calculated based on the section where the calculated field appears • In a Report Footer section, the total is calculated across all records • In a Group Footer section, the total is calculated across the group • The total is reset after the group total is printed

19. What do you have to know before designing a report? • The purpose of the report. • Used for decision making? • Used for identifying exception conditions? • Used as a reference? • The reader of the report. • Upper management/owner? • Middle management/analyst? • Data entry person?

20. What else do you have to know to design a good report? • The content of the report. • Underlying data. • Calculations. • The time period of the report. • Every day? Every other day? Weekly? Monthly? Quarterly? Yearly? • The scope of the report. • Every single row in a table? • Detailed information? Grouped or summary totals only? • Will the report need grouping?