Creating a report using the wizard

Reporting is one of the most powerful functions of any database. Even more powerful is the ability to call up specific types of information, such as all new leads that live in Arizona. In this lesson, you'll create a call list (for follow-up by salespeople) report, and then modify it.

You've already explored the AutoReport (a dropdown of the New Object button, just like AutoForm), but there's a much more useful tool called Report Wizard.

Open your database and click the Reports tab on the left of the database window. Then double-click Create report by using wizard, and it will bring up the first step of the wizard.

In Figure 5-1, the dropdown under the Tables/Queries box shows a list of all tables and queries. In your current database, you have two tables and no queries as of yet. Choose tblLeads and the fields are listed under the available fields window.

Figure 5-1: Report Wizard Step 1.

Double-click the fields you want included in this report. If you accidentally click one, just double click it on the right to remove it from the Selected Fields list.

Choose CompanyName, ContactFirstName, ContactLastName, Title, City, StateID, and Phone, as shown in Figure 5-2. Click Next.

Figure 5-2: Report Wizard Step 2.

On the next screen of the Report Wizard, you're asked about grouping fields. Double-click StateID to group contacts under state. It shows up on the right as a blue field in its own box at the top. The report lists the state once, and then lists all contacts in that state underneath before listing the next state. If you were using this to make a phone list, you could choose ContactLastName, and then click the Grouping Options button at lower left in Figure 5-3 to choose change the grouping option from Normal to 1st Letter to group contacts by the letter their name starts with. In this case, you want to group normally, so just select StateID, as shown in Figure 5-3, and then click Next.

Figure 5-3: Report Wizard Step 3.

If you make a mistake, just create a new report by going through the wizard again.

On the next window, you have the option of sorting by certain fields within the grouped fields. Because you've already grouped by state, sort by City, LastName, and FirstName, as shown in Figure 5-4. Click Next.

TIP
At this point, you might consider grouping by City rather than sorting by city. It might be confusing or unclear how you have grouped items, but you can fix that later.

Figure 5-4: Report Wizard Step 4.

The next window concerns your layout. In most cases, you'll choose enough columns of data to justify using the Align Left (1 or 2). It gives you the most room to fit data on the page and in some cases you need landscape, which just means when the page is held horizontal (longer left to right) but in this case, portrait is fine. This is another area where you can come back through the wizard and experiment to see the different layouts and which you prefer. It's usually preferable to check the Align the field width so all fields fit on a page but you can experiment with this later. Either way, fields may need to be customized. Choose Align Left 1, as shown in Figure 5-5, and then click Next.

Figure 5-5: Report Wizard Step 5.

The next window is personal preference and you can click the different styles to choose one. For this example, choose Corporate. Click Next.

The final window asks for a name for your report that will appear at the top of the report and will also be the name of the report. Type Lead Call Sheet and confirm that Preview the report is selected. Click Finish.

Microsoft Access creates your report and shows you the results in the print preview window. It's not perfect, but it's a good start.

Print preview view

When you've finished with the Report Wizard, Access shows the report in the Print Preview view. This allows you to see what it looks like before you print the report. In Access, this is highly recommended because some reports can be hundreds of pages long; therefore, it's a good idea to make sure it's correct before you click the Print button.

Click anywhere on the report to zoom in and out. In Print Preview, shown in Figure 5-6, at the bottom of the report, you'll see the current page number and arrows to move through the report. Your report may only have one page at this point, but as you add more leads to your table, your report will grow each time you run it.

Figure 5-6: Report Print Preview.

Reports are only a set of instructions. Each time you double-click the report, it applies the set of instructions to the table or query you have associated with the report and includes all existing information that matches the report specifications.

You don't have to click Save at this point because the Wizard automatically saved your report. Unfortunately, it saved it with the name Lead Call Sheet because it used that name you typed into the wizard for both the title and the report name. You'll change that later.

Just above the form are standard buttons you're familiar with, including the now familiar Design View button (which will be used in just a moment) and the Print button, which prints the document.

WARNING
Be careful with the Print button on this page. It doesn't confirm that you want to print. It just sends the whole report to the printer, regardless of its size.

The next three buttons allow you to view a single page (the default), two pages, or multiple pages up to six.

Next to the Multiple Page button is a Zoom dropdown that allows you to zoom in or out to a specific size, as shown in Figure 5-7. If you view the report at 100 percent, it's easily readable, but does not fit on the page. Clicking the page typically zooms in and out between 100% and Fit to page, but will change to zoom between whatever you set in the Zoom dropdown box and Fit to page.

Figure 5-7: Multipage Report Print Preview.

The Close button is self explanatory, but the next button (the one with the W on it) is a dropdown that allows you to output your report to Microsoft Word or Microsoft Excel. Try it. You'll find that it isn't exactly perfect, but it does give you a great deal of flexibility to be able to send reports out to other formats where you can then better modify the results.

TIP
If you're trying to send reports to other people who don't have Access, consider purchasing Adobe Acrobat full version to send them a well-formatted report without their having the ability to change it.

The last three buttons are Database Window, New Object, and Help, were all covered in earlier lessons.

Click Close to return to the database window and right click the report name. Click Rename, and then change the name from Lead Call Sheet to rptLeadCallSheet. This satisfies earlier recommendations of starting each Access Object name with a three-letter code to identify it, such as rpt for Report, and not having spaces so you don't have to use brackets when you create a button for this report later. Access is not case sensitive, so the uppercase and lowercase letters just create a more recognizable name for humans to interact with, but ideally, all this will be behind the scenes and an easy to use menu will provide the interface for most users.

After you've renamed the report, double-click it to reopen it.

In Figure 5-8, notice that the words are cut off and not quite as you might like them. Click the Design button in the upper-left corner to edit.

