Database Reports in Attractive Format

1. Reports

2. Reports display information retrieved from a database in an attractive printed format. • Reports can be created directly from tables, but • More sophisticated ones require the power of select queries, which we look at later.

3. Reports are a lot like forms in that they consist of bound controls, unbound controls, and calculated controls.

4. Reports can be displayed in Design View, which is very similar to the Design View for forms, allowing rearrangement of the layout. • Reports are designed ultimately to be printed, so the other main view of a report is Print Preview.

5. The simplest way to create a report is to use the Report Wizard, which allows you to specify the fields to display from which tables (or which queries) and how the display should be grouped.

6. As an example, consider a database used to keep track of customer orders for a computer retail store. • There are 4 tables in this database. • The fields in the 4 tables and the relationships between the tables are as follows:

8. If we tell the Report Wizard we want a report containing only the first and last name fields from the Customers table and the product name fields from the Products table, and we want them grouped by the customer first and last names, we get a report like the following:

10. Note that to generate this report it automatically took into account the relationships, using data from the Orders and Order Details tables to determine which products were ordered by each customer.

11. We can also use the Report Wizard with just selected fields from the Customers and Products tables to generate the following more detailed report:

13. To get the Report Wizard to generate this report, we first included the unit price for each item purchased among the fields to be displayed. • Then we also clicked the Summary Options… button on the appropriate dialog box:

15. Finally, we checked Sum on this resulting dialog box to tell the Report Wizard we want the unit price values summed within each group of records (i.e., for each customer):

17. But to get reports displaying more sophisticated summaries of the data or based on only those records satisfying specified criteria, we should base our report on queries that we’ve created, not just directly on the tables.

18. For example, we might want to generate a report displaying this information only for those customers whose total purchases are at least $3,000. • To do this, we must use a query as the basis for our report.

19. Charts • To put a chart on your report, go into report Design View and do Insert => Chart… to get the Chart Wizard. • The first thing you must do is to click and drag to outline where on your report the chart is to go. • Then answer all the questions posed by the Chart Wizard.