• OfficeQ Query Editor
• Queries Subset Form

(We recommend printing this page to use as a reference while running the program)

To run the OfficeQ Query Editor program, select the OfficeQ Query Editor menu item from the DataBlox OfficeQ6 submenu of your Windows start menu.

When the program is run, a large form is displayed that is used to edit and run SQL database queries.

OfficeQ Query Editor (main form)

This form is used to run and/or edit SQL queries. When the form starts, all queries are listed on the left. To run a query, you need to pick the .MDB database file containing data to be queried using the top left box. You also need to pick the query from the list on the left. After you run the query, the data resulting from the query will be placed in a form and displayed. The purpose of this is to very that the query is producing the correct results - you cannot do anything useful with the displayed data.

• Curr MDB File - The file containing the data that will be queried.
• Select MDB File - Click here to use a file dialog to change the Curr MDB File.
• Run Current Query - Run the currently selected query. This also occurs if the currently selected query is double clicked. The query is run and the results are displayed. The displayed results cannot be transferred anywhere or used for any purpose - they just show what the query generates. When the query is run from the Excel app - the results will be placed in a spreadsheet. When run from the Access app, the results can be send to an Access report.
• Transfer Queries - This transfers the queries data (the set of queries) from file ...mdb_with_queries... to ...bin_with_queries... This is automatically done wherever this program is exited - so normally you won't need to do it manually.
• Copy Query - Copy the currently selected query to make a new query. A dialog will ask for the name of the new query. All other fields are simply copied and can be edited once the new query exists.
• Queries Subset - Clicking this button will start the Queries Subset Form. See below for the purpose of this form and how to use it.
• EachRow - Where used, this is a brief explanation of what each row of the query results will contain after the query is run. It is documentation only and is often empty.
• Columns - Where used, this is a brief list of what each column of the query results will contain after the query is run. It is documentation only and is often empty.
• XlsArgs - These are arguments that can be passed to the Excel code that places the query results. (details to be added later) If you add VBA code to Excel, you can add your own arguments that the Excel code can pick up.

List of queries

This list contains a row for each query available to run. To run a particular query, you need to make it current by clicking on it in the list - then click Run Current Query to run it.

The queries can be sorted by clicking the field name at the top of each column (such as sTablesUsed or sGroup). To delete a query, click on it in the list - then click Ctrl-Delete. We use these fields to help categorize / sort / maintain the queries. Some of them are likely of dubious value to anyone else!

The individual fields of the listing have the following meanings:

• sName - The name of the query. To help categorize the queries, we typically start sName with a category letter (such as C for customer or A for account), then a 3 digit 'difficulty' rating. The rating is used in sorting - for example all customer queries are sorted easiest to hardest when the queries are sorted by sName. We then add a brief description of what the query is. When an Excel worksheet is used to capture the query output, this field is usually entered at the top to identify the query that created the data.
• sTablesUsed - This field lists what tables are used for each query. When you are creating a new query, it's often helpful to view or copy another query that uses the same table(s).
• sTech - list of technical features used, such as:
  Gr - group by
  Ha - having
  J3 - 3 table join
  Ji - inner join
  Jl - left join
  Pi - transform / pivot
• sGroup - This field contains a single character for each of the query groups listed on our web site.
• sPubLevel - The first character of this field contains a 'P' is the query is publishable. (Any query that makes it to our web site is considered publishable - and therefore will have the 'P'). Other characters are used for various purposes that change over time.

SQL text of query

The upper right box contains the text of the query as it will be submitted to the query runner in the database. Comments (text that is not considered to be part of the query) are allowed - using one of 2 forms:

If a line containing 5 or more dashes is used (such as: ----------------), then that line and any lines following it are considered a comment and are not part of the query.

Any line where the first printable character of the line is a # is considered a comment. Subsequent lines may still be part of the query.

Notes

The lower right box contains notes. Any information can be put here.

Queries Subset Form (Advanced)

This form is used to reduce the number of queries shown in the main form. We initially created this for internal use, but some customers also found it useful. It allows showing only queries that match a certain criteria - such as only queries that use the cCustJob table or only queries that contain technical features, such as GroupBy clauses.

List of Queries

Field sName contains the name of the query.

SQL text of query

Upper right box contains the text of the query. Note that the only table queried here is named 'Query'. This is the table that holds the queries.

Field List

Lower right box. This is simply a list of the fields that make up the Query table - with field name, data type, and example of each. It's here to help write queries.