---

News

Products

Support

Pricing

About Us

Cool Feature

Newsletter

Development Schedule

Caribou Spotlight

Cool Feature -

Access Your Data Through Excel

	Prior Features
	Notes on Equipment Costing

1.    Background

I have long made the case against using spreadsheet systems such as Microsoft Excel to manage the business of a logging contractor. In this article, I discuss how you can have your cake and it too – you can have the structure and integrity of a database system, yet still retain the flexibility of Excel.

Practically speaking, most ‘shrink wrap’ database applications will not cover all of your business needs.  The logging business is complex and the industry practices vary widely across region and type of operation making it virtually impossible for a software vendor to make a standard ‘package.’ It is my experience that many loggers have special business rules or reporting needs that cannot be handled by any given software package.  In these cases Excel and other spreadsheet applications still have a major complementary role to play.

The key is integration.  You must be able to draw upon the data in the database application and use it as you see fit in your spreadsheet application.  Any database program worth its salt should provide the ability to copy-and-paste data from the database application to a spreadsheet or at least allow you to export (and then import) your data.  For example, you should be able to copy-and-paste a selection of load tickets from a database application into your own spreadsheet.

One drawback with cutting-and-pasting between a database application and Excel is that the data loses its integrity. That is, if the database is changed after a copy-and-paste, the spreadsheet data are no longer consistent with the source data, and you have to copy-and-paste all over again.

One solution to this problem is a little-used (and somewhat complex) function in Excel that allows you to set up a database ‘query’ and import data directly from an Access or SQL Server database.  A query is a set of instructions to the database that defines a set of records to retrieve. For example, a query might select all load tickets from a given tract or job delivered to a certain mill over a certain date range.  This query can be stored with the worksheet and be refreshed at any time, thereby allowing your worksheet data to remain consistent with your database data. One safety feature with this query approach is that the data are ‘read-only’ meaning the spreadsheet user cannot alter the data, but he or she can always obtain the most recent data from the database.

I’ll briefly outline two ways to take advantage of this technology: an easy way and a hard way.  The hard way is more complicated, but not overly so, but allows you more flexibility in the data that are returned.

2.    Steps - Easy Way

(1)    Make sure you have the ‘Import Data’ menu item available.

In Excel (2003), you navigate to Data | Import External Data | Import Data from the Main Menu.
 

(2) Identify your data source.

 

You need to tell Excel where your database is.

Let’s assume you have a Microsoft Access database. In the ‘Select Data Source’ window, you browse your computer to find your database   In the example above, my database file is: ‘Clearwater.mdb’.  Now click on your file and then click on ‘Open’.

You will then need to provide the database password (if it is password protected).

Warning: some software vendors may not allow you access to the database and not provide the password.
 

Enter the Password.
 

(3)    What data do you want? 

You will then be prompted with a list of tables.  In a database, your data is stored in a series of ‘Tables’, in which each table is like a worksheet with rows and columns.  In general, each table will contain data on a specific topic, such load details, employees, trucks, account codes, invoices, etc.

Let’s say you wanted a tabulation of loads. You would click on the table that contained your load data and click OK.

(4) Tell Excel where to put your data.

You will then be prompted as to where you want you data inserted into your Excel Workbook:

The window above indicates that the data will be written into your worksheet at cell A1. Cell A1 will be the top left hand corner of your data.

(5) Click OK

Voila! You now have all you load data in Excel.  The load data can now be sorted and tabulated making on the fly reporting really simple. Note, that you can have as many database connections as you want.  For example, in one workbook, you could have a worksheet for loads, a worksheet for contractor pay, a worksheet for cruise volumes, etc.

The steps above outlined above may appear unfamiliar, or even daunting, but with a little trial and error you can do this. Just think, you can get a hold of your data when you want without having to hire a programmer!
 

The main drawback to this 'easy approach' is that you import ALL your data from the table into Excel.  For example, in the loads download, you get all loads for all tracts/jobs/blocks all destinations and for all time.  You also get all the load attributes, not those in which you might be particularly interested.  The 'Hard' allows you to further define your query so that you get back only the data that you need.

