Creating Reports from Scratch

Access 2003's Report Wizard can create reports that you can use "as is" or modify to suit most of your database reporting requirements. In many cases, however, you must create reports that are more complex than or different from those offered by the Report Wizard. For example, you might have to apply special grouping and sorting methods to your reports. Including subreports within your reports requires that you start from a blank report form instead of using the Report Wizard. Like subforms, subreports use master-child relationships to provide detail information, such as the orders placed by each customer by year, quarter, or month.

Reports make extensive use of unbound fields having calculated values. To understand fully the process of designing advanced Access reports independently of the Report Wizard, you must be familiar with VBA and Jet functions, which are two of the subjects of Chapter 10, "Understanding Jet Operators and Expressions." You also must understand the methods that you use to create and design forms, which are covered in Chapters 14, "Creating and Using Access Forms," and 15, "Designing Custom Multitable Forms." Reports make extensive use of Jet functions such as Sum() and VBA expressions such as ="Subtotal of" & [FieldName] & ":". If you skipped Chapters 10, 14, or 15, you might want to refer to the appropriate sections of those chapters whenever you encounter unfamiliar subjects or terminology in this chapter.

Note

The report design techniques you learn in this chapter apply, for the most part, to Access data projects (ADP). The queries on which you base reports in ADP must conform to Transact-SQL syntax. For example, you can't use Jet SQL's TRANSFORM...PIVOT statements to create ADP reports based on crosstab queries. You also can't include Jet-specific functions, such as DLookup() that are provided by the Jet expression service. Chapter 22, "Upsizing Jet Applications to Access Data Projects," describes the workarounds you need to adapt Jet-based reports to ADP.