Others

How to Calculate a Report Footer Total for Successful Quotes in Microsoft Access

Maira Mehtab Sep 24, 2026 869 views

Question details

The user wants to display a grand total price in an Access report footer that only includes quotes marked as successful.

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating a summary report for sales or quotes where only successful transactions should contribute to the final grand total.
Observed behavior: The user needs a specific formula or method to conditionally sum the total price field based on the status of a Yes/No field.

Before you start

Ensure your Access report is open in Design View and that you know the exact field names for your Yes/No status field and the price field you want to total.

Solution 1Recommended

Use a Conditional Sum Formula in the Report Footer

Use a combination of the Sum and IIf functions in a text box within the report footer to conditionally add prices.

In Microsoft Access, aggregate functions like Sum evaluate all records in the section by default. To selectively sum records based on a criteria (such as a Yes/No field), you can embed an IIf statement within the Sum function. This evaluates each record individually, adding the price only if the condition is met.

Open Report in Design View

Right-click your report in the Access Navigation Pane and select 'Design View' from the context menu.

Add a Text Box to the Report Footer

Navigate to the Report Design tab on the ribbon, select the Text Box tool, and click inside the Report Footer section to place the new control.

Enter the Conditional Sum Formula

Select the new text box, open the Property Sheet (press F4), and type the following into the Control Source property: =Sum(IIf([Successful]=True,[TotalPrice],0)).

Customize Field Names

Replace '[Successful]' with the actual name of your Yes/No field, and '[TotalPrice]' with the actual name of your price field as they appear in your record source.

Formula Explanation: The IIf function checks if the quote is successful; if true, it returns the TotalPrice for that record, otherwise it returns 0. The Sum function then adds all these evaluated values together.

Free Microsoft Office alternative

Looking for a Lightweight and Free Office Suite?

While WPS Office does not include a direct database alternative to Microsoft Access, it is a highly compatible, free, and lightweight suite for all your document, spreadsheet, and presentation needs. Transition seamlessly for your daily office tasks without the hefty subscription fees.

1. Visit the Official Website: Go to wps.com to download the latest version of WPS Office Free.
2. Install the Software: Run the downloaded installer and follow the on-screen instructions to complete the setup.
3. Open and Create Documents: Launch WPS Office to start working on your spreadsheets, documents, and presentations with full Microsoft format compatibility.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats.Lightweight and fast, consuming minimal system resources.Familiar, tabbed user interface for easy navigation.Built-in PDF editing capabilities at no extra cost.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my Sum(IIf(...)) formula returning an error in Access?

This usually happens if the field names in your formula do not perfectly match the names in your underlying query or table, or if the text box is accidentally placed in the Page Footer instead of the Report Footer. Aggregate functions like Sum must be placed in a Report Footer or Group Footer.

Can I conditionally sum multiple criteria in an Access report?

Yes, you can nest multiple IIf statements or use the Switch function within the Sum function to evaluate multiple criteria, though it can become complex to read. Alternatively, it is often easier to calculate these conditions in the underlying query.

How do I show a separate total for pending quotes?

You can add a second text box in the Report Footer with a similar formula, checking for False instead of True: =Sum(IIf([Successful]=False,[TotalPrice],0)). This will sum only the quotes that are not marked as successful.

Office Quick Solutions