Summarize Tool
Use Summarize to perform various actions (functions and calculations) on your data.
The Summarize tool can...
Return the sum for a column of data. The sum is calculated by adding all of the rows in the column.
Return the minimum or maximum value in a column.
Count the number of rows in a column.
Group a column of data by identical values.
Concatenate string values.
Perform a variety of mathematical calculations.
Go to Summary Actions for a complete list.
Tip
This tool has a One Tool Example. Visit Access Sample Workflows to learn how to access this and many other examples directly in Designer Cloud.
Tool Components
 |
Figure: Summarize tool with 2 anchors.
The Summarize tool has 2 anchors:
Input anchor: The input anchor connects to the data you want to perform actions on.
Output anchor: The output anchor displays only the results of your actions. Use the Join Tool to join the results of a Summarize tool with the original data.
Configure the Tool
The Summarize tool configuration window has 3 sections.
Select Columns to Add Actions
The names of the data columns from the input, and their associated Supported Data Types, are listed in the table.
Select a column to perform an action. Hold the Shift key and select to choose multiple columns to execute the same actions. Use the filter icon to make bulk selections. Choices include...
All: Select all columns.
None: Select no columns. Any previously selected fields are deselected.
Numeric: Select only columns with a numeric data type (integers, fixed decimals, floats, doubles.)
String: Select only columns with a string data type.
With your columns selected, select + Add Actions. You can choose from several actions. Keep in mind that actions are not available if they are not compatible with the selected Supported Data Types.
Select the actions you want to perform. Your actions are added to the Actions section.
You can perform multiple actions on a single column. To do that, select and add the column once for each action that you want to perform.
Actions
The Actions section contains data columns added from the previous section. Several options are available once you've added columns to the Actions section:
Reorder: Select and hold the reorder icon and drag the action to where you want it in the list, or select the column and use the up or down arrow icons to change the order of the columns. This also updates the column order of the tool output in the Results Grid.
Remove: Select a column and use the trash-can icon to delete the action.
Change action: To change the action, select the dropdown in the Action column and choose a compatible action.
Rename output column header: To rename an output column, select the column and then input a new name into Output Name.
Parameters
Some actions require you to specify additional parameters.
When additional parameters are required, specify them via the Parameters section at the bottom of the tool configuration window. For information on specific parameters, go to Summary Actions.
If additional parameters are not required, the Parameters section isn't available.
Summary Actions
This list describes the types of actions that the Summarize tool can perform:
Group By: Combine database rows with identical values in a specified column into a single row. All of the resulting data from the rows in a group are then summarized. Any non-blob or spatial object has this option. If no Group By column is specified, the entire file is summarized.
Sum: Return the sum value for the group. The sum is calculated by adding all of the values of a group.
Count: Return the count of rows in the group.
Count Non Null: This action is the same as Count, except it is only counting those rows that are not null. Null means there is no value set for the row. That is different from a 0 or an empty string.
Count Distinct: Return the count of unique rows in the group.
Count Distinct Non Null: This action is the same as Count Distinct, except it is only counting those rows that are not null. Null means there is no value set for this row. That is different from a 0 or an empty string.
Count Null: This action is the same as Count, except it only counts those rows that are null. Null means there is no value set for the row. That is different from a 0 or an empty string.
Min: Return the minimum value.
Max: Return the maximum value.
First: Returns the first record in the group, based on its record position.
Last: Returns the last record in the group, based on its record position.
Finance
Net Present Value (NPV): Calculates the Net Present Value for a group. Measures the excess or shortfall of cash flows, in present value terms, once financing charges are met. NPV Properties include...
Discount Rate (Per Period): The discount rate as a percentage. The default value is 8%.
Net Present Value w/Dates (XNPV): Calculates the Net Present Value for a group for a series of dates. XNPV Properties include...
Finance Rate: The finance rate as a percentage. The default value is 8%.
Date Column: The column that contains the associated dates.
Internal Rate of Return (IRR): Calculate the Internal Rate of Return for a group. The Internal Rate of Return of an investment is the interest rate at which the costs of the investment lead to the benefits of the investment. This means that all gains from the investment are inherent to the time value of money and that the investment has a zero net present value at this interest rate.
Internal Rate of Return w/Dates (XIRR): Calculates the Internal Rate of Return for a group for a series of dates. XIRR Properties include:
Date Column: The column that contains the associated dates.
Modified Internal Rate of Return (MIRR): A modification of the internal rate of return and as such aims to resolve some problems with the IRR. The MIRR is a financial measure of an investment's attractiveness. MIRR Properties include:
Finance Rate: The finance rate as a percentage. The default value is 8%.
Reinvest Rate: The reinvestment rate as a percentage. The default value is 8%.
Modified Internal Rate of Return w/Dates (MXIRR): Calculates the Modified Internal Rate of Return for a group for a series of dates. MXIRR Properties include:
Finance Rate: The finance rate as a percentage. The default value is 8%.
Reinvest Rate: The reinvestment rate as a percentage. The default value is 8%.
Date Column: The column that contains the associated dates.
Numeric
Average: Calculate an average value for the group. The average is calculated by taking the sum of all values and then dividing by the total number of values.
Percentile: Calculate the specified percentile value for the group. The percentile is calculated by sorting the data and then returning the row value relative to the specified percentile and its position in the sorted array—the largest value is the 100th percentile, the lowest value is the 0 percentile, the median is the 50th percentile, the 25th percentile is the value in the middle of the median and minimum, etc. Percentile properties include:
Percentile: Specify the percentile to return. The default value is 50%.
Median: Calculate the median value for a group. The median of the group is the middle value when the values are sorted in order. If there is an even number of values there is no number in the middle so the 2 numbers in the middle are averaged.
Mode: Calculate the mode value for a group. The mode of a set of numbers is the smallest number that occurs most often in a group of values. If all values are unique, the smallest number is returned.
Standard Deviation: Calculate the standard deviation for the group. Standard deviation is a measurement variability used in statistics.
Variance: Calculate the Variance for the group. The variance is calculated by taking the standard deviation and multiplying it times itself (StdDev2).
Average - Ignore 0's: Calculate the average as described above, ignoring records that have a value of zero.
Percentile - Ignore 0's: Calculate the percentile as described above, ignoring records that have a value of zero.
Median - Ignore 0's: Calculate the median as described above, ignoring records that have a value of zero.
Mode - Ignore 0's: Calculate the mode as described above, ignoring records that have a value of zero.
Standard Deviation - Ignore 0's: Calculate the standard deviation as described above, ignoring records that have a value of zero.
Variance - Ignore 0's: Calculate the variance as described above, ignoring records that have a value of zero.
String
Count Blank: Count how many rows in a group have a blank or empty value.
Count Non Blank: Count how many rows in a group do not have a blank or empty value.
Concatenate: Take all the rows in a group and concatenate the strings. Concatenate Properties include:
Start: The character specified appears at the beginning of the concatenated string. It is left blank by default.
Separator: The character specified appears in between each value of the concatenated string. The default is a comma.
End: The character specified appears at the end of the concatenated string. It is left blank by default.
Longest: Returns the longest string value of the group.
Mode: Returns the mode of the string values. The mode of a set of string values is the smallest string that occurs most often in a group of values. If all values are unique, the smallest string is returned. The smallest string is the first in ascending sort order.
Shortest: Returns the shortest string value of the group.