لطفا منتظر باشید ...
Practical Examples: Building Reports Needed for Your Application
The sample application requires several reports. A couple of the simpler ones are built here.
Designing the rptClientListing Report
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]
Figure 6.27. The qryClientListing querya foundation for the rptClientListing report.
To build the report, follow these steps:
Figure 6.28. Selecting the qryClientListing query.
Figure 6.29. Selecting CompanyName as the sort field.
Figure 6.30. A preview of the completed report.
Figure 6.31. Changing the report title.
Designing the rptTimeSheet Report
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.
Figure 6.32. The rptTimeSheet report in Design view.
Figure 6.33. The qryTimeSheet query in Design view.
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[View full width]=[rsubTimeSheet].[Report]![txtTotalHourlyBillings]+[rsubTimeSheetExpenses].
[Report]![txtTotalExpenseAmount]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:[View full width]tblProjects: ProjectName
tblTimeCardsHours: TimeCardID, TimeCardDetailID, DateWorked, WorkDescription,
BillableHours, BillingRate, and the expression HourlyBillings: [tblTimeCardHours].[BillingRate]*[BillableHours]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]).
Figure 6.34. The rsubTimeSheet report in Design view.
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).
Figure 6.35. The rsubTimeSheetExpenses report in Design view.
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.
