Welcome to the jWorkBook tutorial!

This tutorial will not only get you started with jWorkBook but it will also give you an extremely useful hands-on tutorial for the SQL language. We've selected the most useful statements from the SQL gamut, put them into some practical examples and given you the framework to test them out on your computer.
( The practical examples are based on a database of operator log entries that are stored in either an Oracle or Access database. For more information on this kind of application, you can look at the documentation on the jLogBook package.)

Overview

This tutorial is designed to get you up to speed quickly both with jWorkBook and also with the SQL language. (jWorkBook is an Excel Add-In that connects to an Oracle, SQL Server or Access database. It is available free of charge from the St James Software Web Site.)

Before you start this tutorial, we recommend you get an overview of what the jWorkBook does and what the various components of the jWorkBook are. We recommend you first browse through the on-line help through the section "Overview of the St James Software jWorkBook".

In contrast to the jWorkBook User Manual, this tutorial is not a reference document but rather a learning tool to get you up to speed with some of the facilities you are most likely to use. Once you have worked through these steps, you will probably find the reference section of the On-line Help more useful in getting quickly to the information you need.

Note that the completed Tutorial can be found in a file called jWorkbookTutorial.xls in the c:\program files\St James software\jWorkBook directory.

Setting up the System

Before use the system, you need to install it and make the connection to the database.
Here are the steps you need to carry out:

First Download the package:

You can download it from the St James Software web site at http://staging.sjsoft.com. Here is what you do:

1. First point your browser at the St James website and click on the Register option in the left hand contents menu. You will be taken to the register page where you can enter your name and e-mail details.
2. Now go to the Free Trials Area and click on the jWorkBook entry. The application will be downloaded into your computer.

If you have already received a copy of jWorkBook, you can still register your copy using the same Register entry on the contents menu on the left of the web page.

Then install the Add In

Once you have downloaded the jWorkBook zip file, do the following:

1. Run the set up file (jWorkBook.msi) and it will create a directory called Program Files\St James Software\jWorkBook. This directory will hold all the relevant files such as the Documentation, Tutorials, xla files, sample databases etc.
2. Now start up a blank worksheet and click on the Tools -> Add Ins menu option in Excel and choose the jWorkBookloader as shown in the image on the right. (If you do not see the jWorkBookloader File, then click on Browse and look for it in the c:\program files\St James software\jWorkBook directory.)
   Click on the OK button.

Now activate the jWorkBook Menu

Once you have installed the xla, you should see the "Enable jWorkBook " option in the Tools menu. Activate the jWorkBook menu by clicking on this option. You should do this each time you load Excel.

Now you can activate the jWorkBook Toolbar.

Just right click anywhere in the Excel menu and select the jWorkBook Toolbar.

Starting up and Logging on to the database

Starting up jWorkBook

From the Excel Spreadsheet, you should see the jWorkBook menu item.

Click on the jWorkBook menu item, then click on the Run option. The jWorkBook window should appear.

Logging in to the Database

Now click on the Log In button. The Log-In screen will appear as shown on the right. Logging in is a 3 step process.

1. First select the license for your system. You get this by registering with St James Software. There is no charge for registering and there is no expiry date on the license. You register at the St James Software web site.
2. Choose the Access option as the database type that you want to work with. Leave the "Use jSuite OLE DB Driver" check box unchecked.
3. Enter the log in details for the database as follows: Enter the Username as Admin, leave the Password field blank and for the Filename, use the Browse button and click on the file C:\Program Files\St James Software\jWorkBook\jWorkBooktutorial.mdb. Now check the "Remember database connection next time" check box and click on the Continue Button.

The main jWorkBook Window should now appear. On the banner, there should be a message giving information about how you are logged in. (See below.)

If you have difficulties starting up or logging in, please contact our support services at This e-mail address is being protected from spam bots, you need JavaScript enabled to view it

Creating a SQL Query

Now that we are logged in to a database, let's create our first query:

