Filtering with MSQuery

Modified on Sun, 26 Jul at 4:19 AM

Overview

The QQube Excel Add-In is designed to hide the complexity of database technology. However, a workbook can become difficult to use, save, or email when it contains a very large amount of QuickBooks data.

PowerPivot is one option because it can efficiently handle millions of rows. When a standard Excel workbook is the preferred delivery format, you can instead reduce the amount of data returned to Excel by editing the underlying Microsoft Query.

How the QQube Excel Add-In Works

The QQube Excel Add-In acts as a front end for Microsoft Query. QQube automatically creates the underlying SQL statement, while Microsoft Query allows advanced users to add criteria before the records are returned to Excel.

In this example, the report is limited to jobs that have active estimates. This can be useful for contractors who have many jobs in QuickBooks but want the workbook to contain only currently active work.

Excel Filtering Compared with Microsoft Query Filtering

Method	What It Does	Workbook Impact
Excel or PivotTable Filter	Hides records from the visible report.	All retrieved records remain stored in the workbook.
Microsoft Query Criteria	Limits the records returned by the query.	Only matching records are loaded into the workbook.

Conventional Excel Filtering

A native Excel or PivotTable filter changes what is displayed, but it does not reduce the amount of source data stored in the workbook.

Excel PivotTable filter hiding jobs without active estimates

Why the Workbook Size Does Not Change

The hidden records are still present in the workbook's underlying data. The filter affects visibility, not the number of records retrieved from QQube.

Filter the Data with Microsoft Query

1. Open the Query Definition

Select the QQube data range.
Select the Data tab.
Select Properties.
Select the Definition tab.
Select Edit Query.

Excel connection properties Definition tab showing Edit Query

2. Display the Criteria Grid

In Microsoft Query, select View > Criteria.

Microsoft Query View menu showing the Criteria command

3. Add the Active-Estimate Criterion

Drag Job Has Active Estimates into the Criteria Field.
Enter Yes in the Criteria Value.

Microsoft Query Criteria grid with Job Has Active Estimates set to Yes

Filter Applied Before Excel Loads the Data

The query now returns only records where Job Has Active Estimates equals Yes.

4. Return the Query to Excel

Select File > Return Data to Microsoft Office Excel.

Microsoft Query File menu showing Return Data to Microsoft Office Excel

5. Confirm the Updated Connection

Select OK to return to the workbook and apply the revised query.

Excel dialog box confirming the revised Microsoft Query connection

Review the Filtered Result

After the query refreshes, the workbook contains only records where Job Has Active Estimates is Yes.

The field's Excel filter list now contains only the matching value because the nonmatching records were never returned to the workbook.

Excel filter showing only Yes for Job Has Active Estimates

Expected Result

The workbook retrieves only jobs that have active estimates. This reduces the number of stored rows instead of merely hiding unwanted records after they have already been loaded into Excel.