Creating a static report
We will create a report on vacation expenses. Certain questions need to be asked before creating the report.
- What information do we want in the report?
- How do we want the information arraigned?
- What fields are required to provide this information?
- Will a query have to be created because these fields are in different tables?
- Are there any calculations required in the data before being added to the report?
The expenses for our vacation are motel, tolls, miscellaneous, breakfast, lunch, supper, snacks, and fuel. One possible report would simply list the totals of each of these expense groups. Another possible report would list the expense totals for each day of the vacation. A third possible report would list the totals for each expense group for each type of payment. (This would let us know where the money came from to pay the expenses.) At the present time, using the data from the queries in a spreadsheet is the best way to handle reports like this. In the near future, the report feature will include these abilities.
For our purposes, we will create two reports. The first one will list the expenses each day other than fuel. The second report will list the fuel costs each day.
The fields we will need for the first report from the Vacations table are: Date, Motel, Toll, Breakfast, Lunch, Supper, SnackCost, and Miscellaneous. This report will not require an additional query.
The second report involves the Fuel table. Since fuel was purchased and entered into this table at times other than during the vacation, a query needs to be created that contains only the fuel purchased during the vacation.
Top of page
Vacations table report
- Create a new report.
- Click the Reports icon in the Database list in the Automobile - OpenOffice.org window.
- In the Tasks list, click Use Wizard to Create Report. The Report Wizard window opens.
- Select the fields.
- Select Table: Vacations in the Tables or Queries dropdown list.
- Use the > to move these fields from the Available fields list to the Fields in report list: Date, Motel, Tolls, Miscellaneous, Breakfast, Lunch, Supper, and SnackCost. Click Next.
Adding fields to a report.
- Label the fields: answering the question How do you want to label the fields.
Shorten Miscellaneous to Misc. Click Next.
- Since we are grouping by the date, use the > button to move the Date field to the Grouping list. Click Next.
- Sort options.
We do not want to do any additional sorting. Click Next.
- Choose layout.
We will be using the default settings for the layout. Click Next.
||If you feel adventurous, try selecting some of the other layout choices. After selecting a choice, drag and drop the Report Wizard window so that you can see what you have selected. (Move the cursor over the Heading of this window, and then drag and drop.)
- Create report.
- Label the report: Vacation Expenses.
- Select Static report.
Top of page
Vacation fuel report
- Create a query containing only fuel bought on the days of the vacation.
- Open a query in Design View.
- Follow the steps for adding tables in
Step 2. Add tables of
Using the Design View to create a query. Add the Fuel table.
- Double-click these fields in the Fuel table listing: Date and FuelCost to enter them in the table at the bottom of the query.
- In the Criterion cell of the Date field, type the following:
- BETWEEN #5/25/2007# AND #5/26/2007#
Setting the criterion for a query.
- Save, name, and close the query. (Suggestion: Vacation Fuel Purchases.)
||When using dates in a query, enter them in numerical form MM/DDYYYY or DD/MM/YYYY depending upon your language's default setting for dates (my default setting is MM/DD/YYYY).|
All dates must have a # before and after it. Hence, May 25, 2007 is written #05/25/2007# or #25/5/2007depending upon your language's default setting.
- Open a new report.
- Right-click the Vacation Fuel Purchases query.
- Select Report Wizard from the context menu.
||When a new report is opened in this way, the query used to open it is automatically selected in the Tables or Queries dropdown list.
- Create the report.
Use >> to move both fields from the Available Fields to the Fields in Report list. Click Next.
- Label fields.
Add a space to FuelCost to make it Fuel Cost (two words). Click Next.
- Group fields.
- Click Date to highlight it.
- Use > to move the Date field to the Groupings list. Click Next.
- Choose layout.
We will be making no changes in the layout. Click Next.
- Create report (final settings).
- Use the suggested name, which is the same as the query.
- Select Static report. Click Finish.
Top of page