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