1. Click on the New Query Button. An empty line will appear in the top grid. This is where our new query will be stored.
2. Enter the name "Basic Select" into the name column in the grid and
3. In the next column, (SQL Query), enter the simple SQL command as follows:
   Select * from LogTable. (This just selects all the data from a table called LogTable. Notice that SQL is not case sensitive so you can write LOGTABLE, logtable, lOGtABLE...whatever.)
4. Now select the Local button in the form and make sure the other options in the form below the top grid are the same as the diagram opposite.
5. Let's run the query.
6. Now click on the Run button. The information from the Logtable should appear in the lower grid as expected.
7. The system automatically adjusts the width of the columns to see all the information.Your lower grid could look something like this.

Note that you can extend the size of the jWorkBook window and also the llower grid by dragging on the bottom right hand corner of the window and the grid. This is useful if you are doing most of your work in the jWorkBook window.

Congratulations, you have logged into a database, written your first SQL command in jWorkbook, and collected the information. In the next section, we will put the information into the spreadsheet.

Saving the results into the spreadsheet

In the section above, we ran a SQL command, which retrieved all the data and placed it in the lower grid. It did not write it into the spreadsheet because we had the "Local" check box checked. Now lets put the information into the spreadsheet. Here's what we do....

1. Click on the small button next to the Output to field. A pop-up window will appear in front of the spreadsheet with the words "Select Destination".
2. Drag the mouse over the area A3 to L20 and click the OK button. The jWorkBook window will reappear looking like the image below.
3. Click on the "Sheet" check box.
   
4. Now click the Run key again. The SQL statement will run as before but this time, it will also write information to the sheet to the location you have specified.
5. Close the jWorkBook pop-up and resize the columns for a more visually appealing view. You could format the message column with word wrap as we have done. The top part of your spreadsheet should look like this:
   
6. Note the following:
   - The column headers have been written in to the first row of the output block because we had the "Headers" option clicked in jWorkBook.
   - If you move the mouse over A3, the memo will show the SQL statement that relates to this data.
   - The information only goes as far as the end of the area we selected in the Output to field (i.e. Row 20) even though this was not the complete data set. If we had checked the "Auto Extend" check box, then all the data from the database would have been returned, even if the number of rows we selected was exceeded.
   - If you click on the cell with the blue text (i.e. A3), then the jWorkBook pop-up opens up showing the command that created the data.

Well done, you have retrieved and stored information in the spreadsheet with a SQL command! Now, in the next section, let's extend our SQL command to show a few things that will be most useful for the future.

Selecting Columns

Let's look at limiting the number of columns returned.

Add another new command using the technique described above as follows:
Select Area, Stream, Message, Priority from logtable
Call the new command "Select Columns" and click on the Run button .

Notice that only the fields we specified in the command have now been returned.

Well done, you have explored the use of the Select clause by limiting the number of columns that are displayed. In the next section, we will look at limiting the data returned from the query by using the Where clause.

WHERE Clause

Now that we have got our first command to function correctly, let's add a few more commands to explore the ability of jWorkBook and SQL.

First, lets explore the Where clause of SQL as follows:

1. Add a new command (by following the steps detailed above, i.e. Click on the jWorkBook menu item, click on the New Query Button).
2. Add the command
   Select * from logtable where Area = 'dig'
   Also give the command a name (Where Clause) and set the options as shown on the right.
3. Click the Run Button.
4. Notice that only log entries from the "dig" area are returned into the lower grid.

You can use this method of filtering the amount of information that is returned to the system. Later in the tutorial, we will use more complex filtering with the AND and OR components and you will also use the LIKE structure with the wildcard % sign.

Logical Combinations

SQL gives you the ability of refining your Where clause by using logical functions like AND, OR and NOT. (You can use these logical expressions in most other parts of the SQL statement too.) Let's try this out...

1. Add a new command (by following the steps detailed above, i.e. Click on the jWorkBook menu item, click on the New Query Button).
2. Add the command as shown below: (We will only look at some of the columns). (To get a new line, press the shift and enter keys at the same time)
3. Also give the command a name (Logical Combinations).
4. Click the Run Button.

