Query columns

The values that are available for the Method property depend on whether the MethodType Property is set to Date or Totals. The following table describes the values for each setting.

MethodType property setting	Method property value	Description
Date	Day	Gets the day from the date expression for the field in the query column. The day is returned as an integer, in the range of 1 to 31, which represents the day of the month. If the day in the date expression is 0, then 1 is returned.
	Month	Gets the month from the date expression for the field in the query column. The month is returned as an integer, in the range of 1 to 12, where 1 represents January and 12 represents December. If the month in the date expression is 0, then 1 is returned.
	Year	Gets the year from the date expression for the field in the query column. The year is returned as an integer. If the year in the date expression is 0, then 1900 is returned.
Totals	Sum	Adds the values of all fields for the specified column within a group.
	Count	Returns the number of rows that are included in the dataset within a group. Note The Count method is associated with the DataItem and not with a specific column, so the DataSource property must be blank.
	Average	Calculates the average value of the fields in the column within a group.
	Min	Retrieves the lowest value of fields in the column within a group.
	Max	Retrieves the highest value of fields in the column within a group.

You can only use the date methods on fields of the Date and DateTime data type.

When you set up a totals method on another column in the query, rows in the resulting dataset are grouped by the day, month, or year, depending on the Method property setting.

For more information about date methods, see Using the Date Method to Retrieve Day, Month, and Year Data.

A totals method performs a calculation on fields in a column and returns a single value. When you set a totals method on a column, the retrieved records are automatically grouped by the other columns in the query, as indicated by a check mark in the Group By column in Query Designer. When grouped by a column, records that have similar values for the column are grouped together. The totals method is applied against the records in the group. A summary value is calculated for the group and returned in a single row for the group in the dataset. The Method property values correspond to the SQL aggregate functions SUM, AVG, MIN, MAX, and COUNT in SQL SELECT statements.

Except for the Count method, you can only use a totals method on a field of the Decimal data type. The totals method returns a decimal value that is calculated from values of all fields in a column.

Count is only associated with its parent DataItem, not a specific column. Therefore, when you set a column to Count, you leave the DataSource Property of the column blank. The Count method returns an integer value that corresponds to the number of rows that are returned from the table that is specified by the data item associated with the column.

For more information, see Understanding Query Totals and Grouping.

Tasks

Reference

Concepts