Using Microsoft Access as an affordable drill-hole database solution

Sn0wdenadminLatest News, Technical Articles

The Problem

Every mining company collects a range of geological and related drill-hole information such as collar, lithology, geophysics, assay and survey data. Naturally these companies require tools for data collection, storage and analysis, however most commercial geological data management software packages are expensive. These packages usually have high setup costs and high ongoing licensing fees which often make them prohibitive to smaller companies. In addition, these commercial data management packages usually require a certain level of technical expertise (such as SQL coding skills) to configure and maintain them.

Failing to recognise the value of their data, many companies use Excel as an alternative. To give an idea of drill-hole data cost, if RC drilling costs roughly $70 to $150 / m, then a database with 30,000 m of RC drilling and assaying would cost in the order of $3 million to replace by redrilling. Despite its value, drill-hole data is often stored in a collection of Excel spreadsheets within a Windows Explorer file structure. Using Excel spreadsheets instead of a database comes with many drawbacks, some of which are listed below:

  • Spreadsheets are not usually secured, backed up or controlled (despite the fact that the data can cost millions to obtain)
  • Data validation takes a lot of time and can be very limited
  • A large number of files are required
  • It is very hard to query the entire collection of spreadsheets
  • It can be difficult to find specific data and export it for reporting or use in other applications
  • File version control issues are common
  • It can be difficult to support or enforce a formal business process
  • It is likely that there will be discrepancies in spreadsheet file structure from hole to hole

These drawbacks make spreadsheets a very poor alternative to a drill-hole database as it does not ensure data integrity. The reasons that people tend to resort to Excel are usually: 1) they know how to use it, 2) they think that they don’t have enough drill-holes or data to warrant a “proper” database and 3) they don’t understand that there are better alternatives out there, which are easy to use and not necessarily expensive. Excel is ultimately a data analysis tool, not a database.

The Solution

Given current cost pressures in the industry there is an increased need for efficiency all round and miners are trying to squeeze as much value out of their existing data. This is hard to achieve when data exists in a multitude of spreadsheets or the database contains un-validated data. Despite this, many still do not want to spend the money on a commercial geological data management software package.

As a solution Snowden can create a customised drill-hole database using Microsoft Access. Microsoft Access comes with the Microsoft Office package so there are no expensive software costs or continual licensing fees. All types of drill-hole data can be managed in a Microsoft Access database including geological, geotechnical, geophysical, geochemical, survey, QAQC, etc…. A drill-hole database designed by Snowden using Microsoft Access can have the following features:

  • Carefully considered database design to suit individual business processes
  • Data entry and editing using carefully considered forms and data dictionaries
  • Data validation rules to ensure data integrity
  • Data viewing using carefully considered forms
  • Data querying for viewing, reporting and exporting
  • Data backup process
  • Data can be exported into modelling software

Anyone who has used any of the other applications in the Microsoft Office suite will find the Microsoft Access interface familiar and easy to use, and, because it is made by Microsoft, there is a huge amount of support, help articles and videos available online for assistance. It is not necessary to send staff on expensive training courses. Users do not need to have knowledge of SQL to perform basic maintenance tasks such as updating code dictionaries and changing validation rules. The Query Wizard tool enables users to query the database without any SQL knowledge at all.

Over time, should a Microsoft Access drill-hole database grow in size, become more complex or need to support more users, the database may be upsized to a Microsoft SQL Server database which will optimise operation. This can be done in a way so that the system maintains the same Microsoft Access front-end with a SQL Server back-end, so that the existing investment in the database (such as queries, reports, forms etc…) is not lost. Snowden can also assist with migrating the data to an “off the shelf” Geological Information Management solution such as acQuire.

In addition, for Australian coal miners using the AusIMM CoalLog standard, Snowden can create a Microsoft Access database using the CoalLog standard data entry sheets and standard code lookup tables for header, drilling, lithology and geotechnical data.

It should go without saying that a robust drill-hole database brings many benefits to a mining company. By making the database more accessible and user-friendly business process can become more streamlined. When all the data is stored in one secure centralised system, data management tasks, analysis and reporting is faster, increasing staff productivity. With in-built data validation, a company can be confident in making decisions based upon this data.

Mining companies spend a lot of money on collecting drill-hole data and they want to get the best value out of it, so it should be looked after properly. Database management should be a priority. By getting their drill-hole database in order they can avoid wasting money on re-drilling, surveying and sampling.

If you would like to get the most out of your drill-hole data or would like to implement a Microsoft Access drill-hole database, please contact us at to arrange a meeting.

Sophia van Praag
Consultant
Snowden

Example form to enter a new hole into the database
Figure 1   Example form to enter a new hole into the database.
Example drill-hole report
Figure 2   Example drill-hole report.

To keep up to date with Snowden please follow us on LinkedIn

Related Articles

  • 22nd July 2015 From Minecraft to crafting Mines What if you didn’t have to travel to get to a mine site? Or what if you could virtually stand in your pit? before any dirt is turned, with the ability to collaborate in real time on its design.
  • 22nd August 2016 Snowden joins acQuire’s Nova Network Partnership program Snowden is pleased to announce that we have joined the Nova Network Partnership program of acQuire. The Nova Network delivers high-quality Geoscientific Information Management (GIM) […]
  • 17th August 2016 Snowden and Microsoft form strategic partnership Snowden is pleased to announce a strategic partnership with Microsoft to drive cloud-first solutions to the mining industry. With an agreed strategy to make cloud technologies relevant to […]