(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.
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
- 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
- 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
- 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.
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
- 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
- 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.
The lower right box contains notes. Any information can be put
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.
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.