Access labs 2017

Lesson

6

Lesson 6 - Creating Reports

Lesson Topics:

6.1 Creating a Basic Report

6.2 Applying a Theme to a Report

6.3 Using the Report Wizard

6.4 Report View and Layout View

6.5 A Look at Design View

6.6 Modifying Report Setup

6.7 Printing Reports

6.8 Using the Label Wizard

6.1 Creating a Basic Report

In this lesson, you will create a Basic Report.

A

s with the other objects we have worked with so far, Access also has wizards that help you to create reports. If you remember, reports are Access objects that represent your data in printed form. Reports can be based upon either tables or queries, although basing your report upon a query allows you maximum flexibility. Reports, unlike Forms, Queries or Tables, do not allow you to make any changes to the underlying data. XE "Reports:creating" XE "Basic Reports:creating"

The fastest and easiest way to create a report is using a Basic Report. A Basic Report creates a “quick-and-dirty” columnar report, based on all fields in the selected table or query. You do not have the option of choosing which fields to include in your report. To only include specific fields in your Basic Report, you may wish to create a query with the fields you want included in your report, and then create a report based on that new query.

To Create a Basic Report

1. In the Navigation pane, select the query or table upon which to base your report.

2. Click the Create tab on the Ribbon.

3. Click the Report button on the Ribbon.

The Report Button

Let’s Try It!

What	Why
1. With Access open, click the File tab on the Ribbon.	Displays Backstage view.
2. Click Open.	Displays the Open dialog box.
3. Click Browse in the center pane.	Displays the Computer pane.
4. Click Desktop in the left pane and then double-click Lesson Files in the right pane.	Opens the Lesson Files folder and displays the files in that folder.
5. Select the Video Sales 6 file and click Open.	Opens the Video Sales 6 database.
6. Click the Title Bar on the Navigation Pane and select All Access Objects from the list.	Displays all Access objects in the database.
7. Click on qryProducts in the Navigation Pane under the Queries category.	Selects the query upon which to base our report.
8. Click the Create tab on the Ribbon.	Displays commands for creating Access objects.
9. Click the Report button on the Ribbon on the Reports command set.	Create a basic report based upon qryProducts and displays the Report in Layout view.
10. Click the View button arrow on the leftmost side of the Ribbon and choose Print Preview from the list.	Displays the report in Print Preview mode - how it will appear if it is printed.
11. Click the Close Print Preview button on the Ribbon.	Displays the report in Layout view.

6.2 Applying a Theme to a Report

In this lesson, you will learn how to apply a theme to a Report.

O

nce your report is created, the contextual tab displays four sets of report tools in Design view: Design, Arrange, Format and Page Setup. Under the Design tab is the Themes command button, which allows you to apply quick formatting by choosing from a gallery of styles. The gallery of styles contains preset formatting that you can quickly apply to your report. Hold your mouse pointer over a style to view the style name. XE "Styles:applying to a Report" XE "Themes:adding styles to Reports with" XE "Reports:Applying Themes to"

To Apply a Style to a Report

1. Display the Report in Design View or Layout View.

2. Click the Themes button under the Design tab under the Report Layout Tools to display the Styles Gallery.

3. Click the desired style from the Gallery.

Tip: You can also apply Themes to Forms.

Let’s Try It!

What	Why
1. Click the View button arrow on the Ribbon and choose Design View from the menu.	Switches to Design View.
2. Click the contextual Design tab on the Ribbon.	Displays Report design tools.
3. Click the Themes button under the Design tab as shown below.	Displays the Themes Gallery.
4. Click the Organic style in the second row, third column as shown below.	Applies the Organic style to the report.
5. Press the Ctrl + W keystroke combination. Click No when prompted to save your changes.	Closes the Report without saving it.

6.3 Using the Report Wizard

In this lesson, you will create a report using the Report Wizard.

T

he Report Wizard steps you through the process of creating an Access report. Unlike the Basic Report wizard, the Report Wizard allows much more control over the design of your reports. Like the Form Wizard, the Report Wizard allows you to select the fields to be included in your report as well as choose various style options. Although you can create a report manually, it can be quite time consuming as reports can be difficult to design. Most people prefer to use the Report Wizard when designing reports. XE "Wizards:report wizard" XE "Reports:creating with the report wizard"