Notice that only records that have a priority of 1 or 2 (i.e. <3) AND records that are in the dig area are shown. You can use any of the conventional logical connectors such as AND, OR and NOT. You can also make the Where clause as long as 40 terms in Access and more in the other databases.

Ordering the Results

Once you have collected the information from the database, SQL gives you the opportunity of ordering the information with the ORDER BY Clause. Let's give it a try..

   1. Add a new command (by following the steps detailed above).
   2. Add the command as shown below: (We will only look at some of the columns)

   3. Also give the command a name (Ordering the results).
   4. Click the Run Button.

Notice that the results are ordered by the stream column.

Special Note: Now that we have used the Where clause and also the Order By it is important to remember that you must always put the SQL Select statement in the following order:

Select fields FROM table
Where <where clause>
Order By <order by clause>

If you don't put them in this order, i.e. Select then where then Order By, the SQL parser will complain!

Column Headings

You do not have to use the table column names as column headings, you can use the AS clause in the SQL statement to have your own column headings in the results. The SQL statement below shows how to do this (and uses some of the things we have just learnt.)

1. Add the command as shown below: (We will use a column heading of "Log Date" instead of the Column Name LogDateTime).Notice that we are going to order by Priority and then by LogDateTime. I.e. all the 1st priority logs will appear first ordered by LogDateTime.

2. Also give the command a name (Column Headings).
3. Click the Run Button.

Notice that the column heading is as you specified and that the ordering is as expected.

LIKE Keyword

SQL gives you a set of rich keywords that will enable you to get exactly the information you want from the database. One of the most useful is the LIKE keyword. This is generally used with the % wildcard. Let's look at an example and then discuss what we have achieved.

