Practical Examples: Building Queries Needed by the Time and Billing Application for a Computer Consulting Firm
Build a query based on tblTimeCardHours. This query gives you the total billing amount by project for a specific date range. The query's design is shown in Figure 4.57. Notice that it's a Totals query that groups by project and totals by using the following expression:BillAmount: Sum([BillableHours]*[BillingRate])
Figure 4.57. The design of the qryBillAmountByProject query.

The DateWorked field is used as the Where clause for the query with this criteria:Between [Enter Start Date] And [Enter End Date]
The two parameters of the criteria are declared in the Parameters dialog box (see Figure 4.58). Save this query as qryBillAmountByProject.
Figure 4.58. The Query Parameters window for qryBillAmountByProject.

The second query is based on tblClients, tblProjects, and tblTimeCardHours. This query gives you the total billing amount by client for a specific date range. The query's design is shown in Figure 4.59. This query is a Totals query that groups by the company name from the tblClients table and totals by using the following expression:BillAmount: Sum([BillableHours]*[BillingRate])
Figure 4.59. The design of the qryBillAmountByClient query.

As with the first query, the DateWorked field is used as the Where clause for the query, and the parameters are defined in the Query Parameters window. Save this query as qryBillAmountByClient.
![]() | These queries are included on the sample CD-ROM in a database called CHAP4.MDB. Of course, if this were a completed application, you would build many other queries. |