To Create a Report using the Report Wizard

1. Click the Create tab on the Ribbon.

2. Click the Report Wizard button under the Reports command set on the Ribbon.

3. From the Tables/Queries drop-down list, choose the table or query upon which to base your new report.

4. Choose fields to add to the report by clicking the > button to add the selected field. Repeat for all desired fields.

5. To add all fields to the report, click the >> button.

6. Click Next.

7. To add grouping to your report, click the field by which you wish to group and click the > button. Repeat for any additional grouping levels.

8. Click Next.

9. To add sorting to your report, click the combo box and then choose the desired field by which to sort. Click the Ascending button to toggle the sort order to Descending (you can sort up to 4 levels).

10. Click Next.

11. Choose the desired layout and page orientation.

12. Click Next.

13. Enter a name for your report.

14. Choose whether to view the report’s design (Layout view) or whether to preview the report (Print Preview).

15. Click Finish.

Let’s Try It!

What	Why
1. Click the Create tab on the Ribbon.	Displays commands for creating Access objects.
2. Under the Reports command set, click the Report Wizard button as shown below.	Launches the Report Wizard.
3. Click the arrow on the right of the Tables/Queries combo box and then select qryCustomers from the drop-down list.	Selects the query upon which we want to base our new report.
4. Click the >> button.	All fields in the query will be added to our report.
5. Click Next.	Moves to the next step of the wizard.
6. Click Next.	As we do not wish to add any grouping, we will move to the next step of the wizard.
7. Click the first combo box and then choose LastName as shown below.	Sets our report to sort ascending by the LastName field.
8. Click Next.	Moves to the next step of the wizard.
9. Click the Tabular radio button in the Layout section and then choose Landscape in the Orientation section as shown below.	Chooses Tabular for the report layout and chooses Landscape for the page orientation.
10. Click Next.	Moves to the next step of the wizard.
11. Type: rptCustomerList in the report title box as shown below.	Enters the name for our report.
12. Ensure that Preview the Report is selected, and then click Finish.	Opens the report in Print Preview view.
13. Click anywhere in the report window.	Zooms out.
14. Click again in the report window.	Zooms in.
15. Click the Close Print Preview button on the Ribbon.	Displays the report in Design view.

6.4 Report View and Layout View

In this lesson, you will look at two new Access views: Report View and Layout View.

I

n this section, we will look at two different views from where you can observe your report data – Layout View and Report View. We have already worked a bit with Layout View when we looked at forms and it works the same way with reports. That is to say, Layout View allows you to change the design of the report while viewing your live data. Like forms, all of your report objects are selectable so you can easily rearrange and resize your fields, columns and rows all while displaying actual report data. XE "Report View: using" XE "Reports:report view"

Report View

Report View allows you to view an accurate rendering of your report and browse your data without the need to display the report in Print Preview view. What’s nice about Report View is that you can apply filters to your report data, displaying only specific records. Additionally, you can easily copy and paste fields of report data from one application to another. If you have added hyperlinks to your report, clicking on a hyperlink in Report View will open the link in your default Web browser. Double-clicking a report in the Navigation Pane will automatically display it in Report View.

To Display a Report in Report View

1. Under the Home tab on the Navigation Pane, select Reports from the drop-down list.

2. Double-click the report in the Navigation Pane

Or

Right-click the report you want to view and select Report View from the contextual menu

Or

Click the View button arrow on the Ribbon and choose Report View

Or

Click the Report View button on the lower-right corner of the Access window.

View buttons on lower-right corner of Access Window

To Display a Report in Layout View

1. Under the Home tab, select Reports from the Navigation Pane, drop-down list.

2. Right-click the report you want to view and select Layout View from the contextual menu

Or

Double-click the report to open it in Report view, click the View button arrow and choose Layout View

Or

Click the Layout View button on the lower-right corner of the Access window.

Let’s Try It!

