Access Aggregate Calculations

As you worked through the lesson on calculations, you may have thought that the calculation you really wanted to create was a sum of hours worked on one project for the Projects form. Unfortunately, displaying aggregate calculations on a form is rather complicated. However you may find that the ease of creating aggregate calculations on reports with the Report Wizard makes up for that. You might want to try working around the aggregate calculation problem by creating a query that includes all the data you need on your form as well as a new field that calculates the result you want to display on the form.

Unlike Access 2010 web databases, web apps do support aggregate queries, with the familiar aggregate functions you have in client queries. Not only are they much faster to build and view than equivalent data macros, but you can also use them as sources for data macros. As you may expect, you still can specify whether a query should return only distinct values or restrict the numbers of rows returned.

Table 4-14 shows that there are times when queries and fi elds in tables are not updateable. As a general rule, any query that performs aggregate operations or uses an ODBC data source is not updateable; most other queries can be updated. When your query has more than one table and some of the tables have a one-to-many relationship, some fields might not be updateable (depending on the design of the query).

If a query uses two tables involved in a one-to-many relationship, the query must include the primary key from the one table. Access must have the primary key value so that they can find the related records in the two tables.