What an assignment! The query to implement this request is shown in Figure 20-12
(qrySalesJan08). This query sums the purchases by each customer for the month beginning
655
Advanced Access Report Techniques 20
1/1/08 and ending 1/31/08. Because the Purchases column is sorted in descending order, the customers
buying the most product will appear at the top of the query results set. The OrderDate
field is not included in the query results and is used only as the query??™s selection criterion (notice
the Where in the Total row).
FIGURE 20-12
An interesting query that sums data and sorts the query results in descending order
Although you could do much of this work at runtime using VBA to programmatically sum the values
returned by the query or a SQL statement in the report??™s RecordSource property, you should
always let the Access query engine perform aggregate functions. All Access queries are optimized
when you save the query. You??™re guaranteed that the query will run as fast as possible??”much
faster than a filter based on a SQL statement in a report??™s RecordSource property.
Pages:
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298