What	Why
1. Click the View button arrow on the Ribbon under the Home tab and choose Layout View from the list.	Displays the report in Layout view.
2. Click in the report header to select the entire header as shown below.	Select the report object we wish to modify. In Layout view, report objects are selectable.
3. Click the contextual Format tab on the Ribbon.	Displays report formatting tools
4. Click the Background Color drop-down arrow and select the Dark Red color swatch from the palette as shown below.	Applies the Dark Red color to the report header.
5. Click directly on the words rptCustomerList, click the Font Color drop-down arrow and select the White color swatch from the palette as shown below.	Applies the White color to the text of the report header.
6. Click the Home tab on the Ribbon.	Displays the Home tab.
7. Click the View button arrow on the Ribbon and choose Report View from the list.	Displays the report in Report view.
8. Click the Save button on the Quick Access Toolbar.	Saves our design changes.

6.5 A Look at Design View

In this lesson, we will look at our report in Design view.

D

esign view is where you make changes to your report. For example, you can add additional descriptive text to your report, add or delete fields from your report, and move and/or resize report controls. XE "Reports:design view" XE "Reports:parts of"

In Design View, a report consists of the following sections:

· Page Header – information in this section appears on the top of each printed page of the report. Often, column headings are placed in this section.

· Page Footer – information in this section appears on the bottom of each printed page of the report. Often, page numbers are placed in this section.

· Detail – where most of the report’s field data appears.

· Report Header- information in this section appears on the first printed page of the report. Often, the title of the report or a company logo is placed in this section.

· Report Footer – information in this section appears on the last printed page of the report. Often, grand total fields are placed in this section.

Report Design View

Like forms, you can also add controls such as text boxes, labels, graphics, lines, or rectangles to reports. To add a control to a report, click the control you wish to use on the Controls command set under the Design tab and then drag it onto the report. Changing properties of report controls works the same way as for form controls – select the control, click the Property Sheet button and then make the desired changes.

To View a Report in Design View

1. Under the Home tab on the Navigation Pane, select Reports from the drop-down list.

2. Right-click the report you want to view and select Design View from the contextual menu

Or

Double-click the report to open it in Report view, click the View button arrow and choose Design View

Or

Click the Design View button on the lower-right corner of the Access window.

Let’s Try It!

What	Why
1. Right-click rptCustomerList and then select Design View from the contextual menu.	Switches to Design View.
2. Select the rptCustomerList label in the Report Header as shown below.	Selects the control whose properties we wish to change.
3. Click the contextual Design tab on the Ribbon.	Displays Report design tools.
4. Click the Property Sheet button on the Ribbon.	Displays properties for the selected label control.
5. Highlight rptCustomerList in the Caption Property and then type: Customer List as shown below.	Changes the caption for the selected label.
6. Click the Close button on the Property Sheet Box and observe the Report Header.	Closes the Property Sheet box. Notice that the report title now reads “Customer List”
7. Click the Save button.	Saves our design changes.

6.6 Modifying Report Setup

In this lesson, we will modify the page setup of our report.

B

efore printing your report, you may need to make some additional changes to the setup of your document, such as adjusting page margins, setting paper size, paper source, and page orientation, choosing a printer and printing your report in columns. Display your report in Print Preview mode and choose your desired page setup options from the Page Size and Page Layout command sets on the Ribbon.

SHAPE \* MERGEFORMAT

You can also enter custom settings in the Page Setup dialog box by clicking the Page Setup button on the Ribbon. From the Page Setup dialog box, click the Print Options tab, the Page tab or the Columns tab, and then enter the desired settings. XE "Page Setup:modifying" XE "Reports:page setup"

SHAPE \* MERGEFORMAT

Note: You must have a printer driver installed to modify the page setup of a report.

To Modify Page Setup

1. From the Home tab, click the View arrow and select Print Preview.

2. To change paper size settings, click the Size button on the Ribbon and choose the desired paper size from the list.

3. To change margin settings, click the Margins button and choose Normal, Wide or Narrow from the list.

4. To change page orientation, click the Portrait or Landscape button on the Ribbon.

To Modify Page Setup from the Page Setup Dialog Box

1. From the Home tab, click the View arrow and select Print Preview.

2. Click the Page Setup button on the Ribbon.

3. For margin options, click the Print Options tab and then enter the desired margin settings (in inches) for the top, bottom, left and right margins.

4. For page settings, click the Page tab.

