Others

How to Sum Hours by Order and Status in Access Query

Camila Milosovich Oct 16, 2026 868 views

Question details

The user needs an Access query to sum the hours for each order specifically when the status is 'Ready' and the location is 'InArea', with the condition that the calculation must stop as soon as the status changes.

How to Sum Hours by Order and Status in Access Query

Product: Microsoft Access
Device & OS: not provided
Scenario: Aggregating data across a large table (over 500,000 records) where records must be evaluated sequentially to sum hours until a status break occurs.
Observed behavior: A standard Group By query fails to account for sequential status changes, and using reserved SQL keywords as field names causes syntax errors during query execution.

Before you start

Before building your query, check your table design for SQL reserved words like 'WHERE' or 'ORDER', as these must be enclosed in brackets to prevent syntax errors during execution.

Solution 1Recommended

Use a Staged Query Approach for Sequential Aggregation

Implement a two-step query process to first isolate consecutive qualifying records and then aggregate the totals for each order.

Because you need to stop summing when a status changes, a standard GROUP BY query is insufficient. A standard aggregation evaluates the entire dataset at once, ignoring the sequence of events. A staged approach allows you to isolate the contiguous 'Ready' and 'InArea' records before performing the final sum calculation.

Additionally, if your table uses reserved SQL words like 'WHERE' or 'ORDER' as field names, you must explicitly enclose them in square brackets to avoid expression errors.

Create the base filtering query

Open Access Query Design view, add your table, and add the necessary fields. In the Criteria row for your status field, enter 'Ready'. For your location field, enter 'InArea'.

Bracket reserved words

Ensure any field named WHERE or ORDER is written as [WHERE] and [ORDER] in the query grid to prevent Access from treating them as SQL commands.

Identify consecutive records

Use a subquery or sequential numbering field in this first query to identify the contiguous blocks of 'Ready' statuses for each [ORDER], filtering out records that occur after a status change.

Create the final aggregation query

Create a second query using the first query as your data source. Click the 'Totals' button (Sigma icon) on the ribbon.

Group and sum the results

Set the Total row to 'Group By' for the [ORDER] field, and set the Total row to 'Sum' for the HOURS field to get your final calculated row per order.

Use a Staged Query Approach for Sequential Aggregation

Database Design Best Practices: To prevent future query complications, consider renaming fields like 'ORDER' to 'OrderID' and 'WHERE' to 'Location' directly in your table design.

Free Microsoft Office alternative

Analyze Exported Database Data with WPS Office

While Microsoft Access is used for complex relational database management, you can easily export your tables and perform advanced data analysis, conditional summing, and reporting using WPS Spreadsheet. It offers a lightweight, highly compatible alternative to Microsoft Office.

1. Export Access Data: Right-click your table or query in Microsoft Access and export the data to an Excel workbook or CSV file.
2. Open with WPS Spreadsheet: Launch WPS Office and open your exported file to view your dataset.
3. Apply SUMIFS Functions: Use the built-in SUMIFS function or PivotTables in WPS Spreadsheet to easily calculate conditional totals by order and status without writing complex SQL code.

Free and lightweight Office suiteFully compatible with Microsoft Excel (.xlsx) formatsAdvanced PivotTables and SUMIFS for powerful data aggregationFamiliar user interface with zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a syntax error when using WHERE as a field name in an Access query?

The word 'WHERE' is a reserved SQL keyword used for filtering records. If you use it as a column name, Access attempts to process it as a command. You must enclose it in square brackets, like [WHERE], to tell Access it is a field name.

Can I use a standard GROUP BY query to sum hours until a status changes?

No, a standard GROUP BY query will sum all records that match the criteria across the entire dataset, ignoring the sequence of rows. You need a staged query or a custom VBA function to evaluate records sequentially and stop the calculation at a status change.

How do I group records by order in Access query design?

Open your query in Design View and click the 'Totals' button (the sigma icon) on the ribbon. This adds a 'Total' row to the design grid. Leave the Total row as 'Group By' for your Order field, and change the Total row to 'Sum' for your Hours field.

Office Quick Solutions