MS Access

Review: Creating a Report with AutoReport

To Create a Report with AutoReport: In the Database window, click the Reports icon in the Objects bar, click the New button, and select either AutoReport: Columnar or AutoReport: Tabular. Click the table or query you want to use for the report from the drop-down list and Click OK.

Modifying a Report

To Modify a Report: In the Database window, click the Reports icon in the Objects bar, click the report you want to modify and click the Design button, or open the form and click the View button on the toolbar.

Adding and Deleting Fields

To Add a Field to a Report: Display the report in Design view and click the Field List button on the toolbar, if necessary. Find the field you want to add to the report in the Field List, then click and drag the field to the desired location on the report.

To Delete a Field or Control: Click the field or control to select it and press Delete.

Moving and Sizing Controls

To Resize a Control: Click the control to select it, click and drag one of its sizing handles, and release the mouse button when the control reaches the desired size. Hold down the Shift key while dragging to maintain the control's proportions while resizing it.

To Move a Control: Click the control and hold down the mouse button, drag the control to a new location, then release the mouse button to drop the control.

To Move a Text Box or Text Label Independently of Each Other: Position the pointer over the upper left sizing handle of the control until it changes to a hand, then click and drag the control.

To Copy a Control Using Drag and Drop: Follow the same procedures as moving a control, only hold down the Ctrl key while you drag the control.

Adjusting Page Margins and Orientation

To Adjust Margins: Select File » Page Setup from the menu and click the Margins tab, adjust the appropriate margins.

To Change a Page's Orientation: Select File » Page Setup from the menu, and click the Page tab. In the Orientation section, select either the Portrait or Landscape option.

Adding Page Numbers and Dates

To Add Page Numbers: Display the report in Design view, select Insert » Page Numbers from the menu, select the page-number format, position, and alignment options, and click OK.

To Insert the Date and/or Time: Display the report in Design view, select Insert » Date and Time from the menu, check or uncheck theInclude Date box and select a formatting option. Check or uncheck the Include Time box, select a formatting option, and click OK.

Understanding Report Sections

To Resize a Report Section: Display the report in Design view, then click and drag the section line up or down.

Grouping and Sorting Records

To Group Records: Display the report in Design view and click the Sorting and Grouping button on the toolbar. Click the Field/Expression cell, click the list arrow, and select a field for grouping records. Click the corresponding Sort Order cell, click the list arrow, select the desired sort order, and select any Group Properties you want to use in the Group Properties area. Repeat these steps for each Field/Expression you want to use to group and sort your data. Close the Sorting and Grouping dialog box when you're finished.

Creating Calculated Controls

To Create a Calculated Control: Display the form in Design view, select the control, and click the Properties button on the toolbar. Click the Data tab, click in the Control Source box, type the expression, and then close the Properties dialog box.

Working with Section Properties

To Modify a Report's Section and Grouping Options: Display the report in Design view, and then click the Sorting and Grouping button on the toolbar.

To Modify a Section's Properties: Display the report in Design view, then double-click the section line for the section whose properties you want to view/modify.

Creating Charts with the Chart Wizard

To Insert a Chart into a Report: Display the report in Design view, select Insert » Chart from the menu, and then drag and drop a chart onto the report, which opens the Chart Wizard. Select the table or query you want to chart from the Table or Query list, and click Next to continue. Double-click each field you want to add to the chart and click Next to continue. Click the chart type you want and click Next to continue. Make any layout modifications to the chart and click Next to continue. Enter a chart name and then click Finish.

