Query Data with the LIMS Data Query Workbook

Summary

The information in this article applies to LIMS 2.x and newer.

Exporting data from LIMS to Excel using LIMS’ Excel Interface is a powerful means to gather and analyze LIMS data. You can use the Excel Interface to export data from any LIMS report to an Excel template. However, when you cannot retrieve the necessary data using an existing LIMS report, a more general-purpose tool is useful.

Using the LIMS Data Query workbook, you can insert the results of any Structured Query Language (SQL) Select statement directly into Excel. This article shows how you can use the LIMS Data Query workbook for ad hoc access to LIMS data.

More Information

You can access the data from any LIMS report in Excel by exporting the report’s data to an Excel template. In most cases, you use a report-specific template designed to receive the report’s data. For quick access to any report’s data simply export the report to the LIMS Export Template, which is a minimal export template containing only the required LIMSData worksheet with empty BeforeTransferFromLIMS and AfterTransferFromLIMS macros. If you do not already have a copy of the LIMS Export Template you can download it here. Copy the template to your Excel export templates folder which you will find listed in LIMS’ Workstation Configuration screen on the Admin menu.

There is a good chance you will encounter a situation where you cannot easily retrieve the necessary data by exporting an existing LIMS report. For example, one LIMS user needed a list of customers who had no sampling activity during a specific date range. For these cases, the LIMS Data Query Excel workbook is a good solution. The LIMS Data Query workbook is a general-purpose tool used to retrieve any LIMS data using an SQL Select statement. Note that constructing a valid SQL Select statement requires knowledge of both SQL and the LIMS data model. To learn more about the SQL Select statement search Microsoft Access help for the SELECT Statement topic. You can explore the LIMS data model by viewing the design of the system’s data tables in the Access development environment. You can also contact LIM’s technical support for assistance constructing an appropriate SQL statement.

After downloading file LIMS Data Query.xls from the File Library, follow the steps below to query data using the workbook:

1. Start LIMS.

2. Open LIMS Data Query.xls in Excel.

3. Enter a valid SQL SELECT statement on the Query worksheet shown below.

4. Click the [Query] button.

5. View the results of the query on the LIMSData worksheet.

Simply enter an appropriate SQL Select statement to query any data for your specific task. Recall that our earlier example required a list of customers with no sampling activity during a specific date range. To gather the required data, we can begin with the following SQL statement:

SELECT Customer.Name, Count(SampleID) AS SamplesLogged
FROM Customer LEFT JOIN Sample ON 
Customer.CustomerID = Sample.CustomerID
WHERE Customer.Inactive = FALSE AND
(Sample.AddedDate IS NULL OR
Sample.AddedDate BETWEEN 
#1/1/2003 00:00:00# AND #12/31/2003 23:59:59#)
GROUP BY Customer.Name;

For all active customers in the LIMS, the SQL statement above will generate a count of the number of samples logged between the date range specified. A sample’s AddedDate identifies the date and time the sample was logged. The AddedDate BETWEEN range uses 00:00:00 (i.e. midnight) for the start date time and 23:59:59 (i.e. one second before midnight) for the end date time to include any samples logged on or between the start date and end date. Because we really want just a list of active customers who have not been sampled at least once during the specified date range, we simply change the above SQL statement to:

SELECT Customer.Name, Count(SampleID) AS SamplesLogged
FROM Customer LEFT JOIN Sample ON 
Customer.CustomerID = Sample.CustomerID
WHERE Customer.Inactive = FALSE AND
(Sample.AddedDate IS NULL OR
Sample.AddedDate BETWEEN 
#1/1/2003 00:00:00# AND #12/31/2003 23:59:59#)
GROUP BY Customer.Name
HAVING Count(SampleID) = 0;

Note that only the last two lines have changed by inserting the “HAVING Count(SampleID) = 0” clause. Although the HAVING clause does not have to begin on a separate line, it is an SQL readability standard to do so. Use Alt+Enter in the Excel cell to insert a new line. If we want a list of customers who were sampled at least once during the specified date range, we would simply change the HAVING clause to “HAVING Count(SampleID) > 0.” Additional criteria can be added to the SQL statement’s WHERE clause to further refine the data retrieved.

Even when a LIMS report does contain the necessary data, you may still find the LIMS Data Query workbook simpler since you can execute a query and retrieve the results using a single button click. You can save different SQL statements or variations in distinct workbooks with appropriate names to indicate their purpose and then rerun the query at any time.