Advanced Sample Queries Using SQL

Summary

The information in this article applies to LIMS version 1.6.0 and beyond and Microsoft Access 2.0/97.

LIMS includes a standard sample query dialog that is used throughout the system wherever samples may be queried. This dialog uses a simple query-by-example interface to locate samples of interest. While this method is both simple and powerful, some queries can not be accomplished using only this technique. This article describes the query by SQL interface added at version 1.6.0.

More Information

Structured Query Language (SQL), commonly pronounced “sequel”, is an industry-standard language used to define, manipulate, and control data in relational databases. Although there are several ANSI standards for SQL, relational database vendors often add their own extensions and enhancements to utilize the features of their product. Microsoft Access includes its own version of SQL. Search the Access online help system for more information on SQL and specific statements such as SELECT.

The LIMS software uses SQL extensively to retrieve and update data in the LIMS database. The system uses SQL SELECT statements to query data. The standard sample query dialog, which uses a query-by-example (QBE) interface, constructs a SELECT statement from the user-specified criteria. The query-by-SQL option of this dialog can be used to append an additional WHERE clause to the one constructed from the QBE fields. While this capability requires knowledge of the underlying database model, it offers query capabilities beyond those available with QBE.

To use the query-by-SQL feature to create advanced sample queries, enter a SELECT statement WHERE clause without the word WHERE. Following are example WHERE clauses. Note that the syntax is not case-sensitive but is shown in mixed case for legibility. SQL keywords are shown in upper case. Descriptions are show in brackets { } below the example.

• SampleID = “9704100915”
  {find a specific sample}
• CustomerID IS NULL
  {samples without a customer}
• CompletedDate IS NOT NULL
  {completed samples}
• AddedUser = “jdoe”
  {samples logged by a specific user}
• UpdatedUser IN (“jdoe”, “bsmith”, “tjones”)
  {samples last updated by any of these users}
• CustomerSampleID LIKE “ABC*”
  {samples with a customer’s sample ID that begins with “ABC”}
• ProjectID IN (SELECT ProjectID FROM Project WHERE Name LIKE “W*”)
  {samples with project names that begin with “W”}
• SampleID IN (SELECT SampleID FROM qrySampleAnalysis WHERE Analyte = “Total Coliforms” AND AnalysisResult >= 500)
  {samples with a Total Coliform result greater than or equal to 500}
• SampleID IN (SELECT SampleID FROM qrySampleAnalysis WHERE Analyte = “Salmo(C)” AND AnalysisResultFormatted = “Pos” AND AnalysisDate BETWEEN #1/1/97# And #1/31/97#)
  {samples with positive Salmo(C) results during January 1997}

These are just a few examples of the types of advanced queries that can be used to locate specific samples. Combine query-by-SQL with the criteria in the QBE fields and almost any sample query is possible. To explore the LIMS database model with a full copy of MS Access, use the Edit Relationships window to view table relationships or open a table in design mode to view the table layout.

CAUTION: Use these features to view the database model only. Do not edit relationships or table layouts.