What is a report?
A report is an Access object. It is used to display your data in an organized manner so that users can print it.
Within your Access database, you can view all of your report objects in the Database Window. See below.
Creating a Report
Let's take a look at creating a new report "from scratch".
In the database window, select Reports along the left toolbar. Then click on the New button.
The New Report window should appear. For the purposes of this tutorial, highlight the option called Design View and click on the OK button.
We are now viewing our report in design view. You should see a blank report that looks like this:
Under the View menu, select Properties. You should now see the properties window for the Report object.
Let's take a few moments to explore some of the more commonly used properties for a Report object.
Record Source property
The Record Source property sets the source of the data for the report.
The Record Source property can be set to a table name, query name, or you can build your own SQL statement by invoking the Query builder (ie: clicking on the button with the three dots to the right of the Record Source property).
In this example, we are going to invoke the Query builder by clicking on the button with the three dots to the right of the Record Source property.
When the Show Table window appears, select the table(s) that you'd like to use to populate your report. In our example, we've selected the Suppliers table. Then click on the Add button.
Once you've added all of the tables that you need, click on the Close button.
When the Query Builder window appears, you can now build your SQL statement that will be used to populate your report. In this example, we've chosen to select all fields from the Suppliers table, but only those records where the SupplierID is less than 10.
You can now click on the X button on the top right of the window.
When prompted with the message box, click on the Yes button.
Now when you return back to the Properties window for the Report object, you should see your SQL in the Record Source property.
Caption Property
The Caption property is the value that displays in the title bar of your report.
We'll set the Caption property to display "Suppliers Report".
Now when we view the Form as an end-user would, it would look like this:
As you can see there is a title bar at the top of the report and it displays the value that we set in the Caption property.
That covers the more commonly used properties on a Report object.
For the purposes of this tutorial, we've set the Report properties as follows:
Record Source = SELECT Suppliers.*
FROM Suppliers
WHERE ((([Suppliers].[SupplierID])<10));Caption = Suppliers Report
Now, let's build our report.
Adding objects to our report
Our next step is to add objects from the Suppliers table to our report.
Since we've already specified our Record Source, Access gives us some nice features to quickly drop objects onto our report. In the toolbar, there is a button called Field List. It looks like a piece of paper with writing on it. Click on this button so that it is selected.
Once you do this, you should have a small window appear on your screen that looks like this.
This window lists all of the fields that are available for your report to use, based on the Record Source property.
To add one of these fields to your report, highlight the object in the window, and then drag it to the location in the report where you wish to add this object.
We've chosen to add the following four objects to our report. These have been added to the Detail section of the report.
The first thing that you'll probably want to do is resize your objects. To do this, click the object that you wish to resize.
You should notice small black boxes around the perimeter of the object. Move your mouse pointer over one of these "resizing boxes". When your mouse pointer displays a double-headed arrow, hold down the left mouse button and drag the object to the desired size.
If you wish to move the object, move your mouse pointer until it displays a hand icon. Then hold down the left mouse button and drag the object to the desired location.
You should also resize your Detail section of your report. To do this, move your mouse pointer over the bottom of the detail section until your pointer displays a double-headed arrow, then hold down the left mouse button and drag until the Detail section is the desired size.
Next, you probably want to set up a Page Header. This will be the text that displays at the top of each of your report pages.
To do this, select the Label icon from the Toolbox. This is the icon that has the capital and small letter A's.
Then left-click in the Page Header section of your report and drag to size the Label object.
Enter the text that you'd like to see displayed on each page. In our example, we've typed "Suppliers Report".
You can resize the text using the text font and size options in the Toolbar.
We've chosen to set the text to Arial 14.
Next, you might want to add page numbers to the bottom of your report.
To do this, under the Insert menu, select Page Numbers.
When the Page Numbers window appears, you can select how you'd like the page numbering to appear. In this example, we've chosen to see "Page N of M" as the format, the page numbers will appear at the bottom of the page (ie: Footer section), and the numbers with appear in the right-hand side on the footer.
Now, when you view your report, you can see that Access has inserted the Page Numbers in the Page Footer section.
Viewing your report as a user would
Now that you have finished placing your objects on the report, you will want to view the report as a user would. This whole time you've been editing your report in Design view.
To view your report, select Print Preview from the drop-down list on the toolbar. See picture below.
Our report looks like this:
Saving report
One final step is to save our Report object. You can do this at any time during creation of the report, but for the purposes of this tutorial, we are saving this as our last step.
Click on the Save button in the toolbar. (disk icon)
A "Save As" window should appear as follows:
Select a name for the report and click on the OK button.
Now when you return to the Database Window, you will see your report in the list.
Congratulations! You've now created your first report.