Figure 5-8: 100% Report Print Preview.

Report in Design View

In Design view, reports look similar to forms and many items are the same.

In Figure 5-9, as you saw in the Form Design view, the field list is on the right, below it is the Properties box and to the left of that, you'll see the Toolbox. A new one is the Sorting and Grouping Toolbar, to the left of the toolbox. This is very important in Report Design and is covered in this lesson.

Figure 5-9: Report Design View.

TIP
Remember to save as you go along, and then if you make a mistake, close the report without saving.

Toolbar

In the Report Design view, you have the exact same Formatting toolbar as you did in the Form Design view. The other toolbar is also the same as the Form Design view with the exception of a few buttons.

The first button has three choices as the Form Design View button did, but the three here are Design View, Print Preview, and Layout Preview. In the previous section, you saw Print Preview. Figure 5-9 shows Design View. Layout Preview is similar to Print Preview, but it does not show you all the data, just a sampling so that you can see the layout.

Except for this button and one other, the toolbar is identical to the Form Design View toolbar. The one new button on this toolbar is near the middle of the bar just to the right of the toolbar toggle button. It's the toggle to the Sorting and Grouping dialog box.

Sorting and groupings

The Design view in Figure 5-9 shows your groupings on the report. The StateID Header is a Header for a custom grouping in the Sorting and Grouping Window at the bottom, while the others are all standard groupings.

Each section of the reports has unique properties.

A Text box (field) will only show the first item that appears. All fields should be in the detail section unless in a custom grouping or a calculation.

TIP
Any or all of these sections can be turned on or off in its properties window. If only summary information is desired, the detail section may be hidden when printed.

In Figure 5-9, in the Sorting and Grouping List, Leads are Sorted by StateID, and then City, ContactLastName, and ContactFirstName. At the left of StateID, a small grouping symbol indicates that this Field groups the data.

Below the list of Fields that are grouped and sorted is the Group Property list. In this example, the StateID field only has a Header, not a Footer. If the Group Footer was turned on, it might be used to add space or an expression to calculate how many Leads were in each state.

Group On has a dropdown box with a choice of Each Value or Prefix Characters. If, for example, you wanted to create a phone list, you could use the ContactLastName field and group on Prefix Characters, and then use the field below it, Group Interval, set to 1 to group all the people whose last names start with A, B, and so on.

As in all situations, check your data to make sure your report makes sense. For example, if you were to set the StateID to Group on Prefix Characters, and then set the group interval to 2, it would not make any difference in this report. However, if you set the group interval to 1, as in the phone list example, you would see a list that grouped the states as follows: AZ, BC, CA, DE, and so on. The report would put all States together that started with A, then B, C, D, and so on, but in the StateID Header, it would just list the first state. This is very important. If you place a field in any header, it will only show the first value in that field. In the event that you're grouping by Each Value in that header, this makes perfect sense. In any other case, be sure the data you're getting out is what you want.

Most headers and footers use only labels and expressions, such as sums, averages, counting, dates, times, and so on. Text Boxes that only contain a single field name should only be used in the Detail section of the report, unless you're sure you're getting the correct information out.

Formatting

Formatting works exactly the same as in Form Design. Click any field on the form to select it, and then resize it to make the words fit within the label or textbox and choose any formatting you like, such as Bold, Italic, Font, Color, and so on.

Try selecting and moving objects around on the form. You can use undo to step back, or close the form without saving if you just want to start over. To enlarge or shrink a section, just move your cursor to the top edge of any section and when you get a double-headed arrow, click and drag up or down.

Notice that the labels are always the same as the Field Names. You can edit this so they fit on your form. For example, ContactFirstName and ContactLastName are too big for their label boxes, so just click the label, and then click again to edit them to First Name and Last Name.

Use your select tool to move the fields around to match Figure 5-10. Remember that you can select multiple fields by holding your Shift key. Also, right-click to use the Align and Size choices if you're having trouble lining up the text boxes and labels. Be careful of the lines that are purely decorative, but can easily end up getting selected when you click and drag. In this example, add a line (found on the toolbox) under your detail text boxes, and then delete the City Label and the City Text Box. Just click and press the Delete key on your keyboard.

Figure 5-10: Edited Report Design View.

Although you can directly edit in text boxes, for example ContactFirstName, it's key that you do not unless you specifically know the name of a field to be used instead. When you select an object on the report, you can see on the top of the properties box whether it's a Label or a Text Box. Labels are simply that. They can say whatever you like and that is what will show on the Report. Text Boxes are linked to Data in the table or query that the report is pulling from and must reflect the actual field names.

TIP
If you'd like to add a field, just drag it from the field list, just as you did in the forms.

Properties

Don't forget that as you click each property, you'll see a description in the lower-left corner of the screen. Don't be afraid to experiment. You can't break Access and if you make a mistake, undo. If you make too many mistakes, just close without saving.

TIP
Each object on the report has a unique set of properties.

Moving on

In this lesson, you created a new report and then modified it to your needs and preferences. During this process, you looked at reports in preview and Design view. Continue to build reports as needed to add functionality to your database.

In Lesson 6, you'll learn about queries and review what you've learned in this course.

Assignment: Create a report

Create a new report using Report Wizard using tblLeads, and then go to design mode and modify the report so it looks cleaner.

Practice modifying your existing reports.

Quiz

Question 1
True or False: Reports are required in Microsoft Access.

A) True
B) False

Question 2
True or False: When you create a report using the Report Wizard, it automatically creates a query from the table.

A) True
B) False

Question 3
Where can sorting and grouping be done? (Check all that apply)

A) Within the report
B) Within the query
C) Within forms
D) None of the above

Question 4
True or False: Every time you run a report, it pulls the most current data from the related table.

A) True
B) False