You can create a variety of different reports in Access, ranging from the simple to the complex. Begin by thinking about your report's record source. Whether your report is a simple listing of records or a grouped summary of sales by region, you must first determine which fields contain the data you want to see in your report, and in which tables or queries they reside.
After you choose your record source, you will usually find it is easiest to create your report by using the Report Wizard. The Report Wizard is a feature in Access that guides you through a series of questions and then generates a report based on your answers.
What do you want to do?
Choose a record source
A report consists of information that is pulled from tables or queries, as well as information that is stored with the report design, such as labels, headings, and graphics. The tables or queries that provide the underlying data are also known as the report's record source. If the fields that you want to include all exist in a single table, use that table as the record source. If the fields are contained in more than one table, you need to use one or more queries as the record source. Those queries may already exist in your database, or you may need to create new queries specifically to fit the needs of your report.
Create a report by using the Report tool
The Report tool provides the fastest way for you to create a report, because it generates a report immediately without prompting you for information. The report displays all the fields from the underlying table or query. The Report tool may not create the final, polished product that you ultimately want, but it is quite useful as a means to quickly look at the underlying data. You can then save the report and modify it in Layout view or Design view so that it better serves your purposes.
-
In the Navigation Pane, click the table or query on which you want to base the report.
-
On the Create tab, in the Reports group, click Report.
Access builds the report and displays it in Layout view.
-
For more about viewing and printing your report, see the section View, print, or send your report as an e-mail message.
After viewing the report, you can save it and then close both the report and the underlying table or query that you used as a record source. The next time that you open the report, Access will display the most recent data from your record source.
Create a report by using the Report Wizard
You can use the Report Wizard to be more selective about what fields appear on your report. You can also specify how the data is grouped and sorted, and you can use fields from more than one table or query, provided you have specified the relationships between the tables and queries beforehand.
-
On the Create tab, in the Reports group, click Report Wizard.
-
Follow the directions on the Report Wizard pages. On the last page, click Finish.
When you preview the report, you see the report as it will appear in print. You can also increase the magnification to zoom in on details. For more about viewing and printing your report, see the section View, print, or send your report as an e-mail message.
Note: If you want to include fields from multiple tables and queries in your report, do not click Next or Finish after you select the fields from the first table or query on the first page of the Report Wizard. Instead, repeat the steps to select a table or query, and click any additional fields that you want to include in the report. Then, click Next or Finish to continue.
Create labels by using the Label Wizard
Use the Label Wizard to easily create labels for a wide variety of standard label sizes.
-
In the Navigation Pane, open the table or query that will be the record source for your labels by double-clicking it.
-
On the Create tab, in the Reports group, click Labels.
-
Follow the directions on the pages of the Label Wizard. On the last page, click Finish.
Access displays your labels in Print Preview so that you can see them as they will appear when they are printed. You can use the slider control on the Access status bar to zoom in on details. For more information about viewing and printing your report, see the section View, print, or send your report as an e-mail message.
Note: Print Preview is the only view you can use to see multiple columns — the other views show the data in a single column.
Create a report by using the Blank Report tool
If you aren't interested in using the Report tool or the Report Wizard, you can use the Blank Report tool to build a report from scratch. This can be a very quick way to build a report, especially if you plan to put only a few fields on your report. The following procedure explains how to use the Blank Report tool:
-
On the Create tab, in the Reports group, click Blank Report.
A blank report is displayed in Layout view, and the Field List pane is displayed on the right side of the Access window.
-
In the Field List pane, click the plus sign next to the table or tables containing the fields that you want to see on the report.
-
Drag each field onto the report one at a time, or hold down CTRL and select several fields, and then drag them onto the report at the same time.
-
Use the tools in the Header/Footer group on the Design tab to add a logo, title, page numbers, or the date and time to the report.
Understand the report sections
In Access, the design of a report is divided into sections. You can view your report in Design view to see its sections. To create useful reports, you need to understand how each section works. For example, the section in which you choose to place a calculated control determines how Access calculates the results. The following list is a summary of the section types and their uses:
-
Report Header This section is printed just once, at the beginning of the report. Use the report header for information that might normally appear on a cover page, such as a logo, a title, or a date. When you place a calculated control that uses the Sum aggregate function in the report header, the sum calculated is for the entire report. The report header is printed before the page header.
-
Page Header This section is printed at the top of every page. For example, use a page header to repeat the report title on every page.
-
Group Header This section is printed at the beginning of each new group of records. Use the group header to print the group name. For example, in a report that is grouped by product, use the group header to print the product name. When you place a calculated control that uses the Sum aggregate function in the group header, the sum is for the current group.
-
Detail This section is printed once for every row in the record source. This is where you place the controls that make up the main body of the report.
-
Group Footer This section is printed at the end of each group of records. Use a group footer to print summary information for a group.
-
Page Footer This section is printed at the end of every page. Use a page footer to print page numbers or per-page information.
-
Report Footer This section is printed just once, at the end of the report. Use the report footer to print report totals or other summary information for the entire report.
Note: In Design view, the report footer appears below the page footer. However, when the report is printed or previewed, the report footer appears above the page footer, just after the last group footer or detail line on the final page.
Understand controls
Controls are objects that display data, perform actions, and let you view and work with information that enhances the user interface, such as labels and images. Access supports three types of controls: bound, unbound, and calculated:
-
Bound control A control whose source of data is a field in a table or query is a bound control. You use bound controls to display values from fields in your database. The values can be text, dates, numbers, Yes/No values, pictures, or graphs. A text box is the most common type of bound control. For example, a text box on a form that displays an employee's last name might get this information from the Last Name field in the Employees table.
-
Unbound control A control that doesn't have a source of data (a field or expression) is an unbound control. You use unbound controls to display information, lines, rectangles, and pictures. For example, a label that displays the title of a report is an unbound control.
-
Calculated control A control whose source of data is an expression rather than a field is a calculated control. You specify the value that you want in the control by defining an expression as the source of data for the control. An expression is a combination of operators (such as = and + ), control names, field names, functions that return a single value, and constant values. For example, the following expression calculates the price of an item with a 25 percent discount by multiplying the value in the Unit Price field by a constant value (0.75).
= [Unit Price] * 0.75
An expression can use data from a field in the report's underlying table or query, or from a control in the report.
When you create a report, it is probably most efficient to add and arrange all the bound controls first, especially if they make up the majority of the controls on the report. You can then add the unbound and calculated controls that complete the design by using the tools in the Controls group on the Design tab.
You bind a control to a field by identifying the field from which the control gets its data. You can create a control that is bound to the selected field by dragging the field from the Field List pane to the report. The Field List pane displays the fields of the report's underlying table or query. To display the Field List pane, on the Design tab, in the Controls group, click Add Existing Field.
Alternatively, you can bind a field to a control by typing the field name in the control itself or in the box for the ControlSource value in the control's property sheet. The property sheet defines the characteristics of the control, such as its name, the source of its data, and its format.
Using the Field List pane is the best way to create a control for two reasons:
-
A bound control has an attached label, and the label takes the name of the field (or the caption defined for that field in the underlying table or query) as its caption by default, so you don't have to type the caption yourself.
-
A bound control inherits many of the same settings as the field in the underlying table or query (such as for the Format, DecimalPlaces, and InputMask properties). Therefore, you can be sure that these properties for the field remain the same whenever you create a control that is bound to that field.
If you already created an unbound control and want to bind it to a field, set the control's ControlSource property to the name of the field. For details about the ControlSource property, search Help for "ControlSource."
Fine-tune your report in Layout view
After you create a report, you can easily fine-tune its design by working in Layout view. Using the actual report data as your guide, you can adjust the column widths, rearrange the columns, and add grouping levels and totals. You can place new fields on the report design and set the properties for the report and its controls.
To switch to Layout view, right-click the report name in the Navigation Pane and then click Layout View.
Access shows the report in Layout view.
You can use the property sheet to modify the properties for the report and its controls and sections. To display the property sheet, press F4.
You can use the Field List pane to add fields from the underlying table or query to your report design. To display the Field List pane, do one of the following:
-
On the Design tab, in the Tools group, click Add Existing Fields.
-
Press ALT+F8.
You can then add fields by dragging them from the Field List pane to the report.
Fine-tune your report in Design view
You can also fine-tune your report's design by working in Design view. You can add new controls and fields to the report by adding them to the design grid. The property sheet gives you access to a large number of properties that you can set to customize your report.
To switch to Design view, right-click the report name in the Navigation Pane and then click Design View.
Access shows the report in Design view.
You can use the property sheet to modify the properties for the report itself and the controls and sections it contains. To display the property sheet, press F4.
You can use the Field List pane to add fields from the underlying table or query to your report design. To display the Field List pane, do one of the following:
-
On the Format tab, in the Controls group, click Add Existing Fields.
-
Press ALT+F8.
You can then add fields by dragging them from the Field List pane to the report.
Add fields from the Field List pane
-
To add a single field, drag the field from the Field List pane to the section where you want it displayed on the report.
-
To add several fields at once, hold down CTRL and click the fields that you want. Then, drag the selected fields onto the report.
When you drop the fields onto a report section, Access creates a bound text box control for each field and automatically places a label control beside each field.
Add controls to the report
Some controls are created automatically, such as the bound text box control that is created when you add a field from the Field List pane to your report. Many other controls can be created in Design view by using the tools in the Controls group on the Design tab.
Determine the name of a tool
-
Place the mouse pointer over the tool.
Access displays the name of the tool.
Create a control by using the tools in the Controls group
-
Click the tool for the type of control that you want to add. For example, to create a check box, click the Check Box
tool. -
Click in the report design grid where you want to position the upper-left corner of the control. Click once to create a default-sized control, or click the tool and then drag in the report design grid to create a control of the size that you want.
-
If you don't position the control perfectly on the first try, you can move it by using the following procedure:
-
Click the control to select it.
-
Position the mouse pointer over the edge of the control until the pointer turns into a four-headed arrow
. -
Drag the control to the location that you want.
-
This procedure creates an "unbound" control. If the control is the type that can display data (a text box or check box, for example), you need to enter a field name or expression in the ControlSource property for the control before it will display any data. See the Understand controls section in this topic for more information.
Display the property sheet
To display the property sheet in Design view, do one of the following:
-
On the Design tab, in the Tools group, click Property Sheet.
-
Press F4.
Save your work
After you save your report design, you can run the report as often as you need to. The report's design stays the same, but you get current data every time you print or view the report. If your reporting needs change, you can modify the report design or create a new, similar report based on the original.
Save your report design
-
Click File > Save or press CTRL + S.
Alternatively, click Save on the Quick Access Toolbar.
-
If the report is untitled, type a name in the Report Name box, and then click OK.
Save your report design under a new name
-
If you're using Access 2013 or higher, click File > Save As > Save Object As.
If you're using Access 2010, click File > Save Object As.
-
In the Save As dialog box, type a name in the Save Report to box for the new report and then click OK.
View, print, or send your report as an e-mail message
After you save your report design, you can use it over and over again. The report's design stays the same, but you get current data every time you view or print the report. If your reporting needs change, you can modify the report design or create a new, similar report based on the original.
View your report
There are several ways to view your report. Which method you choose depends on what you want to do with the report and its data:
-
If you want to make temporary changes to which data appears on the report before you print it, or if you want to copy data from the report to the clipboard, use Report view.
-
If you want to be able to change the design of the report while looking at the data, use Layout view.
-
If you simply want to see what the report will look like when it is printed, use Print Preview.
Note: If your report is formatted with multiple columns, you can only see the column layout in Print Preview. Layout view and Report view display the report as a single column.
View your report in Report view
Report view is the default view that is used when you double-click a report in the Navigation Pane. If the report is not open, double-click the report in the Navigation Pane to see it in Report view.
If the report is already open, right-click the report name in the Navigation Pane and then click Report View.
Work with your data in Report view
In Report view, you can select text and copy it to the clipboard. To select entire rows, click and drag in the margin next to the rows that you want to select. You can then copy these rows to the clipboard by doing one of the following:
-
On the Home tab, in the Clipboard group, click Copy.
-
Right-click the selected rows and then click Copy.
-
Use the keyboard shortcut - Press CTRL+C.
Show only the rows you want by using filters
You can apply filters directly to your report without leaving Report view. For example, if you have a "Country/region" column and you want to see only those rows where the country/region is "Canada", do the following:
-
Find the word "Canada" in the report and right-click it.
-
Click Equals "Canada".
Access creates and applies the filter.
Toggle a filter on and off
You can switch between a filtered and non-filtered display by clicking Toggle Filter in the Sort & Filter group of the Home tab. This does not remove the filter — it just turns it on and off.
Remove a filter
-
Right-click the field from which you want to remove the filter.
-
Click Clear Filter from fieldname. (Your actual field is listed for fieldname.)
Once a filter has been removed, you cannot switch it back on by using the Toggle Filter command. You must first re-create the filter.
Note: If you apply a filter to a report and then save and close the report, the filter will be saved. However, the next time you open the report, Access will not apply the filter. To reapply the filter, on the Home tab, in the Sort & Filter group, click Toggle Filter.
Preview your report by using Print Preview
Right-click the report in the Navigation Pane, and then click Print Preview on the shortcut menu.
You can use the navigation buttons to view the pages of a report sequentially or to jump to any page in the report.
1. Click to display the first page.
2. Click to display the previous page.
3. Click to display the next page.
4. Click to display the last page.
5. Type a page number in this box and then press ENTER to jump to a specific page.
In Print Preview, you can zoom in to see details or zoom out to see how well the data is positioned on the page. With the cursor positioned over the report, click once. To reverse the effect of the zoom, click again. You can also use the zoom control on the Access status bar to zoom further in or out.
To close Print Preview, do one of the following:
-
On the Print Preview tab, click Close Print Preview.
-
Right-click the report in the Navigation Pane and then click Layout View or Design View on the shortcut menu.
Tip: After previewing your report, you can export the results to Microsoft Word, Microsoft Excel, or several other Office programs. On the External Data tab, in the Export group, click the button for the format that you want and follow the instructions.
Print your report
You can print a report while it is open in any view, or even while it is closed. Before you print, be sure to double-check the page settings, such as the margins or page orientation. Access saves the page settings with the report, so you need to set them only once. You can set them again later, if your printing needs change.
Change the page settings
-
Open the report in Print Preview. You can change page settings in any view, but Print Preview is best because you can see the effects of any changes immediately.
-
On the Print Preview tab, in the Page Layout and Page Size groups, click Portrait
or Landscape 
to set the page orientation, Size 
to set the paper size, Margins 
to adjust the margins, and so on. -
After you make a change, use the navigation buttons to view several pages to ensure that you haven't created any formatting problems on later pages.
Send your report to a printer
-
Open the report in any view, or select the report in the Navigation Pane.
-
Click File > Print > Print.
Access displays the Print dialog box.
-
Enter your choices for options such as printer, print range, and number of copies.
-
Click OK.
Send your report as an e-mail message
You can send your report to recipients as an e-mail message instead of printing a paper copy:
-
In the Navigation Pane, click the report to select it. On the External Data tab, in the Export group, click Email.
-
In the Send Object As dialog box, in the Select Output Format list, click the file format that you want to use.
-
Complete any remaining dialog boxes.
-
In your e-mail application, type the message details and send the message.
No comments:
Post a Comment