Office Settings & Configuration

How to Display an Access Query Total on a Form

Phi Hung Vo Sep 30, 2026 868 views

Question details

The user needs to calculate and display the difference between total income and expenses from an aggregate query on a Microsoft Access form.

How to Display an Access Query Total on a Form

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating a database form to summarize financial records and display an aggregated balance.
Observed behavior: Aggregate query results do not automatically appear in the Expression Builder or form controls, preventing the direct display of computed totals.

Before you start

Ensure you have the exact table names, field names, and the saved aggregate query ready before attempting to reference them in your form controls.

Solution 1Recommended

Use the DLookup Function in a Form Control

Since query results cannot be directly linked as a standard control source, use the DLookup function to retrieve the calculated total.

Microsoft Access forms are designed to display data row by row. To show a single aggregate value from a query (like a grand total), you must use a domain aggregate function to look up the value independently.

Open Form in Design View

Right-click your form in the Navigation Pane and select Design View.

Add a Text Box

Go to the Form Design tab, select the Text Box tool, and click on the form to place the new control where you want the total to appear.

Open the Property Sheet

Select the newly added Text Box, press F4 to open the Property Sheet, and navigate to the Data tab.

Enter the DLookup Formula

In the Control Source property, enter your formula. For example: =DLookup("BalanceField", "YourAggregateQueryName"). Replace the field and query names with your actual database identifiers.

Save and View

Save the changes and switch back to Form View to verify that the query total is now correctly displayed.

Use the DLookup Function in a Form Control

Tip for Form Loading: If the data underlying the query changes while the form is open, you may need to use a macro or VBA to Requery the text box so the total updates dynamically.

Solution 2

Calculate Balance by Summing Row-Level Data

Instead of subtracting two separate aggregate totals, calculate the balance for each record first and then sum the result in a query.

Free Microsoft Office alternative

Need a lightweight alternative for tracking finances? Try WPS Office

While WPS Office does not include a relational database management tool like Microsoft Access, it offers a powerful, free alternative to Microsoft Word, Excel, and PowerPoint. If you are primarily managing financial records, tracking income, and calculating balances, WPS Spreadsheet is an excellent, user-friendly tool that handles complex calculations effortlessly.

1. Open WPS Spreadsheet: Launch WPS Office and create a new blank Spreadsheet to serve as your financial tracker.
2. Log Income and Expenses: Create dedicated columns for Date, Description, Income, and Expenses, entering your data row by row.
3. Calculate the Total Balance: At the bottom of your data or in a summary dashboard area, use the formula =SUM(C:C)-SUM(D:D) (assuming Income is column C and Expenses is column D) to instantly display your balance.

Highly compatible with Microsoft Office formats, including XLSX, DOCX, and PPTX.Free and lightweight alternative to heavy Microsoft Office installations.Advanced spreadsheet functions and pivot tables to easily calculate income, expenses, and totals without complex database queries.Familiar, tabbed user interface for quick adoption and seamless migration.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't my aggregate query show up in the Expression Builder?

Microsoft Access form controls bind to the specific record source of the form. Because aggregate queries return separate sets of grouped data rather than continuous rows related to the form's current record, they do not automatically populate in the Expression Builder for direct binding.

What is the syntax for the DLookup function in Access?

The basic syntax is DLookup("FieldName", "TableNameOrQueryName", "OptionalCriteria"). For a simple grand total from a query that only returns one row, you can safely omit the criteria parameter.

Can I calculate totals directly in the form's footer instead of using a query?

Yes. If your form is continuously bound to a table containing Income and Expense fields, you can add a Text Box to the Form Footer and set its Control Source to =Sum([Income]) - Sum([Expense]). This calculates the total for all records currently displayed on the form.

Office App Problems