The procedure to develop a report consists of two logically related steps. First, the RQBE capability of Access is used to specify and relate the various data files together to provide all information. Second, the report generator is used to format the report. The following steps detail the procedures. We use the following four data files that are within the Sales Example Database. The fields for each file are listed below and are more specifically defined within the database.
You can copy the Sales Example Database from here (file name: SALES.MDB) (note: file is in Access 2000 format)
You can copy the Sales Example Database in Office 97 format from here (file name: SALES97.MDB) (note: file is in Access 97 format for students who don't have Office 2000)
| Specific Sales File | Customer Information File | Inventory Information File | Salesperson Information File |
|---|---|---|---|
| Customer Number | Customer Number | Part Number | Salesperson Number |
| Part Number | Customer Name | Part Description | Salesperson Name |
| Sales Quantity | Current Balance Owed | Quantity on Hand | Commission Rate |
| Salesperson Number | Price | ||
| Invoice Number | |||
| Date of Sale |
The Specific Sales Records file contains a record of all transactions, including the Customer Number, the Part Number, the Quantity of the sale, the Salesperson Number, the Invoice Number, and the Date of the sale. The Customer Information File contains the Customer Number, the Customer Name, and the Customer's Current Balance. The Inventory Information File contains the Part Number, the Part Description, the Quantity On Hand, and the Price. The Salesperson Information File contains the Salesperson's Number, the Salesperson' s Name, and the Commission Rate.
First, we use RQBE to "tie" the data files together to provide all information for our report. We want to develop a "Sales" report so we use the Specific Sales Records file as our primary file. The other files provide additional information as necessary. How we order and group the data in the file determines the report format. Let's create a report of Sales by Customer first. The procedure is detailed as follows.
First, start "ACCESS" by clicking on the "Access" icon on the desktop. The initial "Access" window appears. Click on "Cancel" to proceed.
1. Open the Sales Example Database
Click on "File," then on "Open Database." The "Open Database" window opens. Point to the "A" drive or the "C" drive and appropriate directory to locate YOUR COPY of the Database. Click on the
"Sales Example Database" to highlight it, then click on "Open" to open it. The "Sales Example Database" window will open with four data files, as described above, in the
database. You can click on a data file to highlight it, then click on "Open" to display the data
within the file. Click on "File," then on "Close" to close the data file. Alternately, you can click on the "X" in the upper right corner of the Database window to close the display.
2. Initial RQBE Development
Start the RQBE Query Development from the "Sales Example Database" window. Click on the
"Queries" option, then on "New." The "New Query" window opens with "Design View" option highlighted. Click on "OK." The "Show Table" window opens. Click on the "Specific Sales Records" file, then "Add" to add it to the query, then on "Close" to close the "Show Table" window. The Specific Sales Records" file window appears in the "Query1" window.
3. Specify fields to include, printing, and ordering of data
Double click on the "Customer Number" field in the "Specific Sales Records" file window to specify the field as one we want to use. The field is copied into the query area in the lower part of the window. Next, click anywhere in the box to the right of "Sort:", then on the down arrow button that appears to the right, then on the "Ascending" choice. This specifies the data from the file to be in "Customer Number" order, sorted ascending. The box to the right of "Show" should have a check indicating that the "Customer Number" field is to be included in the query results.
Repeat the above process for all fields in the "Specific Sales Records" file that are to be included in our query. You can omit the "Sort" specification since we want to order the data only on the "Customer Number" field.
4. Saving your work to date
It is always a good idea to save your work as you go along. This ensures that you will have a
working copy of your work in case something disastrous happens. To save your work do the
following: Click on "File," then on "Save As." You can specify any name that you desire. For example, you may wish to name the query "First Sales Query". Then click on "OK" to save it. Click on the "X" in the upper left corner of the window to close the "First Sales Query" window. The "Sales Example Database" window reappears.
To see the results of your work so far, click on "Open." The query will execute and the results will display on the screen. Click on "X" in the upper right corner to close the query.
5. Creating a "Report"
From the "Sales Example Database" window select the "Reports" option, then the "New" option. The "New Report" window opens. First, click on the "Design View" option in the top of the window. Next, in the lower half of the window there is a bar to specify the query (or file) to use when building the report. Click on the down arrow button to open a list of available queries (and files), then click on the desired query (the one we created in step 3 above - you specified a name for it). Finally, click on "OK" to proceed.
The "Report1:Report" window will appear. This is the "generic" report design window. We "design" our report here by placing data fields, column headings, and report headings. The order in which the report elements are placed is not important at this time as they can easily be moved..
To place specific fields into the report, first click on the "Field List" icon (thirteenth from left, just to left of hammer and wrench icon). The list of the fields from the query we developed in step 3 above will appear. Click and drag the fields desired into the "Detail" section of the report design screen. When you drag the field it appears that the field is placed twice. In fact the right field is actually the data field, while the left "field" is a default field description heading text that can be deleted. Click away from the field just placed, then click on the left "field" to select it. Boxes appear around the "field." Press the "Del" key to delete it. Repeat this click and drag process for all fields desired in the report.
To save the report click on "File," then on "Save As." Enter a name for the report - for example, "Report for First Query" - then click on "OK." Click on "X" to end the report to return to the "Sales Example Database" window.
6. Adding more information from other files to our query
We turn our attention back to the query that we created in steps 2 and 3 above. We can add fields from the other three files to our query by developing relationships between the "Specific Sales Records" file and the other files. Remember, relationships are developed through a common field between the "Specific Sales Records" file (our primary file) and the other files. The common field servers as a "pointer" to the other files and tells Access which record to select in order to provide the specific information.
We begin by selecting our query with which we have been working. From the "Sales Example Database" window, click on the "Query" option. Highlight "First Sales Query" (or whatever you named your query), then click on "Design." The "First Sales Query:Select Query" (or whatever you named it) window opens. Click on the "Show Table" icon (thirteenth from left on toolbar, just to left of exclamation point icon). The "Show Table" window opens. Select the "Inventory Information File" by clicking on it to highlight it, then on "Add" to add it to the query. The "Inventory Information File" appears within the "First Sales Query:Select Query" window. Click on "Close" to close the "Show Table" window.
The "Inventory Information File" must now be related to the "Specific Sales Records" file. To specify which fields are to be used to define a relationship, point to the field to be used in one of the file windows, then "click and drag" to the desired field in the other file window. A line will appear between the two selected fields in the file windows. You can change this as required.
Assuming the relationship illustrated between "Specific Sales Records" file and the "Inventory Information File" is correct, we proceed with our query development. Next, add new fields from the "Inventory Information File" to the query. The procedure is similar to the placement of fields in step 3 above. Add the "Price" field from the "Inventory Information File." You will have to "scroll" to the right in the fields section of the query to see the new field that you just added. Next, click on the next blank field name box to the right of the "Description" and "Price" fields. Enter [Sales Quantity]*[Price] as the next field. Note that the field names are enclosed within braces [ and ]. This is our calculation of the extension of the price of the item sold.
Click on "File," then on "Save" to save the updated query. Click on the "X" to close the query. "Open" the query to see the results. Click on "X" to close the view of the query.
Repeat step 6 for the "Customer Information File" and the "Salesperson Information File." Add the Customer Name and Salesperson Name fields to the query, as well as any other desired fields.
7. Incorporate the new fields into our report
We can now include the newly added fields from our query into our report, making our report more
complete and informative. To do this, select the "Reports" option, select the "Report for First Query" (or
whatever you called it) report, then click on "Design" to open the "Report for First Query:Report" window.
Click on the "Field List" icon (thirteenth from left on toolbar) to open the window of fields from
the "First Sales Query." We will be working with the "Detail" section of the report for now.
Note: you may have to have more space in the report for field placement. To get more space make the report wider. Point to the "edge" of the white area, then click and drag to the right to widen the report. You can make the report as wide as necessary and can always narrow it later after all fields have been placed.
"Click and drag" the "Price" field into the right side of the report just to the left of the "Date" field. Be sure you drag the field into the DETAIL section of the report - just below the "Detail" line. When you click and drag the field into the report, the field name is in black text, and inside a black box. The default column header name is blue (or some other color depending on how Access is configured) and comes along with the field (no, I don't know how to stop it). What you must do is click away from the field you just placed (anywhere in the report screen), then click on the blue column header, then press the "Delete" key. This procedure will remove the column header (which we will replace later).
You can always delete a field by selecting it, then pressing the "Del" key.
"Click and drag" the "Expr1" field (which is our Sales Quantity*Price calculation) into the Detail section, as well as the Customer Name, Salesperson Name, and any other desired fields.
Click on the "Print Preview" icon (first icon on left on toolbar) to preview the report on the screen. Click on "Close" to return to the report window. Click on "File," then "Save" to save your report. Click on the "X" to close the report window.
Note: the report may be wider than the page - that's OK for now.
8. Dressing up fields and figures
All fields can be altered to meet our specific requirements. For example, the size of fields may
be lengthened or shortened. Dollar amount fields may be specified as displayed as currency - "$"
sign and decimal inserted. To alter field characteristics do the following:
First, open the "Report for First Query" (or whatever you called it) report, then click on "Design." The "Report for First Query:Report" window opens and permits access to all fields in the report.
Next, you must select the field to be altered. Click on a field to select it. Black box "handles" appear around the field. You must always select a field before doing anything to it. Point to the upper left handle box. The "hand" icon will appear. You can click and drag the field anywhere you want it to appear.
Point to the rightmost center handle box. The double arrow icon appears. You can click and drag the field width to be anything you wish. This is how you size fields.
With the desired field selected, click on the "Properties" icon (eighteenth from left on toolbar) to open the "Text Box" window. In the box to the right of "Format" click anywhere, then on the down arrow that appears to select the desired format. For example, select the "Currency" option for money fields. Click on the box to the right of "Decimal Places" then on the down arrow button. Select the desired number of decimal places (usually 2 for money). Click on "X" to complete the definition.
Click on the "Print Preview" icon to display your progress. Click on "Close" to return.
Click on "File," then on "Save" to save your report so far. Click on "X" to close the report window.
9. Grouping data
Wouldn't it be nice if the customer's name and number appeared only once at the top of his/her
data. Then all of the items the customers purchased could be listed below their names. The
"Grouping" facility allows us to do this.
Grouping data involves the definition of a "header" and a "footer" for each customer. The customer's name and number will appear in the "header" that prints just before all of the detail items purchased. The total purchase amount will appear in the "footer" which prints after the last detail item purchased is printed. This is really quite logical if you think about it for a minute. It is a very good report format and is quite easy to do.
From the "Sales Example Database:Database" window, select the "Report" option, then highlight "Report for First Query" (or whatever you named it), then click on "Design." This opens the "Report for First Query:Report" window and gives us access to the report format and fields.
Next, click on the "grouping" icon (fifteenth from left on toolbar) to open the "Sorting & Grouping" window. You can see that we have already specified that the report be sorted by Customer Number. We want to add a "Group" header. In the lower part of the window is the "Group Properties" section. To the right of "Group Header" click on the word "No," then on the down arrow button that appears. Click on "Yes" to indicate that we want a group header. Since the Customer Number field is the only field in the "Sorting & Grouping" window, we get a header section for the Customer Number. Notice that a gray bar with "Customer Number Header" appears in the report design screen. Click on "X" to close.
Now you want to click and drag all fields that you want to print only once when the customer number changes into the header section. For example, the customer number and name should only appear once at the beginning of the customer's purchases, so click and drag these fields into the header section.
You may now want to rearrange the fields that are in the detail section - drag them to the left, for example, to narrow the entire report.
Preview the report. Notice that the customer's name and number appear only once at the heading of the list of items the customer bought. Click on "Close" to return to the report design screen. Move the detail section fields around as you desire.
Click on "File," then "Save" to save your work. Be sure to do this often.
10. Total sales by customer
Now let's develop the "Footer" section of the report - the information that prints just after the last
detail item the customer purchased. For example, the total amount of each customer's purchases
would print in the footer.
Click on the "Sorting & Grouping" icon, then, in the "Group Properties" section, click in the bar to the right of "Group Footer." Next, click on the down arrow button, then on "Yes" to select. Finally, click on "X" to close the definition. The "Customer Number Footer" bar will appear on the report format screen.
Now we are ready to define the total purchases field in the footer section of the report. First, click on the "Toolbox" icon (fourteenth from left on toolbar, crossed hammer and wrench) to open the "Toolbox." Next, select the "ab|" button, then move the mouse arrow down to the footer section. We want to specify the placement of the total field, so it would logically be placed below the "Expr1" field (the Saleqty*Price calculation) in the detail section. Click and drag a box in the footer section below the "Expr1" field that is in the detail section. An "Unbound" field appears. (we will worry about the blue column header later) While this "unbound" field is selected, click on the "Properties" icon, then set the format to "Currency" and the decimal places to 2.
Next, click on the bar for "Control Source." A button with three dots appears to the right of the down arrow. Click on this button to open the "Expression Builder" window. In the lower left of the window double click on the word "Functions," then on "Built-In Functions." The center and right windows have stuff appear in them. In the rightmost window scroll down until the word "Sum" appears. Double click on the word "Sum." In the upper left corner of the window the expression "Sum(<<expr>>)" appears. We want to change "<<expr>>" to the name of the field in the detail section that we want to total: Expr1. So use the mouse to highlight "<<expr>>", then type "Expr1" in its place. It should look like this: SUM(Expr1) Click on "OK" to save. Click on "X" to end the process. Print preview as desired.
Note: when using field names from the various data files in the SUM function, you will have to surround the field name with the square braces. For example, if you wish to sum the "Course Fee" field, your function will look like this: SUM([Course Fee]) The surrounding of a field name with the square braces applies only if the field name is comprised of two or more words. If, for example, the field name is "CourseFee", the square braces are unnecessary. They won't hurt, but then aren't necessary.
As with other fields that we added to the report, we need to get rid of the default blue header that was placed along with the field. Click away from the new field, then click on the blue column header to select it. Press the "Del" key to delete it.
Click on "File," then "Save" to save the report.
11. Report Headings
Now that we have the basic format of the report pretty well defined, we are ready to turn our
attention to the report headings. There are two sections: Report Header and Page Header. The
contents of the Report Header section appear only once at the top of the entire report. The Page
Header contents appear at the top of every page in the report. Both sections can contain
anything. They usually contain the text information necessary to identify the report and its contents.
There is one basic operation that define working with headers: working with text. To delete existing text, click on the text to select it (black boxes surround the text), then press the "Del" key to delete it. To enter text, from the toolbar select the "toolbox" icon to open the "toolbox." From the toolbox, click on the "Aa" option, then move the mouse to where you wish the text to appear. Click and drag a box for the text, then enter the text. You can then move it around as desired. Adjust the field length as before.
Let's work with the Report Header first. Say we want "ABC Widget Company" to appear at the top of the report. First, select and delete the existing report header. Next, simply click and drag a box for the text, then enter it. Move and size it as desired. The next line may be "Sales Report by Customer." The Report Header can have ANYTHING in it that you desire. Make the header look like you want the report to appear. (Note: we don't normally put fields in the header sections.)
Notice that there is a field "now()" in the Report header. This is the automatic date field. Place it anywhere you wish. There is a "=Page" field at the very bottom of the report. This is the "Page number" field. Place it in the header as you wish.
Now let's work with the Page Header. The current contents are default column headings that were developed when the report was first created. We will want to delete them all, then replace them with more descriptive column headings. As before, select and delete the existing default column headers. Next, from the Toolbox, select the "Aa" option, then click and drag text boxes as desired, then enter the text. You will want to enter each word separately to facilitate moving headings around. Work with the column headings until you are satisfied.
Click on "File," then on "Save" to save the report. Click on "Print Preview" icon to view your work. Click on "X" to end the report session.
12. Printing the report
We are now ready to print the report. Click on "File," then on "Print" to open the
"Print"window. Select the "Setup" option, then select the "Portrait" or "Landscape" option in the "Orientation" section. Click on "OK" to save the setting, then on "OK" to start the printing.