3.    Steps - The Hard Way

(1)    Select 'New Database Query' instead of Import Data.

In Excel (2003), you navigate to Data | Import External Data | New Data Base Query from the Main Menu.
 

(2)    Select Your Data Source.

You will now be prompted for the type of database to which you want to connect.

You want to select MS Access if you are using an MS Access Database (Duh!).  There is a little checkbox at the bottom of the form that will take you through a wizard if you leave it checked.  In my experience, the wizard is more confusing than helpful.  Moreover, it does not let you build a dynamic query (I'll explain what that is later on).  So make sure to uncheck the checkbox.

Click OK

(3)    Select you Database

You will be prompted to select your database. 

Use this form to browse to your database and click OK.

You will then be prompted for a user name and password. 

In MS Access, the default login name is 'admin' and the password is blank.  For The Logger's Edge you must use the login name admin and enter the actual database password.

After you enter the user name and password,  you will be prompted to select the database again.

I have no earthly idea why you are prompted for the database again, but you are.  Just select the same database as before and hit OK.

(4)    Select your database tables

You will then be prompted with a list of tables in your database.

If you wanted to do a tabulation of your loads, you would select the Loadslips Table (see above) and click Add.  If this is the only table you want, you can then click Close.

(5)    Select your data items from your table(s)

You will now be presented with a somewhat overwhelming screen.  The screen is laid out with your tables on the top and a grid on the bottom.  To select a data item, like block, truck, net weight, or destination, to output to Excel, you need to double-click on the item.

When you double-click on an item, the window will show you a preview of the data in the lower grid.  What you want to do here is select all the items you want to export to Excel.

At this point, we can get back selected data from the loadslips table, but what we really want to do is get back the load data for a defined set of attributes.  In the following example, I will show how to make the query return loads based on the tract/block/job you select over a given date range.

In order to add a filter to your query, you need to click on 'Add Criteria'

This selection will prompt you with a criteria option window.

In order to filter by the block/tract/job, select the item BLOCK_CODE.

In the value field type a question (?) mark.  The question mark is the key:  it will allow use to provide the actual block/tract/job name from a reference in our Excel Workbook.  Once you have filled in the form as above, click 'Add'.

We now want to handle the date range.  Click on 'Add Criteria' again.  This time select 'DATE_OUT' is greater than or equal to 01-01-1900 (or any arbitrary date).  We would like to use the question mark here, but we can't.  We will replace the 01-01-1900 (or any arbitrary date) with a question mark later.

We now want to handle the ending date of our date range.  Click on 'Add Criteria' again.  This time select 'DATE_OUT' is less than or equal to 01-01-1900 (or any arbitrary date).

Click Add to ensure that the date criteria is added. 

If that is the last criteria you want to add (which it is, at least for now!), click Close.

You should now see a screen like below.

If you want to see your criteria, you can select View | Criteria from the main window.

You should have NO data in the lower grid.  Why is that?  Because we have not filled in the question marks.

Now for a little legerdemain.  Click on the SQL button on the main toolbar.

This brings up an ugly set of text.  This text is what the database uses to actually bring back your data. 

What you want to do here is:

1. Replace the snippet (LOADSLIPS.BLOCK_CODE = '?') with (LOADSLIPS.BLOCK_CODE = ?)  -- take out the single quotes
2. Replace (LOADSLIPS.DATE_OUT>={ts '1900-01-01 00:00:00'}) with (LOADSLIPS.DATE_OUT>=?)
3. Replace (LOADSLIPS.DATE_OUT<={ts '1900-01-01 00:00:00'}) with (LOADSLIPS.DATE_OUT<?)

Your resulting SQL window should look as such:

Click OK the save your changes.

Warning:  You may receive a prompt (or set of prompts) to enter a 'Parameter' value.

You can't just hit cancel to ignore them.  You must enter some real or dummy data to get past the prompt.  But, don't worry, the values you enter here are irrelevant to out purposes.  Note that you will be prompted with this parameter box for each criteria you are using in your query.  In our case, we have date fields as two parameters, so we will need to enter a valid date such as 01-01-2000 in order to get past this window.

What is important is that if you click on the SQL button again your question mark (?) changes have been retained.

(6)    Save Your Query

Hurrah!  We are now ready to save your query.

Click on File | Save.

You are now prompted with a 'Save As' entry box.  Select a location and file name that are appropriate.  If you need to edit your query, you will need to remember where you saved your query.  The default file extension for a query is '.dqy'; I recommend that you use this default.

(7)    Return to Excel

Select File | Return Data to Microsoft Office Excel to return to Excel.

(8) Tell Excel where to put your data.

You will then be prompted as to where you want you data inserted into your Excel Workbook:

The window above indicates that the data will be written into your worksheet at cell A5. Cell A5 will be the top left hand corner of your worksheet.

In the screen below, I selected cell A5 as where to put my data.

But wait!  There are field names (column headers), but no data.  The reason for this is that we still have not filled in the question (?) marks for out block/tract/job or date range.

(9) Fill in the question marks.

You next have to tell the query what block/tract/job to select and what date range to use.  To do this, enter a tract/block/job code, a start date and an end date into the workbook.  In the example below, I have entered these items into cells B1, B2 and B3.

I just typed in the row headings in A1, A2, and A3.

You now have to tell the query to use the cells B1, B2 and B3 in your query.  This mapping is performed by right-clicking on the cell A5 (or the top left of your data range).  This action will bring up a pop-up menu.  You want to select 'Parameters' -- note the ? mark.

A parameter is a variable that you provide to the query.  The cool thing about parameters is that you can change them at your leisure and you don't have to mess with the big ugly query stuff again.

In our query, we have three parameters, one for each of the question marks in our query.  In the screen below (which is a repeat from above, you can see we have three question marks.  Each one of these is a parameter.

They are ordered in the order that they appear in the query.  That is, the tract/block/job is parameter 1, the start date is parameter 2 and the end date is parameter 3.

By right clicking in the Excel workbook on cell A5 and selecting 'Parameters' from the popup menu, you will be prompted for the Excel cell references for each parameter.

 

In the screen above, I have set the cell reference for Parameter 1 to be cell B1 -- this is the tract/block/job I entered above.  The checkbox 'Refresh automatically when cell value changes' tells Excel to update your data if you change the tract/block/job.  This a good feature, so I recommend keeping it checked.

You now need to set the values for Parameters 2 and 3.  In my example, I would set Parameter 2 to be cell B2 (the date from) and Parameter 3 (the date to) to be cell B3.

Once you have set each parameter, click OK.

Your load data should magically appear.

If your data do not appear, you can click on the bank (!) button to refresh the worksheet data.

If the data still do not appear, make sure you have entered a valid tract/block/job code and a valid data range.  You might also have to edit your parameter settings  (edits are performed by right-clicking on cell A5 (or the top left corner of your data).

You are now set.  Remember to Save Your Workbook.  All the work that you have done up to now is saved along with the workbook.  You never have to fool with the query mapping again.  You can now just key in a new tract/block/job code and your worksheet will be automatically refreshed with the data currently in your database.

Moreover, if you leave the tract/block/job code the same and come back a week later, and click refresh, all the latest data for that tract/block/job will be loaded into the workbook.  Cool huh?

(10) Adding the Polish.

Now that we have the data access part of the worksheet set up, its now time to add a little spit and polish.  The first order of business is to replace the ugly database field names (see row 5 above) with their real names.

The first thing you want to do is to type in the column headings you want to use.

In your Excel workbook, insert a new row above the database field names. 

Do NOT type over the existing row.

In the excel worksheet above, I have added a new row 5 and typed in the column headings I want to use instead of the MS Access field names.

Your next step is to right-click on cell A5 (still the top left corner of your data range) and select 'Data Range Properties'

This selection will allow you to modify how your data are presented in the workbook:

In this form, you will want to uncheck two checkboxes:

1. Uncheck 'Include field names'.  This setting is the one that controls whether or not the MS Access Data Field names are shown as column headings.  You want to turn this off so that the worksheet will your own headings.
2. Uncheck 'Adjust column width'.  This setting resets the column width every time the query is refreshed.  I recommend that you set the column widths that you want in Excel and then stop the query from resizing the columns all the time.

Once you have unchecked these two items, click OK to save your settings.  Then refresh you data.

The field names are now gone -- leaving just your column headers.  At this point you can apply all the cell formatting you want -- format the numbers, bolding the headings, changing the column widths.  Your formatting will be preserved as long as you leave checked the item 'Preserve cell formatting' in the Data Range Properties form above.

Doesn't that look much better.

But, wouldn't it be really cool if the entry for the Tract/Block/Job could be a pick list instead of a field you had to type in all the time.  I mean, who can remember all of the Tract/Block/Job codes?

In order to replace the text entry cell (cell B1) with a drop down list, you first need to create your list.  I would recommend that you go to The Logger's Edge and open the Tract/Block/Job setup list.  This item in under Setup | Blocks | Setup in the main Logger's Edge menu.  Select and Copy the column with the Tract/Block/Job codes  from the grid.  Now, go back to Excel and Paste that column into a new worksheet in Excel.

In my example above, I have copied my Tract/Block/Job list into Sheet 2 starting at Cell A2.

Once you have created your list, you will need to turn the cell range into an Excel List.  In Excel you can create a List by blocking a range of cells, right-clicking on the selected block, and select Create List from the pop-up menu

 

In my worksheet, I have selected cells A1 to A14 to comprise my list.  I right click in the range and select 'Create List'

You will be prompted with a 'Create List' form.  Make sure that your cell range is shown in the entry box.  If your list has a column heading (like in my example), check the checkbox 'My list has headers.'  But, do not include the header text in your selected range.

Click OK

Your range is now outlined in Blue.  Note that you can add to your list by keying in additional Tracts/Blocks/Jobs in the cell that has the blue *.

The next step involves creating a 'Named Range' for your list.  First select the set of cells that represent your tracts/blocks/jobs.  Next, while the range is selected, go to the textbox in the top left of your worksheet and type in the name that you want to assign to the range.

In my example, above, I have keyed in the name 'MYBLOCKS' for my range name.  Note: this name cannot have spaces, but underscores are OK.  (E.g. I could have named my range 'MY_BLOCKS' instead.  You can now use this range to serve as the basis for a drop down selection list.

So how do turn a cell into a drop-down list?  It is by no means obvious in Excel how to do this.

What you need to do go back to Sheet1 and select the cell that has your Tract/Block/Job.  Then, from Excel's main menu, select Validation under the Data menu item.

 

This selection will bring up a 'Data Validation' options form.

Under the 'Validation criteria' item, you must select List.

In the Source entry box you then key in an '=' sign and the name of your range (from above)

In my example, I named my range 'MYBLOCKS'.  I have entered it in the screen above.

Two important notes here:

1. Do not forget the '=' sign.  It will not work if you omit it.
2. You must type your range name.  You cannot browse or otherwise select it from a list.

You can leave the other entries with their default values  -- you only need to select 'List' and key in your range name.

Click OK.

Voila!  Your cell is no longer a text cell, but is now a drop-down list containing all your tracts/blocks/jobs from your list on Sheet2.

Now, you can select a tract/block/job at will and your sheet will automatically refresh.  Now that is cool!

Whew - that was a lot of work.  But, you can save your workbook and all your settings will be saved.  The next time you come into the workbook all you have to do is select your tract/block/job and your data will be instantly refreshed.  (Note - you might have to enter the database password.)

Now that you know how to apply these tools, you can make as many workbooks as you like utilizing any of your data in The Logger's Edge.

Moral

Integrating your worksheets with your database application allows you to combine the best of both worlds: you can retain the flexibility of Excel and the ability to define your own reports or set up your own analyses, but still retain the data integrity of a database application.

The moral of the story is that you don’t always have to say no to spreadsheets – you just need to know when to say no.