Microsoft Access 2010 expressions are comparable to formula fields in Microsoft Excel Expressions, and you can calculate fields totals in a table or query by using a function called DSum. The DSum function is a built-in function in Access 2010 that calculates the total sum of a set of field values in a select set of records. For example, you might use the DSum function in a query to calculate the total purchases made by a customer over a select period of time. The syntax for this function is DSum(expression, domain, [criteria]).
Open your Access 2010 database. Determine the fields in your table or query that need to be totalled. The field results should be number values for the function to work properly. If you are using a version of Access other than 2010, the steps to create this function are similar, but the locations of the software tools could vary.
Select your saved query from the Access menu or create a new query using the Query Wizard. The Query Wizard can be found under the "Other" tab on the "Create" menu.
Open your query in "Design View" by selecting "Design View" from the "View" menu.
Click on the "Builder" button under the "Design" tab to open the "Expression Builder" pop-up window.
Paste the following "DSUM" syntax into the expression builder window: DSum("[Field_name]","Table_name","[textfield] = 'Value' ")
Highlight the "Field_name" area of the expression. "Field_name" in this example is the expression and represents the field in your table or query that includes the values to be totalled. Replace this text with the name of your field.
Replace "Table_name" with the name of your table or query where the field is housed. This is the domain area of the function and identifies the set of records to be evaluated.
Highlight "[textfield] = 'Value' " to modify the criteria for the function. Criteria restrict the range of data on which the DSum function is performed. For example, if your textfield is Field2 and your value is "10," the function will only review the rows of data where Field2 is equal to 10. Criteria work similarly to a "WHERE" clause in SQL expressions. You can omit this section of the function if a criteria modification is not necessary.
Replace "[textfield]" with the field name that includes the criteria and "Value" with the criteria text or number.
Click "Run" from the header menu to view the results of the query. The expression field will display with the appropriate total of your fields.
- You may use functions in Access to calculate data in tables, reports and queries. DSum calculates the values of fields before the data is grouped in Access.
Tips and Warnings
- You may use functions in Access to calculate data in tables, reports and queries.
- DSum calculates the values of fields before the data is grouped in Access.