You can perform different types of calculations in a list in Access Web Datasheet. You can display aggregate values, such as sum and average, for a column in the list. You can add calculated columns that use a formula to calculate values for each row in the list. You can also use formulas when specifying a default value for a column.
The following illustration shows the Employee-Payroll list in Access Web Datasheet. The list displays the aggregate values in the Total row at the bottom of the list, and a calculated column titled Net Pay.
The Total row displays a count of the rows in the Person column and the sum of values in the Net Pay column. The Net Pay column uses the formula that is displayed in the ScreenTip to calculate values for each row.
About displaying aggregate values
About displaying calculated values in a column
About using a formula to calculate default values for a column
In Datasheet View, you can show and hide the Total row by clicking Show Totals in the List tab of the ribbon. Initially, the Total row displays the sum for each numeric column in the list. If the list does not have any numeric columns, it displays a count of the items in the last column of the list. You cannot edit the values displayed in the Total row.
After enabling the Total row, you can show or hide the aggregate values for a specific column. You can also change the aggregate function that is used by a column by simply selecting a different function from the drop-down list in the Total row. The ScreenTip that appears when you hover your mouse pointer over an aggregate value specifies the aggregate function that is used by the column.
You cannot filter data in a list based on aggregate values. However, if a list is filtered, the aggregate values are calculated based only on the items that are included in the return set. The Total row does not get sorted along with the other rows in the list.
The following table lists the aggregate functions that are supported in a list, and the data types that support each function.
| Function name | Description | Data types that support function |
|---|---|---|
| Average | Returns the average of the values in the column being aggregated. | Number, Currency, DateTime |
| Count | Tallies the number of values. For Boolean, Attachment, and DocIcon columns, only Yes values are included. | Number, Currency, DateTime, Boolean, Attachment, DocIcon, ShortText, MultiLineText, SingleChoice, MultiChoice, Lookup, Hyperlink |
| Maximum | Returns the largest value in the column being aggregated. | Number, Currency, DateTime |
| Minimum | Returns the smallest value in the column being aggregated. | Number, Currency, DateTime |
| Sum | Calculates the sum of the values in a column. | Number, Currency |
| Standard Deviation | Returns the standard deviation of the values in the column being summarized, assuming the values are a sample of a larger population. | Number, Currency |
| Variance | Returns the variance of the values in the column being summarized, assuming the values are a sample of a larger population. | Number, Currency |
When you click the drop-down arrow in a cell in the Total row, you will see None displayed in the list, along with other aggregate functions. Select None for a column that you do not want to aggregate.
You can aggregate values in a calculated column. The list of functions that will be available for the column depends the data type of the value resulting from the calculation.
You can add a calculated column to a list to calculate values by using a formula. A formula can only refer to values in other columns within the same row. You cannot refer to a specific cell or a range of cells in a formula.
The calculated values are stored in the column, but you cannot edit the values. When the values referenced by the formula change, the list automatically recalculates the value for the calculated column.
The ScreenTip that appears when you rest your mouse pointer on a calculated value specifies the formula that is being used by the column. You can edit a formula by selecting a cell that uses the formula and entering edit mode. After you finish editing the formula, and exit edit mode, the new values are automatically calculated and displayed in the column.
You can sort and filter values in a calculated column.
When you add a column to a list of type single line, choice, number, currency, or date and time, you can specify a formula instead of a constant as the default value. When you add a new row, the list will evaluate the formula and show the result as the default value in the column.
When you specify a formula as a default value, you can call any of the supported functions, but the formula cannot reference other columns. Since the formula will be evaluated before you enter data in other columns, column references are not allowed.