a. To change page orientation, choose Portrait or Landscape in the Orientation section.

b. To change paper size, select the desired size from the Size combo box in the Paper section.

c. To change printer paper source, select the desired option from the Source combo box in the Paper section.

d. To choose a different printer, click Use Specific Printer, click the Printer button and then choose the desired printer from the printer combo box.

5. To print your report in columns, click the Columns tab and enter the desired column settings.

6. Click OK.

Tip: You can also access Report Setup options from the Page Setup tab on the Ribbon when in Layout View or Design View.

Let’s Try It!

What	Why
1. Click the View button arrow and select Print Preview from the list.	Displays the report in Print Preview view.
2. Click the Margins button on the Ribbon and choose Narrow from the list as shown below.	Choose Narrow as the margin type.
3. Click the Margins button again on the Ribbon and choose Normal from the list.	Choose Normal as the margin type.
4. Click the Page Setup button on the Ribbon.	Displays the Page Setup dialog box.
5. In the Left Margin Box, type: .75	Sets the left margin to .75 inches.
6. In the Right Margin Box, type: .75	Sets the right margin to .75 inches.
7. In the Top Margin Box, type: .50.	Sets the top margin to .50 inches.
8. Click the Page tab.	Switches to Page options. Observe the settings.
9. Click OK.	Applies the settings and closes the Page Setup dialog box.

6.7 Printing Reports

In this lesson, you will learn how to print reports.

N

ow that you’ve modifed the page setup, you are ready to generate a hard copy of your report. Click the Print button on the Ribbon when previewing a report to display the Print dialog box or press the Ctrl + P keyboard shortcut. The Print dialog box allows you to: XE "Reports:printing" XE "Print:reports" XE "Print:dialog box" XE "Print Button"

· Modify printer properties

· Choose which printer to use

· Set the page range

· Set the number of copies to print

The Print dialog box allows you to modify the final printer settings before generating a hard copy.

To send your document directly to the printer without displaying the Print dialog box, click the File tab on the Ribbon, click Print from the menu and then choose Quick Print from the second pane.

If you use the Quick Print feature often, you can add it to your Quick Access toolbar by clicking the arrow next to the Quick Access Toolbar and choosing Quick Print from the list. The Quick Print icon will now be displayed on the toolbar.

To Print a Report

1. Click the Print button on the Ribbon when previewing a report

Or

Press the Ctrl + P keyboard shortcut

Or

Click the File tab on the Ribbon, click Print from the menu and then click Print in the right pane to display the Print dialog box.

2. Enter any desired print options in the Print dialog box.

3. Click OK.

4. To send a report directly to the printer, click the File tab, click Print from the menu and then choose Quick Print from the second pane.

Let’s Try It!

What	Why
1. Click the Print button on the Ribbon.	Displays the Print dialog box.
2. In the Print Range section, enter 3 in the From box and 4 in the To box as shown.	Enters a specific range to print.
3. In the Copies section, type 3 as shown.	Enters the number of copies of the report to print.
4. Click Cancel.	Cancels the print job.
5. Click the Close Print Preview button on the Ribbon.	Returns to Report Design View.
6. Click the Close button on the Report window. If prompted, save your changes.	Closes the report.

6.8 Using the Label Wizard

In this lesson, you will learn how to create mailing labels using the Label Wizard.

T

he Label Wizard allows you to create mailing labels in standard and custom sizes from the data in your tables. Like the Report Wizard, the Label Wizard steps you through the process of creating labels, allowing you to select various options as you go. Access supports most standard label sizes and manufacturers. XE "Reports:label wizard" XE "Wizards:label wizard" XE "Mailing Labels:creating with the label wizard"

To Create Mailing Labels

1. In the Navigation pane, select the query or table that contains the data for your labels.

2. Click the Create tab.

3. Click the Labels button on the Ribbon.

4. Choose the label manufacturer from the Filter by Manufacturer combo box.

5. Select the desired product number in the window.

6. Click Next.

7. Select any desired font options.

8. Click Next.

9. Select fields from the Available fields window and then click the > button to add them to the prototype label. Add any desired punctuation, spaces, or additional text to the prototype. Press Enter to move to the next line.

10. Click Next.

11. Add the field(s) by which to sort in the Sort By window.

