Filtering with MSQuery

Overview

The purpose of the QQube Excel Tool is to hide the complexities of database technology, but there are times where the amount of data in a spreadsheet can make it unwieldy and too large to use, or to e-mail to others.

One option is to use PowerPivot, as it handles millions of rows.  However, that may not be your preferred delivery.

The QQube Excel Add-In is actually a front-end for MSQuery, which contains the actual SQL Statements that QQube auto generates behind the scenes.

Here is how to invoke MSQuery to do the filtering.

In this example we are going to filter out jobs that don't have active estimates. This can be especially helpful for contractors who have many, many jobs in their QuickBooks file, but only want to see what is active.

Example of Conventional Filtering

In our first storyboard, we are filtering using the native Excel Pivot Table filter. But regardless of the filter - the data is still stored in the spreadsheet.  

And this does not reduce the data set within the spreadsheet.

Example of Filtering with MSQuery

To access MSQuery, go to the Data Tab, click on Properties, Highlight the Definition Tab, and click on Edit Query.

Show the Criteria Grid. View > Criteria.

Drag and drop the field "Job Has Active Estimates" into the Criteria Field. Type 'Yes' into the Criteria Value.

Click on File > Return Data to Microsoft Office Excel.

Click on OK to return to the spreadsheet.

Notice that there is only ONE option for Job Has Active Estimates - 'Yes'.