MSQuery filtering with QQube and Excel | CLEARIFY

Using MSQuery to filter data in your spreadsheet

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.

We are going to do this, by using the mechanism that Excel uses to request the data from the QQube database - MS Query.

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. 

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.

Without MSQuery

In our first storyboard, we are filtering using the native Excel Pivot Table filter. But irregardless of the filter - the data is still stored in the spreadsheet.  This is not the optimal scenario to reduce the file size.

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'.