Quiz

  1. Which of the following statements about the AutoReport Wizard is NOT true?

    1. The AutoReport Wizard is the fastest and easiest way to create a report in Microsoft Access.

    2. The AutoReport Wizard can only create two types of reports: Columnar and Tabular.

    3. Reports created with the AutoReport Wizard usually come out looking sharp and professional and don't require further clean-up work.

    4. The AutoReport Wizard can only create reports based on a single table or query .

  2. Which of the following statements is NOT true?

    1. The Field List displays all the fields from a report's underlying table or query.

    2. Click the Field List button on the Toolbar to display the Field List.

    3. You can add fields to a report by dragging them from the Field List onto the report.

    4. The Field List displays all the fields from every table in a database.

  3. Controls and their corresponding text labels cannot be moved independently of one another. (True or False?)

  4. Which of the following statements is NOT true?

    1. You can move a control to a different location on a report by clicking, dragging, and dropping the control.

    2. To add a page number to a report, select View » Header/Footer from the menu and click the Page Number button on the Header/Footer toolbar.

    3. You can resize a report by clicking and dragging the right edge of the report.

    4. You can resize a control by clicking the control to select it, grabbing one of its sizing handles, and dragging and releasing the mouse button when the control reaches the desired size.

  5. You want a report to group and total sales by month. Where would you place a calculated control containing the following expression =SUM([Sales]) to calculate the totals for each month?

    1. In the Month Group Footer section.

    2. In the Page Footer section.

    3. In the Report Footer section.

    4. In the Summary section.

  6. Which of the following is NOT a report section?

    1. Report Header section.

    2. Page Header section.

    3. Summary section.

    4. Detail section.

  7. The only way to sort a report's records is to base the report on a query, which actually does the work of sorting the records. (True or False?)

  8. Which of the following expressions is incorrect?

    1. =Total for: [Employee].

    2. =[InvoiceDate]+30.

    3. =[LastName]&" "&[FirstName].

    4. =[Units]*[UnitPrice].

  9. You want to track the progress of the stock market on a daily basis. Which type of chart should you use?

    1. Line chart.

    2. Column chart.

    3. Row chart.

    4. Pie chart.

  10. How do you adjust a page's margins?

    1. Click and drag the edge of the page to where you want the margin set.

    2. Select Format » Page Setup from the menu, click the Margins tab, and adjust the margins.

    3. Select File » Page Setup from the menu, click the Margins tab, and adjust the margins.

    4. Click the Margins button on the Formatting toolbar.

  11. How can you view a report's sorting and grouping options?

    1. Select Format » Sorting and Grouping from the menu.

    2. By double-clicking the Report Selector box in the upper left corner of the report.

    3. Select File » Page Setup from the menu and click the Sorting and Grouping tab.

    4. Click the Sorting and Grouping button on the toolbar.

  12. What is the procedure for selecting multiple controls on a report?

    1. Press and hold down the Shift key as you click each object that you want to select.

    2. Use the arrow pointer to draw a box around the object that you want to select.

    3. If the controls are aligned along a horizontal or vertical line, click the horizontal or vertical ruler above or to the left of the controls.

    4. All of these.

Homework

  1. Start Microsoft Access, if necessary, and then open the Homework database.

  2. Use AutoReport to create and save a tabular report named "Customers," using the Customers table as the underlying data source.

  3. Delete the DOB field from the report.

  4. Change all of the report's margins to a half-inch.

  5. Sort the information on the report by DOB.

  6. Save your changes and close the Homework database.

Quiz Answers

  1. C. The AutoReport Wizard can create in record time, but they aren't usually well-organized or professional looking.

  2. D. The Field List only displays fields from a report's underlying table or query.

  3. False. You can click and drag the upper left sizing handle to move a label or control independently of one another.

  4. B. This procedure will let you add page numbers in Microsoft Word, but not in Microsoft Access.

  5. A. You would want the calculated control in the Month Group Footer section to total monthly sales.

  6. C. Although you can summarize information in a report section, there isn't actually a section called a "Summary section."

  7. False. Although you can sort a report using a query, you can also simply click the Sorting and Grouping button on the toolbar and specify the field you want to use to sort the report.

  8. A. This expression is missing the quotation marks (") and the ampersand (&) symbol. The corrected expression would be ="Total for: "&[Employee].

  9. A. Line charts are used to illustrate trends. If you used the other three chart types to track the stock market, there would be too many data points.

  10. C. You adjust a page's margins by selecting File » Page Setup from the menu, clicking the Margins tab, and adjusting the margins.

  11. D. Click the Sorting and Grouping button on the toolbar to view a report's sorting and grouping options.

  12. D. All of these are procedures for selecting multiple controls on a report.