A Quick Start to Office-QB
Epsilon-Logic Systems (Updated May 15, 2003)
This article is a quick start to Office-QB, the best visual query builder for office environment. In few simple steps you will learn how to use the Office-QB application to create queries helpful in your daily tasks running your business.
In creating RDBMS (Relational Database Management Systems) queries a little knowledge of the SQL language is helpful. But if you are not familiar with SQL command syntax, do not worry, because Office-QB is designed precisely for that purpose. The visual interface of Office-QB will help you create queries with a few drag-drop and field selection methods. In due time, it will also improve your SQL language skills and abilities, so that you will be able to create fairly complex or advance SQL queries.
· How can I use the query builder in office environment?
· How do I get back to an existing QB-project?
· How do I connect to a database?
· How do I start with a query?
· Can I edit the content of a query result?
· How do I export the result of a query to other applications?
· How do I output the result of a query in HTML format?
· Can I mail the query result directly?
How can I use the query builder in office
environment?
In most corporations or business environments, a multitude of
business processes develop or occur. A large and critical portion of these
processes dwell in or around a legacy system, customized especially for the
particular business information system. Smaller but more frequent portions of
these data processes require more creative interaction or rather more flexible
treatment than the controlled alternative of the methods available in the legacy
system. Such processes may involve the retrieval of data from several databases
of various types to be used with Microsoft Office® products or other
software applications, transfer of data from one database system to another,
data warehousing and analysis, database administrative tasks, generation of
query based reports in HTML or other formats, as well as other such data
processes.
The aim of
Office-QB
application is to bring these non-trivial tasks to the level of average office
employee skills. So that the end-users may create and run queries against a
variety of back-end databases, both inside and outside the organization's legacy
system. Business analysts may use
Office-QB
application to analyze critical business data. Database administrators may use
Office-QB
application as a smart query builder tool in their daily work. Finally, it is
always wise to have a smart query builder tool around, since there is always a
data processing question hanging around in the mind of any office employee. The
relevant question is how can one familiarize oneself with SQL queries in a short
period of time, and advance their business information logic and skills.
Office-QB may be used along with Microsoft Office®
products, such as Excel, Access, and Word, against Microsoft SQL Server,
Microsoft Access, as well as other non-Microsoft databases (such as Oracle,
Sybase, DB2, etc.), to enhance the overall data retrieval and control skills in
your office environment. In particular, using
Office-QB you may
access data from MS-Access, MS-SQL Server, Oracle, Sybase, DB2 and other
databases, all in one single QB-project. Moreover, you may utilize
copy/paste methods to transfer selected data from query results of
Office-QB to
Microsoft Office® applications. You may also use the Import
feature of
Office-QB to
transfer bulk data between different database types. In addition, using the
output features of
Office-QB one may
export query results as HTML documents, or send direct mail that contains query
results via the Mail Query
special menu.
How do I make a QB-project?
To start with
Office-QB
application, you will need to create a QB-project (Query Builder
project). A QB-project will organize your work into a user-specified
directory on your system. In this way saving you time and effort by avoiding the
undesirable alternative of query files scattered all over your system.
To create a project run the
Office-QB
application, and from the File
menu select New Project.
This will open the New Project
dialog (see Figure 1 for more details),
In the default New
tab of this dialog, enter the
Project name field and set the
Project storage location
via the button next to the edit-box. Note that this 3-dot button will open the
standard Windows Browse for Folder
dialog, in which you may select a path for project storage files. After entering
the project name and path, click on the
OK button to create the
project. This will display the project as an MDI child window in the main window
of the
Office-QB
application, with the Connections
pane containing only the root node, namely the
Databases node (note
that the Connections
pane is located on the upper-right corner of the main window).
The next step you need to know in using
Office-QB is to
define a data access connection to the desired database, against which queries
will be executed. This step will be described next, but first we need to inform
that you may get back to this project by using the project explorer of the
Recent tab via this
same dialog.
How do I get back to an
existing QB-project?
If you want to get back to an existing QB-project, you may
call the New Project
dialog just like the way you used in creating new project, but this time you
need to click on the Recent
tab of the dialog. In this case the dialog has the appearance displayed in
Figure 2 below:
Figure 2. Showing the New Project dialog with the Recent tab selected
Note that the Recent
tab of this dialog resembles a project explorer, in the sense that, when
selecting a recently created project, the project's content is displayed in the
tree-view control on the right side of the dialog. In this way you may explore
the project contents before opening it in the main window of
Office-QB. Note
that sometimes the links of this
Recent Projects list-box need to be refreshed via the
Update button.
To open an existing project, you may select the project from the
Recent Projects
list-box and then click the OK
button.
How do I connect to a database?
After you create or open a QB-project in
Office-QB, from
the
File menu select
New Connection
menu item, which will open the
New Database Connection
dialog, as shown in Figure 3 below (note that an alternative way to access this
dialog may be via the
New item of the popup menu,
using the right-mouse click on the
Databases root-node of
the
Connections
pane):
Figure 3. Showing the New Database Connection dialog
In the New Database Connection dialog, the user must then select a data access method using one of the items in the Data Access Type list-box, and enter a user-friendly name for the connection in the Connection Name text-box. When the user OK-s this dialog, depending on the data access type selected, different type of data access dialogs may be prompt. For illustration purpose, let us select the OLE DB Dynamic Data Access : ADO item from the Data Access Type list-box. This will prompt the Data Link Properties dialog shown in Figure 4 below:
Figure 4. Showing the Data Link Properties dialog
In this Data Link Properties dialog, the user must first select the OLE DB provider from the Provider tab. For example, for MS-SQL Server the user must select the Microsoft OLE DB Provider for SQL Server item from the list of providers. For Oracle the best option is the native Oracle Provider for OLE DB. For MS-Access one may use the Microsoft Jet 4.0 OLE DB Provider, while for all other ODBC compliant databases the Microsoft OLE DB Provider for ODBC may be used.
For example let us assume that we want to connect to the Northwind database, which comes as a sample database in MS-SQL Server. We proceed by first selecting the Microsoft OLE DB Provider for SQL Server item from the list in the Provider tab and clicking the Next >> button. This will switch the tab to the Connection tab, where more details must be entered about the database connection, such as: data source name / location of data / server name, server logon information, initial catalog or database name.
In this Connection tab, for step 1, you need to specify the server name, by either selecting it from the combo-list, or (if not present) you must type-in the exact name. For step 2, you must select either of the radio buttons for authentication to the SQL Server. For example, the Use Windows NT Integrated Security option, will use the Windows NT logon information and therefore requires no further logon/password information. On the other hand, the Use a specific user name and password option, requires the user's SQL logon information, which was granted by the database administrator. Finally in step 3, you must select a database name from the list of database in the combo-box.
When the connection information is specified and the dialogs submitted, a new connection node is added to the Databases root-node. So that in the future you do not need to redefine this connection information for the specific database connection. All you have to do to establish connection to the specific database, is to double-click on this connection node.
Note that, in the future if you want to modify the properties of an existing connection node, you may use the Properties menu item from the popup menu, obtained via the right-mouse button click method on the selected connection node itself. This will display the connection information dialog corresponding to the data source and data access type for the selected connection node, with which the user may modify the data source name / location of data / server name, server logon information, initial catalog or database name.
Once a connection node is defined and open, the user may create any number of queries for that particular database connection. These queries will appear as child nodes under the connection node in the Connections pane. In the following paragraph we will give you and example of how to create a simple query on the Northwind sample database in the MS-SQL Server.
How do I start with a query?
After defining the connection to the desired database, we are ready
to create queries against this database. To create a new query on the particular
database connection, we
use the
New menu item from the popup menu
(see Figure 5), with the right-mouse button click on that connection node.
This will create a query child node under this connection node, and will open
this new query into the query builder panes. By default any new query has the
name “New Query”,
which may be modified by a second left-mouse click on the query node making the
node label editable.
Figure 5. Showing the popup menu
Alternatively, the user may create a new query based on an existing query by simply copying the existing query via the Copy menu item of the right-mouse popup menu, and then using the Paste menu item of this same popup menu triggered on the connection node, the user may paste a copy of the existing query with the name prefixed by “Copy_of_” string.
To save modifications to a query the user may use the Save Query menu item under the File menu. This will store the query changes in the binary file for the connection, over which the query is defined. Note that, the user may open an existing query into the query builder by double-clicking on the node corresponding to that query. Only one query may be opened for edit at the same time.
After we create a new query, the Relations, Columns and SQL panes are initially empty. We drag-drop selected tables from the DB Browser pane into the Relations pane. For example try grabbing the Orders table from the Tables folder of the Northwind database node in the DB Browser pane, drag-drop it into the Relations pane. Similarly drag-drop the Order Details table into the Relations pane (see Figure 6 for the result):
Figure 6. Showing the Orders and Order Details tables with relationship
Observe that Office-QB automatically added the enforced relation between the Orders and Order Details tables. It also inserted the following SQL command text in the SQL pane:
SELECT * FROM Orders INNER JOIN [Order Details] ON [Order Details].OrderID = Orders.OrderIDTo add specific fields to this current query, we click on the field check-boxes in the tables in the diagram of the Relations pane to select them. For example try selecting the ShipVia field of the Orders table and the Quantity field of the Order Details table. You may alternatively select these fields via the drop-down Column combo-box in the Columns pane.
In the Columns pane we can define grouping, sorting as well as criteria. For example, select the field Orders.OrderDate via the combo-box in the Columns pane, and enter the text >'11-10-1997' in the Criteria column for that field-row. That is we want to query all orders with order date greater than November 10, 1997. This will automatically update the SQL command text in the SQL pane, by appending the following WHERE-clause:
WHERE
(Orders.OrderDate > '11-10-1997')
Now execute the query by clicking the blue arrow button in the query builder child window's toolbar. The result of the query will be displayed in the Result tab-view.
Can I edit the content of a query
result?
For most simple queries, the user may run the query and then edit any
unconstraint field directly in the
Result tab-view.
For example, create a new query, drag-drop the Orders table from the
Northwind database into the
Relations pane,
and then execute this new query. All records of the Orders table will be
displayed in the grid of the
Result tab-view.
Try editing the content of the cells under the ShipName field-column
(Note: you may use the DOWN-ARROW key followed by the END key to
continuously edit cells under a field-column).
How do I export the result of a query
to other applications?
In the
Result tab-view
of Office-QB
application, one may use the
Copy and
Paste menu items to transfer portions of data between
Office-QB and
other applications. Moreover, the
Import Result
menu of
Office-QB may be
used to transfer bulk data from one data source to another.
How do I output the result of a query
in HTML format?
Office-QB
application comes with a built-in HTML output feature, which gives the user the
ability to output the result of any query directly in HTML format. Essentially,
in HTML format the output of the query result is imbedded in HTML-table.
Moreover the widths of the columns of the HTML output are controlled by the
widths of the field-columns of the grid of the
Result tab-view.
So that the user may adjust the column-widths of the HTML output by resizing the
grid-columns in the
Result tab-view.
To output a query in HTML format, first define the query and execute it. When
the result of the query is displayed in the
Result tab-view,
adjust the widths of the field-columns in the grid to proper sizes, and from the
Command menu
select the
Output Result as HTML
menu item. This will generate the HTML output and display it in the default web
browser on your system.
Can I mail the query result directly?
In
Office-QB
application, one can directly e-mail the query result via the
Mail Query menu
item of the
Command menu.
This feature is similar to the HTML output, but will display the output in
Outlook Express
New Message
dialog, from where the user may send the e-mail containing the HTML output of
the query result (see Figure 7 for more details):
Figure 7. Showing the e-mail New Message dialog containing the output of the query result