The sample application requires several reports. A couple of the simpler ones are built here.
The rptClientListing report lists all the clients in the tblClients table. The report includes the company name, contact name, intro date, default rate, and term type of each customer. The report is grouped by contact type and sorted by company name. It provides the average default rate by contact type and overall.
The rptClientListing report is based on a query called qryClientListing, which is shown in Figure 6.27. The query includes the CompanyName, IntroDate, and DefaultRate fields from the tblClients table. It joins the tblClients table to the tblContactType table to obtain the ContactType field from tblContactType, and joins the tblClients table to the tblTerms table to obtain the TermType field from the tblTerms table. It also includes an expression called ContactName that concatenates the ContactFirstName and ContactLastName fields. The expression looks like this:
ContactName: [ContactFirstName] & " " & [ContactLastName]
To build the report, follow these steps:
Select Reports from the Objects list and double-click Create Report by Using Wizard.
Use the drop-down list to select the qryClientListing query (see Figure 6.28).
Click the >> button to designate that you want to include all the fields in the query within the report. Click Next.
Indicate that you want to view your data by tblContactType. Click Next.
Do not add any grouping to the report. Click Next.
Use the drop-down list to select CompanyName as the sort field (see Figure 6.29).
Click Summary Options and click the Avg check box to add the average default rate to the report. Click OK to close the Summary Options dialog and Next to proceed to the next step of the wizard.
Select Landscape for the Orientation and click Next.
Select a style for the report and click Next.
Give the report the title rptClientListing; then click Finish.
The completed report should look like Figure 6.30. Click Design to open the report in Design view. Notice that both the name and title of the report are rptClientListing (see Figure 6.31). Modify the title of the report so that it reads Client Listing by Contact Type and Company Name.
The rptTimeSheet report is much more complex than the rptClientListing report. It includes two subreports: rsubTimeSheet and rsubTimeSheetExpenses.
The rptTimeSheet report is shown in Figure 6.32. It's based on a query called qryTimeSheet (see Figure 6.33). It contains fields from both tblTimeCards and tblEmployees.
The rptTimeSheet report has a Page Header that includes the title of the report, but nothing else is found within the Page Header. The TimeCardID header contains the EmployeeName and DateEntered from the qryTimeSheet query. The report's Detail section contains the two subreports rsubTimeSheet and rsubTimeSheetExpenses. The TimeCardID footer has a text box that contains the grand total of hours and expenses. The expression within the text box is
=[rsubTimeSheet].[Report]![txtTotalHourlyBillings]+[rsubTimeSheetExpenses].
It is easiest to build the expression using the Expression Builder.
The Page Footer holds two expressions, one for the date and another for the page number. They look like this:
=Now() ="Page " & [Page] & " of " & [Pages]
The rsubTimeSheet report is based on qrySubTimeSheet; this query contains the following fields from the tblProjects and tblTimeCardHours tables:
tblProjects: ProjectName
tblTimeCardsHours: TimeCardID, TimeCardDetailID, DateWorked, WorkDescription,
The design of rsubTimeSheet is shown in Figure 6.34. This subreport can easily be built from a wizard. Select all fields except TimeCardID and TimeCardDetailID from qrySubTimeSheets. View the data by tblTimeCardHours. Don't add any groupings, and don't sort the report. When you're finished with the wizard, modify the design of the report. Remove the caption from the Report Header, and move everything from the Page Header to the Report Header. Collapse the Page Header, remove everything from the Page Footer, and add a Report Footer with the expression =Sum([HourlyBillings]).
Change the format of the HourlyBillings and the TotalHourlyBillings controls to Currency. Use the Sorting and Grouping window to sort by TimeCardID and TimeCardDetailID.
The rsubTimeSheetExpenses report is based on qrySubTimeSheetExpense, which contains the following fields from the tblProjects, tblExpenseCodes, and tblTimeCardExpenses tables:
tblProjects: ProjectName tblTimeCardsExpenses: TimeCardID, TimeCardExpenseID, ExpenseDate, ExpenseDescription, and ExpenseAmount tblExpenseCodes: ExpenseCode
The design of rsubTimeSheetExpenses is shown in Figure 6.35. This subreport can easily be built from a wizard. Select all fields except TimeCardID and TimeCardExpenseID from qrySubTimeSheetExpense. View the data by tblTimeCardExpenses. Don't add any groupings, and don't sort the report. When you're finished with the wizard, modify the design of the report. Remove the caption from the Report Header, and move everything from the Page Header to the Report Header. Collapse the Page Header, remove everything from the Page Footer, and add a Report Footer with the expression =Sum(ExpenseAmount).
Change the format of the ExpenseAmount and the TotalExpenseAmount controls to Currency, and use the Sorting and Grouping window to sort by TimeCardID and TimeCardExpenseID.