The Simple Query Builder

The Simple Query Builder is much more powerful than you think. This was brought to my attention during a recent training in Louisiana. One of the trainees pointed out a way to use it that I had never thought of before.

The Simple Query Builder that is part of Prime 16 is much more powerful that earlier versions included in Prime. It exists to allow users, even those who don’t know how or don’t have the ability to use the Microsoft Query Builder, to build quite sophisticated queries and then play them back.

Changes from earlier versions is its ability to:

1. Be based on more than one table, such as Clients and Eligibility
2. Admins can build more complex queries, including 5 or 6 tables. These queries can be used as a basis to write a query in the Simple Query Builder. This is much simpler than using multiple tables. It works just as though all the data was in a single table, rather than the actual five or six tables that were used.
3. It has an additional sort level
4. It has an additional criteria level
5. It displays the SQL Language. This language can be copied and pasted into the Microsoft Query builder SQL View and used or modified.
6. The user gets to choose which fields are displayed, what sort order to use and what criteria are entered.
7. The Query can be saved and played back at any time.

Once the query is run, you can use the selections on the Search choice on the Ribbon bar to:

1. Limit what records are displayed based upon what is selected.
2. Change the sort order.
3. Enter a whole range of date criteria, such as between, next quarter, this week, etc.
4. Even open the advanced box and get the equivalent to the Access Query builder utility.

Here we will change the results to show only clients records that that were closed in 2015. Also note, this query is using a combination query that include fields from both the Client and Eligibility tables.

This means you can get a general list of cases, other services, etc. that might apply. Then choose which fields from what table are displayed. You can then ‘drill down’ to the records you want.

The drill down works exactly the way it does on the Search Screens. Here it is using the ‘Microsoft Query Builder look’ to control what records are displayed! You can build multiple field - complex criteria and sorts. Here we use it to look for Clients over 125% of Poverty.