1. Add a new command (by following the steps detailed above).
2. Add the command as shown below: (This command says to retrieve all records that have any text (the % sign) followed by the word pinch followed by any text (the second % sign).

3. Also give the command a name (LIKE keyword).
4. Choose a destination address using the technique described above and check the "Sheet" check box.
5. Click the Run Button. The top 3 rows of the ouput information should look like this.

With Access, you will actually get 4 rows that meet the criterion. If you run the same SQL statement with Oracle, you will only get one row since Oracle is case sensitive and won't return the other 3 rows because the capitalization doesn't match. If you want to get all the rows irrespective of the capitalization in Oracle, use the following statement. The Upper function first changes the message to upper case and then compares it with the string %PINCH%.

The other keywords or SQL operators that you can use include BETWEEN ... AND (between two values inclusive), IN (in a list of values), and IS NULL (is a null value (i.e. there is nothing in the cell.)

SQL Functions

You can use a rich set of functions inside your SQL commands that (with a little ingenuity) will enable you to retrieve exactly the information you are looking for. Access doesn't use the standard SQL functions but it does enable you to use many of the Visual Basic type functions within the SQL query.

Let's look at a useful function: the visual basic format function. In this example, we want to reformat the logdatetime column into three columns, the day of the week the date itself and the time of the log. (For Oracle users, there are equivalent functions such as the to_char and to_number functions.)

1. Add a new command (by following the steps detailed above).
2. Enter the command as shown below: (This command says to retrieve the logdatetime and format it in three different ways for each of the columns that represent the logdatetime columns.
3. Also give the command a name (DOW Logs).
4. Click the Run Button. You should get the following results in the spreadsheet. Notice that logdatetime date field has been formatted in the three different formats that you specified.

There are many other SQL functions that you can use. (Some of the databases don't support all of them.) Ones that you might find useful include: Length, Upper, Lower, LTrim, Substr etc. You can get more details of these functions by looking at the more detailed documentation on the database that you are using.

IN Keyword

Here is another useful keyword that is worth practicing. This gives you the option to select records that occur in a list of required options. Here we go...

1. Add a new command (by following the steps detailed above).
2. Add the command as shown below: (This command says to retrieve all records that have an area equal to either dig or precip.).
3. Also give the command a name (In keyword)
4. Click the Run Button. In the lower grid, you should only see records that contain either an Area = dig or an Area = precip..

The other keywords or SQL operators that you can use include BETWEEN ... AND (between two values inclusive), LIKE (usually used with the wildcard %), and IS NULL (is a null value (i.e. there is nothing in the cell.)

Using a Cell as a Command Variable

 In this section, we will use a cell in the spreadsheet to further refine our command.

1. In the spreadsheet, go to the cell A1 and type in the text precip. We will use this to modify our command.
2. Now add a new command using the technique described above as shown in the image to the right.
I.e. Select * from LogTable where Area = '[A1]' order by priority
3. Select the area from A3 to N20 as the destination and set the Sheet checkbox.
4. Name the command "Cell variables". Your command should look like the image on the right.
5. Click on the Actual radio button. You should see how the value in the cell precip has been substituted in the Query. I.e.
6. Now close the jWorkBook window and select the "Cell Variables" query from the drop down box on the jWorkBook Toolbar.( If you can't see the toolbar, you can activate it by right clicking anywhere in the Excel menu area and selecting the jWorkBook Toolbar.)
7. Click on the Run button next to the name of the query. Notice that only the rows where the Area is equal to the value 'precip' are returned. Also notice that the rows are ordered by the Priority field.
8. Now in the spreadsheet, change the value of the cell A1 from precip to dig. On the jWorkBook Toolbar, click on the Run button again and you will see that only records that have an area equal to dig are returned.

Excellent, we have run a command where the make up of the command is determined by a value of a cell in the spreadsheet. You can extend this idea as far as you like. Often, you don't want to change the actual SQL command, just an element in the command. If you have other users for example, they could use this facility without having to know what the SQL command is.

In the next section, we will look at multiple commands and how they are can be grouped.

Working with Multiple Statements

jWorkBook enables you to work with multiple statements as a group. You could for example be loading different parts of the spreadsheet with different queries all in a single action. Let's work with this facility in this section.

First let's put some of the statements we have created into a group called LogBook.

1. If the jWorkBook window is not already visible, from the spreadsheet, click on the jWorkBook menu item and click the Run option. The jWorkBook window will appear.
2. Click on the Edit Groups Button in the Group frame. A new section of the form will appear on the right of the form.
3. Click on the New Group Button and enter the name LogBook. You will see the new group name LogBook appear in the list of groups. Select this Group name by clicking on the word LogBook in the upper window.
4. Now go to the SQL statement grid and select the SQL statement called Basic Select, and click on the Add Query button. The name Basic Select will appear in the list of queries.
5. Next click on the query called Select Columns and then the Add Query button. This query will be added to the group.
6. Do the same for the queries Where Clause and Logical Connections. All these queries will now appear in the list of queries for this group as shown in the diagram on the right.
7. Note that we can reorder the commands in the group. In this way, we can use the cell substitution facility so that the result of an earlier query can be used in a later query.

Excellent, we have now created a group of queries called LogBook. Let's hide the detail of the group by clicking on the Edit Groups button in the Group Frame again to toggle this section of the form off.

We can now run all the queries in this group sequentially by selecting the Group from the combo box (in our case LogBook) and clicking on the Run Group button. Let's do it.

You will see the queries sequentially running and placing information (if we have the Sheet checkbox set) in the spreadsheet.

Notice that you can also run the Group from the jWorkBook toolbar. I.e. close the jWorkBook window and select the group name from the rightmost combo, then click on the Run button to the right of the group name.

Using Dates

Particularly in process control, we are concerned not only in what has happened but also in when it happened. Because of this, we often need to be able to manage time and date information. Date and time information is usually held in relational databases in a special date format. There are a number of tools to help you access and manipulate this information. Let's try out an example:

1. Add a new command (by following the steps detailed above).
2. If you Have an Access database: Add the command as shown below: The statement is designed to retrieve all the logs that occurred on the 1st November, 2001. In this example, we have formatted the logdatetime into a string to compare it with the string '01/11/01'.)

Here is an Oracle version which is slightly different: Let's look at what this statement says:
The trunc function says to remove the time part of the date format. I.e. we don't mind what time of the day it occurred, if it was stored on the 1st, we want it.
The to_char function converts the date format to a character string so that we can compare it to the other string "01/11/01".
The DD/MM/RR argument in the to_char function is an instruction to the to_char function to create the text string so that it is in the form DD/MM/RR. Note that RR is a format that only uses 2 digits for the year. Otherwise we would have used YYYY.
3. Now give the command a name (Using Dates)
4. Click the Run Button. In the lower grid, you should only see only the logs that were recorded on the nominated date.

The other keywords or SQL operators that you can use include BETWEEN ... AND (between two values inclusive), LIKE (usually used with the wildcard %), and IS NULL (is a null value (i.e. there is nothing in the cell.)

Current Date

What happens if we are only interested in the records as related to the current date?. For example, what if we only wanted to see the records that occurred in the last 7 days or in the last year? Let's try out an example:

1. Add a new command (by following the steps detailed above).
2. If you have an Access database, add the command as shown below: The statement is designed to retrieve all the logs that occurred in the last 365 days. Essentially, the statement says to collect all the records that have a logdatetime field which is greater than current date and time minus 365 days. (Or, in other words, any record that has a logdatetime later than 365 days ago.

If you have an Oracle System, the command again will look slightly different. I.e. the Oracle sysdate replaces the Access date() function.
3. Now give the command a name (Current Date)
4. Click the Run Button. In the lower grid, you should only see only the logs that were recorded in the last year.

The other keywords or SQL operators that you can use include BETWEEN ... AND (between two values inclusive), LIKE (usually used with the wildcard %), and IS NULL (is a null value (i.e. there is nothing in the cell.)

Date Calculations

With SQL, you can perform calculations with the date field. I.e. Add, subtract, multiply etc. Essentially, it enables you to perform all the functions that make logical sense. Let's use an example to illustrate this kind of calculation.

1. If you have an Access System: Add the query as shown below: The statement is designed to retrieve all the logs that occurred in the 4 days after the 1st of November, 2001. Remember to use the USA format if you are living in the USA. I.e. Instead of 01/11/01, use 11/01/01.

If you have an Oracle System: Add the query below. The statement is designed to retrieve all the logs that occurred in the 4 days after the 1st November, 2001. We use the to_date function instead of the Access datevalue function.
2. Now give the command a name (Date Calculations)
3. Click the Run Button. In the lower grid, you should only see only the logs that were recorded in the nominated periods.

SQL Calculations

With SQL, you can perform calculations with the numerical information, as you would expect. I.e. Add, subtract, multiply etc. You can use the parentheses to force the order in which the calculations are performed. Let's look at a simple example. We will use the emp database which is a database with one record for every employee.

1. First let's look at the emp database so we know what the raw data is. Add a new query and enter the command as
Select * from emp. Now run the command and you will see the information similar to the image below:
2. Now add another new query.
3. Add the query as shown below: The statement is designed to retrieve the user name, the hours worked and calculate the shift hours based on an 8 hour shift..
4. Now give the command a name (SQL Calculations)
5. Click the Run Button. Your information should look something like this

Joining Tables: Looking at more than one table

So what if our data is in more than one table. For example, up till now, we have printed out the area as a cryptic code like dig or precip etc. In another table (the table emp), we have a more readable description for these areas such as Digestion, Precipitation etc. What if I wanted to print out these words instead of the cryptic codes?

SQL can look at more than one database and link them together with what is called a join. A join is where we link one column in one table with a similar column in a second table so that the resulting table appears to hold information on both tables. See if you can go along with this example.

1. Add a new command (by following the steps detailed above).
2. Add the command as shown below:
The statement is designed to get information from two tables (logtable which we have given a shorthand alias called t) and emp which we have called e.
We have joined the tables together with the Where clause which says that each row where the logtable username is equal to the emp username.
3. Now give the command a name (Joining Tables), click on the Sheet checkbox and and set the Output to to the Range A3 to L20.
4. Click the Run Button. In the spreadsheet, you should see something similar to the following.

Note that we now have a column called AreaDescription, which has a readable description from the emp database which corresponds to the area that is designated to the particular user.

COUNT Clause

The COUNT clause is used to get the number of rows that satisfy a given criterion. Here is a very simple example:

1. Add a new command as shown below. The statement is designed to get the number of rows in the table where we have an emergency priority.
2. Click the Run Button. In the lower grid, you will see the number of high priority logs in the table (in this case, 11 records have a Priority of 1.)

GROUP Clause

The count clause is fine, but what if we wanted to see how the emergency alarms were split up across the areas? I.e. we need to group the alarms based on the area column. Here is another example:

1. Add a new command as shown below. The statement is designed to count the number in each of the areas and return a record for each area containing the number of logs for that area.
2. Click the Run Button. In the lower grid, you will see the number of logs for each of the areas.

Note that we have ordered the results with the highest count first descending to the lower counts. (The order by count(*) desc controls this where the key word desc indicates that we want the order descending.)

Having Clause

The group clause is fine, but what if we were not interested in all the groups and really only wanted to see the number of alarms in dig, precip and util? I.e. we need to choose the groups having a certain criteria. This example illustrates the Having clause:

1. Add a new command as shown below. The statement is designed to count the number of priority 1 and 2 logs in each of the areas and return a record for each area specified in the having clause.
2. Click the Run Button. In the lower grid, you will see the number of priority 1 or priority 2 logs for each of the areas in the having clause.

Special Note: Now that we have used the Where clause, the Order By clause and also the Group and Having clause, it is important to remember that you must always put the SQL Select statement in the following order:

Select fields FROM table
Where <where clause>
Group By <group by clause>
Having <having clause>
Order By <order by clause>

If you don't put them in this order then SQL parser will complain!

SQL Sub Selects

With SQL, you can embed SQL statements within SQL statements. With this facility, there is virtually no end to the power you can add to a single SQL statement. This example gives you some idea of what you can do with sub selects.

1. Add a new command as shown below. This statement collects a number of rows from a new table called categorylists. Essentially, it creates a view which contains 3 columns, a category which will be Priority, a code which will be 1,2,3 etc. and a description of the code which will be Emergency etc.

Here are the results of the sub select.

2. We now embed the above statement into a composite statement as if this sub select statement was a table in its own right. We make a join between the two tables t and c and instead of showing the alarm priority as 1,2,3 etc. we can now show it as Emergency, Normal etc.
(Note that we have used the CINT (Coerce to Integer) function to convert the c.code parameter to an integer so that we can compare it with the t.Priority Integer. In Oracle, you would exchange the CINT with the to_number function.)

3. Click the Run Button. If you select the Sheet checkbox, you will see a column called Alarm Priority, which will have the names of the alarms as retrieved from the categorylists table using the separate sub select statement.

Special Note: With sub Selects, it is important to remember:

1. Always put the Sub Select in Parenthesis and
2. Be aware of whether the sub select is going to return a single value or a set of values. The outer part of the SQL statement will either be expecting a single value or, as in the example above, a set of values and of course the expectation of the outer part of the SQL statement must match the embedded Sub Select.

Congratulations, (if you got this far!) you have now completed all the sections in the tutorial!

If you like, you can log out from the database using the Log Out Button, or you can just save the spreadsheet and exit from Excel and the system will just log out automatically.

Remember the completed Tutorial can be found in a file called jWorkbookTutorial.xls in the c:\program files\St James software\jWorkBook directory.

At this stage you can either read further using the on-line help or just dive in and start using the application. In any event, you can always contact us here if you have queries. Above all, have fun with the application!

Our Copyright Policy states: We encourage you to freely copy and distribute this information as often as you please to whomever you please as long as you do not remove our logo from the information.

Click here to view our Price List.