Creating a Custom Report with the Simple Query Builder

The Simple Query Builder is a wizard in Prime that turns making a query into a series of simple steps. By answering a few questions, the user can get out an amazing amount of information. This feature has been greatly improved from earlier versions.

a) The first step is to open the query builder. Do this either from the Touch Tiles under reports or choose it from the Ribbon Bar under reports.

b) A form opens up that has a series of choices that you need to fill out. If we want to create a new report, the first decision is whether we want to report on a single table or a combination of two tables. This is a new feature.

1) A Table or Query opens up an existing table or query for you to report on.

2) A Combination of two Tables gives you a choice of several custom queries that let you report on information from two tables. It would be possible to put choices in here that would include the fields from 3, 4 or more tables.

c) When the pull down is clicked, the choices of the multiple tables appear.

d) For our example the Eligibility and Clients choice will be used.

e) Once selected, we see a list of fields from both tables that we can choose to include in our query.

1) To select the fields, click on the one you want in the Choose box and press the right arrow to move it into the included box.

2) Pressing the arrow pointing at a line moves all the fields in.

3) If you want to remove a selection, click on it in Included fields box and press the arrow pointing to the left.

f) Our next choice is limiting the data and ordering it.

1) We use the Limit box to set our criteria. This report is going to include only data where the date open from the Eligibility sheet was in 2016. We use the >= and <= operators to set these criteria.

2) The Order box is used to sort the resulting data. The date opened is selected to sort the data.

g) Use the Run Query button to run your report.

1) Before it runs the report, it asks if you want to save the query. This allows you to run it in the future.

2) The last step is seeing the results of our query.

3) The SQL Language box lets more advanced users see what code the builder constructed. This code could be copied and pasted into the SQL view of a new Access query and further customized.

h) This is the result of our query. The builder allowed us to use fields from two different tables, ClientsW and Eligibility.

i) If we had saved the query, we could play it back in the future by opening the Simple Query Builder, clicking on Choose and Run a Saved Query, and choose it from the list.