Others

How to Create an Access Query to Count Closed and Open Records

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to count the number of closed and open records in a database based on whether a date field is filled or blank, which can then be used to generate a pie chart.

Product: Microsoft Access
Device & OS: not provided
Scenario: Generating summary totals to compare open versus closed status for reporting or chart creation.
Observed behavior: Requires a conditional SQL query that can differentiate and count records depending on the presence or absence of a value in the 'Date Closed' field.

Before you start

Before creating your query, verify the exact name of your data table and ensure the target date field is correctly named in your database schema.

Solution 1Recommended

Use a Conditional SUM Query in SQL View

You can use an IIF statement nested within a SUM function to evaluate whether the Date Closed field is null, effectively counting the totals for each status.

By utilizing the IIF function, Microsoft Access evaluates the condition of each record row by row. If the 'Date Closed' field has a value (IS NOT NULL), it assigns a 1 for closed records. If it is blank (IS NULL), it assigns a 1 for open records. The SUM function then aggregates these values to provide the final count.

Open Query Design

Open your Microsoft Access database, navigate to the 'Create' tab on the ribbon, and click on 'Query Design'.

Switch to SQL View

Close the 'Show Table' dialog box that appears. Right-click the query tab and select 'SQL View' from the context menu.

Input the SQL Statement

Paste the following SQL query into the text area: SELECT SUM(IIF([Date Closed] IS NOT NULL,1,0)) AS CountClosed, SUM(IIF([Date Closed] IS NULL,1,0)) AS CountOpen FROM YourTableName;

Update Table Name and Run

Replace 'YourTableName' with the actual name of your table. Click the 'Run' button (the red exclamation mark) on the Design tab to execute the query and view your open and closed totals.

Field Name Adjustments: If your date field has a different name, make sure to replace [Date Closed] with your exact field name, keeping the square brackets if the name contains spaces.

Free Microsoft Office alternative

Manage and Visualize Your Data Seamlessly with WPS Office

While Microsoft Access is a powerful database tool, many data tracking and charting tasks can be efficiently handled using spreadsheets. WPS Office offers a free, lightweight, and highly compatible alternative to Microsoft Office, featuring robust spreadsheet tools to count, filter, and visualize your open and closed records effortlessly.

1. Install WPS Office: Download and install WPS Office for free on your preferred device.
2. Import Your Data: Open WPS Spreadsheet and open your exported database tables or raw data files.
3. Calculate and Chart: Use the =COUNTBLANK() or =COUNTIF() functions to calculate open and closed totals, then highlight the results and insert a Pie Chart from the Insert tab.

Fully compatible with Microsoft Excel (.xlsx, .xls) and CSV formats.Easily use COUNTIF functions to track open and closed records without needing to write SQL queries.Create stunning pie charts and dynamic dashboards in just a few clicks.Lightweight installation and a familiar interface for seamless migration from Microsoft Office.

Frequently Asked Questions

Can I use this Access query for other status fields besides Date Closed?

Yes. You can modify the field name inside the brackets and change the condition. For example, if you have a text field called 'Status', you could update the expression to SUM(IIF([Status]='Closed',1,0)).

How do I create a pie chart from this query in Access?

Once you save the query, you can use it as a Record Source. Go to the Create tab, insert a Chart into a Blank Form or Report, and select this saved query to map the CountClosed and CountOpen fields to your pie chart.

What exactly does the IIF function do in this SQL statement?

The IIF (Immediate If) function evaluates a specific condition. In this query, it checks if the date field is empty or not. If the condition is true, it returns the first value (1). If false, it returns the second value (0). The outer SUM function then adds all the 1s together to generate the total count.

Office Quick Solutions