Finding Samples Missing Analysis Costs Using SQL

Summary

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

LIMS assigns costs to a sample’s analyses at the time the sample is completed. Analyses with no assigned costs can result in completed samples with no analysis cost. Use the techniques in this article to locate samples with missing costs.

More Information

LIMS marks a sample completed when either all analysis results are completed in the results entry screen or all incomplete analyses are deleted using the sample login screen’s edit mode. When a sample is completed through either of these methods, the sample’s completion date is set and costs are assigned to the sample’s analyses. For each sample analysis, a cost is assigned by looking up either the customer’s cost for this analysis or the default analysis cost. If the sample has no customer or the customer has no assigned cost for the analysis, the default analysis cost is used. If no cost exists for either of these cases, the completed sample’s analysis is not assigned a cost.

While it is possible to locate analyses with no costs by generating an invoice detail report and performing a visual inspection of the report’s data, a more practical approach is to use an SQL expression in a sample query. Use the user-defined report (UDR) generate screen to query the samples, then either display the samples using the sample selection screen or create a simple UDR to display sample characteristics. Use the query-by-SQL option to enter one of the following SQL expressions along with any other query criteria in the query-by-example fields to locate samples missing analysis costs.

To find completed samples where all analyses have no cost:

     SampleID IN (SELECT Sample.SampleID
     FROM Sample INNER JOIN SampleAnalysis
          ON Sample.SampleID = SampleAnalysis.SampleID
     WHERE Sample.CompletedDate Is Not Null
     GROUP BY Sample.SampleID
     HAVING Sum(IIf(IsNull([Cost]),0,1))=0)

To find completed samples with at least one analysis without a cost (including samples where all analyses have no cost):

     SampleID IN (SELECT Sample.SampleID
     FROM Sample INNER JOIN SampleAnalysis 
	 ON Sample.SampleID = SampleAnalysis.SampleID
     WHERE Sample.CompletedDate Is Not Null
     GROUP BY Sample.SampleID
     HAVING Sum(IIf(IsNull([Cost]),0,1))<Count([AnalysisID]))

To find completed samples with at least one analysis with a cost and at least one analysis without a cost:

     SampleID IN (SELECT Sample.SampleID
     FROM Sample INNER JOIN SampleAnalysis
          ON Sample.SampleID = SampleAnalysis.SampleID
     WHERE Sample.CompletedDate Is Not Null
     GROUP BY Sample.SampleID
     HAVING (Sum(IIf(IsNull([Cost]),0,1))<Count([AnalysisID]) AND
     Sum(IIf(IsNull([Cost]),0,1))>0))

TIP: Save these and other frequently used or complex SQL expressions in a text file and use the Windows clipboard to copy and paste expressions into the SQL field of the query dialog.

To correct a sample with a missing cost, first assign the missing analysis cost(s) to either the sample’s customer or the analyses. Then simulate sample completion by editing one of the sample’s analysis results and setting it to the identical value. The system will detect that an analysis record has been “changed” and recomplete the sample. Since the analysis’ result remains unchanged, no audit trail event is triggered. Note that this will change the sample’s completion date to the current date.