How to Use the DSum Function in Access

Save

Microsoft Access expressions compare to formula fields in Microsoft Excel, which means you can calculate field totals in a table or query by using the Dsum function. The function, which is built-in to Access, calculates the total sum of a set of field values in a selected 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 set period of time. The syntax for this function is DSum(expression, domain, [criteria]).

  • Open the Access database. Determine the fields in your table or the query that you need to total. The field results must be number values for the function to work properly. If you are using an older version of Access, the steps to create this function are similar, but the locations of the software tools might vary.

  • Select your saved query from the Access menu or create a new query using the Query Wizard tool.

  • Open the query in Design View by selecting Design View from the View menu.

  • Click 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 totaled. Replace this text with the name of your field.

  • Replace Table_name with the name of your table or the query in which 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 review only the rows of data where Field2 is equal to 10. 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 OK.

  • Select Run from the header menu to view the results of the query. The expression field displays the field totals.

Tips & Warnings

  • 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.

References

Promoted By Zergnet

Comments

You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!