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 make a QB-project?
· 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
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
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
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
that sometimes the links of this
Recent Projects list-box need to be refreshed via the
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:
(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
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