12. Click Next.

13. Type the name for your report.

14. Click Finish.

Let’s Try It!

What	Why
1. Click on qryCustomers in the Navigation Pane under the Queries category.	Selects the query upon which to base our label report.
2. Click the Create tab on the Ribbon.	Displays commands for creating Access objects.
3. Click the Labels button on the Reports command set of the Ribbon.	Launches the Label Wizard.
4. From the Filter by Manufacturer drop-down list, select Avery as shown below	Selects the label manufacturer.
5. Choose product number Avery UA 5160 from the label size window as shown below.	Selects Avery 5160 labels.
6. Click Next.	Moves to the next step of the wizard.
7. Click in the Font name drop-down list and type: Tim until Times New Roman appears as shown below.	Selects the font we wish to use for our labels.
8 Click Next.	Moves to the next step of the wizard.
9. Select FirstName from the Available fields list then click the > button as shown below.	Adds the FirstName field to our Prototype label and then moves to the next field in the Available Fields window.
10. Press the spacebar.	Adds a space after the First Name field.
11. Click the > button.	Adds the LastName field to the Prototype label and then moves to the next field in the Available Fields window.
12. Press Enter.	Starts a new line in the Prototype label.
13. Double-click the Address field in the Available fields window.	Adds the Address field to the Prototype label.
14. Press Enter.	Starts a new line in the Prototype label.
15. Click the > button.	Adds the City field to the Prototype label.
16. Press , and then press the spacebar.	Adds a comma and a space after the City field.
17. Click the > button.	Adds the State field to the Prototype label.
18. Press the spacebar twice.	Adds two spaces after the State field.
19. Double-click the Zip field. The prototype label should look like the example below.	Adds the Zip field to the Prototype label.
20. Click Next.	Moves to the next step of the Wizard.
21. Double-click the State field in the Available Fields Window.	Specifies that our labels will be sorted by the State field.
22. Click Next.	Moves to the next step of the Wizard.
23. In the name box, type: rptCustomerLabels as shown below.	Provides a name for our label report.
24. Click Finish.	Displays our labels in Print Preview view.
25. Click anywhere on the report area.	Zooms in.
26. Click the Close Print Preview button on the Ribbon.	Displays the Report in Design View.
27. Click the Close button on the Report window. Save any changes.	Closes the Report.
28. Click the File tab and select Close from the File Options menu.	Closes the Video Sales 6 database.

Lesson Summary – Creating Reports

· In this lesson, you learned how to create a basic report by choosing the query or table upon which to base your report, and then clicking the Report button under the Create tab.

· Next, you learned how to apply a preset style to a report by clicking the Themes button under the Design tab of the contextual Report Layout Tools tab to display the Styles Gallery and then clicking the desired theme style from the Gallery.

· Then, you learned how to create a report using the Report Wizard, by clicking the Report Wizard button under the Create tab and following the directions of the Wizard.

· Then, we looked at two report views - Report View which allows you to view an accurate rendering of your report and browse your data without the need to display the report in Print Preview view and Layout View, which allows you to change the design of the report while viewing your live data.

· Then, you worked with the Design view of a report. You learned that report Design view consists of a Page Header, Page Footer, Detail section, Report Header and Report Footer. You learned that you can switch to Design view by right-clicking the report you want to view and selecting Design View from the contextual menu, or by double-clicking the report to open it in Report view, clicking the View button arrow and choosing Design View or by clicking the Design View button on the lower-right corner of the Access window.

· Then, you learned how to modify report setup from the Page Layout command set on the Ribbon while previewing a report. We also worked with the Page Setup dialog box.

· Then, we learned how to set print options from the Print dialog box such as modifying printer properties, choosing which printer to use, setting the page range and setting the number of copies to print by clicking the Print button on the Ribbon when previewing a report or by pressing the Ctrl + P keyboard shortcut.

· Lastly, we learned how to create mailing labels using the Label Wizard by clicking the Labels button under the Create tab and following the directions of the Wizard.

Lesson 6 Quiz

1. To create a Basic Report:

A. Click the Report Wizard button under the Create tab.

B. Click the Basic Report Wizard under the Create tab.

C. Click the Report button under the Create tab.

D. Click the Blank Report button under the Create tab.

2. When creating a basic report, you can choose which fields to add to your report.

A. True

B. False

3. To apply a preset style to a report, you:

A. Click the Themes button and choose the desired style from the gallery.

B. Click the AutoStyle button and choose the desired style from the gallery.

C. Click the Navigation Pane button and choose the desired style from the list.

D. Click the Styles button and choose the desired style from the gallery.

4. In which of the report views can you modify the layout of a report (select all that apply)

A. Report View

B. Layout View

C. Print Preview

D. Design View

5. Which views allows you to view your actual data while being able to rearrange the fields on your report (select all that apply):

A. Report View

B. Layout View

C. Print Preview

D. Design View

6. You want to browse your live data while being able to apply filters to view only specific records. What report view would best accomplish this?

A. Layout View

B. Browse View

C. Design View

D. Report View

7. How can you send a report directly to the printer without displaying the Print Setup dialog box?

8. To specify custom margin settings for a report, you:

A. Click the Margins button on the Ribbon while previewing a report and then type in your desired margin settings.

B. Click the Page Setup button on the Ribbon while previewing a report and then type in your desired margin settings under the Margins tab.

C. Click the Page Setup button on the Ribbon while previewing a report and then type in your desired margin settings under the Print Options tab.

D. You cannot specify custom margins for an Access Report.

9. You can rearrange fields on your report when in Report view.

A. True

B. False

10. To launch the mailing label wizard:

A. Click the Report button under the Create tab.

B. Click the Avery button under the Create tab.

C. Click the Envelopes and Labels button under the Create tab.

D. Click the Labels button under the Create tab.

11. You can base a report off of either a query or a table.

A. True

B. False

12. What happens if you change the caption property of a label?

A. Text will be placed underneath the label.

B. The label’s background (fill) color will change.

C. You will change the label’s text.

D. You will change the text that appears when you hold your mouse pointer over a label.

LAB 6 – ON YOUR OWN

1. Open the Lab6 database in the Lesson Files folder.

2. From the Navigation Pane, display All Access Objects.

3. Create a new report based on the qryProducts query by using the Report Wizard. Add the following fields to the report:

Category

Title

Price

Rating

Do not add any grouping to the report. Sort the report by Category. Choose a tabular layout with a portrait orientation. Name your report rptProductListing and open the report in Print Preview view.

4. Switch to Design View (Hint: first click the Close Print Preview button). Select the rptProductListing label and then click the Property Sheet button under the Design tab. Change the Caption property of the label to: Product Listing. Close the Propery Sheet pane. Close the report and save any design changes.

5. Create a new report using the Label Wizard. Base the report on the qryCustomers query and use Avery 5160 standard labels. Add the fields so that the prototype label looks like the one below.

Sort by LastName. Name your label report rptCustomerLabels.

6. Close the report and exit the database

Congratulations!

You have completed Access 2016 – Level 1! You are now ready to use the skills that you have learned in this class to design your own Access databases. Don’t be afraid to experiment with all you have learned. If you would like to continue your Access training, we have an Access 2016 – Level 2 class in which you learn about database relationships and work with queries, tables, forms and reports in more detail.

Class Project – Employee Database

You are the owner of Rodney’s Video store. Create a new database called “RVS Employees”

Create a table with includes the following fields: Employee ID, First Name, Last Name, Address, Home Phone, Cell Phone Social Security Number, Date Hired, Title, Salary, email address and Department. Be sure to follow naming conventions when creating your database objects and fields.

Create a form named “frmEmployees” that you can use to enter in employee information. Enter at least 8 records using your form.

Create a query that only lists employees whose salary is greater than $25,000 (when entering in your data, make sure to include a couple of records for employees who fall under the $25,000 salary range). Save and name your query “qryMore_than_25”

Create another query that lists only employees from a particular state (you choose) and whose salary is greater than $30,000. Name the query something meaningful.

Create an employee report based upon your “qryMore_than_25”. Make any changes that you feel would improve the appearance of the report. Save the report as rptMore_than_25.

Create a mailing label report for your employees based upon your employees tables. Only include relevant fields in your report (i.e. name and address fields). Save your report as “rptLabels”