title: Query to count unique sample IDs with number of tests passed/failed
body: <p>I have a linked database and have created a report that summarizes samples tested between a specified date range: product, product group, product description, test description - how many passed/failed that test. All works well except I need to tally the number of samples tested per product group not the number of tests – which is what is currently outputting. I have a query that analyzes the results and allocates a pass/fail designation and these are counted; I've also created a query that counts the number of samples submitted (also between a date range) but can't figure out how to incorporate that into the main query used for the report. The sum function adds up the number of tests but I need the number of samples (i.e. one sample could have 5 different tests and may only pass 2 of them). I am having difficulty trying to put 2 + 2 together! <a href="http://i.stack.imgur.com/fqMCo.png" rel="nofollow">I've attached an image of my report design to help visualize</a></p>
  
  <p><strong>Copy of LabResultsAnalysisCountPass-Fail Query:</strong></p>
  
  <pre><code>SELECT DISTINCTROW [Copy of LabResultsAnalysisWithPass Query].Name, [Copy of    LabResultsAnalysisWithPass Query].Plant, [Copy of LabResultsAnalysisWithPass Query].ProductGroup, [Copy of LabResultsAnalysisWithPass Query].ProductDescription, [Copy of LabResultsAnalysisWithPass Query].TestDescription, [Copy of LabResultsAnalysisWithPass Query].Pass, Count([Copy of LabResultsAnalysisWithPass Query].Pass) AS CountOfPass
  FROM [Copy of Count Lab Samples Query], LabProductTest INNER JOIN [Copy of LabResultsAnalysisWithPass Query] ON LabProductTest.LabProductTestID = [Copy of LabResultsAnalysisWithPass Query].LabProductTestID
  GROUP BY [Copy of LabResultsAnalysisWithPass Query].Name, [Copy of LabResultsAnalysisWithPass Query].Plant, [Copy of LabResultsAnalysisWithPass Query].ProductGroup, [Copy of LabResultsAnalysisWithPass Query].ProductDescription, [Copy of LabResultsAnalysisWithPass Query].TestDescription, [Copy of LabResultsAnalysisWithPass Query].Pass, LabProductTest.Deleted
  HAVING (((LabProductTest.Deleted)=False));
  </code></pre>
  
  <p><strong>Copy of LabResultsAnalysisWithPass Query:</strong></p>
  
  <pre><code>PARAMETERS [Enter Lab Results start date] DateTime;
  SELECT PlantInformation.Name, LabPlantSample.Plant, Product.ProductDescription, ProductGroup.ProductGroupCode, ProductGroup.ProductGroup, LabTestProvincialStandard.TestDescription, LabTestProvincialStandard.Standard,                IIf([ResultTxt]="NEGATIVE","Passed",IIf([ResultTxt]="POSITIVE","Failed",IIf([Standard]="NEGATIVE" And [ResultTxt]&lt;&gt;"NEGATIVE" And [ResultPrefix]&lt;&gt;"&lt;","Failed",IIf([Standard]="NEGATIVE" And (IsNumeric([ResultTxt])&lt;="10" And [ResultPrefix]="&lt;"),"Passed",IIf(CCur([ResultTxt])&lt;=(CCur([Standard])) And (IsNull([ResultPrefix]) Or [ResultPrefix]="&lt;" Or [ResultPrefix]=""),"Passed",IIf(IsNumeric([ResultTxt])&lt;=10 And [ResultPrefix]="&lt;","Passed",IIf(IsNumeric([ResultTxt])&gt;(IsNumeric([Standard])) And (IsNull([ResultPrefix]) Or [ResultPrefix]="&lt;" Or [ResultPrefix]=""),"Failed",IIf(CCur([ResultTxt])&gt;(CCur([Standard])) And (IsNull([ResultPrefix]) Or [ResultPrefix]="&gt;"),"Failed","Failed")))))))) AS Pass, LabProductSample.LabProductSampleID, LabProductTest.LabProductTestID, LabPlantSample.SampleDate
  FROM PlantInformation INNER JOIN (LabPlantSample INNER JOIN (((LabTestProvincialStandard INNER JOIN (Product INNER JOIN ProductGroup ON Product.ProductGroupCode = ProductGroup.ProductGroupCode) ON LabTestProvincialStandard.ProductGroupCode = ProductGroup.ProductGroupCode)     INNER JOIN LabProductTest ON LabTestProvincialStandard.TestDescription = LabProductTest.TestDescription) INNER JOIN LabProductSample ON (LabProductSample.LabProductSampleID = LabProductTest.LabProductSampleID) AND (Product.ProductDescription = LabProductSample.ProductDescription)) ON LabPlantSample.PlantSampleID = LabProductSample.PlantSampleID) ON PlantInformation.Plant = LabPlantSample.Plant
  WHERE (((LabPlantSample.SampleDate)&gt;=[Enter Lab Results start date]) AND ((LabProductTest.Deleted)=False) AND ((LabProductSample.Deleted)=False) AND ((LabPlantSample.Deleted)=False) AND ((PlantInformation.Inactive)=False))
  ORDER BY PlantInformation.Name, ProductGroup.ProductGroupCode, ProductGroup.ProductGroup, LabTestProvincialStandard.TestDescription;
  </code></pre>
  
  <p><strong>Copy of Count Lab Samples Query:</strong></p>
  
  <pre><code>PARAMETERS [Enter Lab Results start date] DateTime, [Enter Lab Results end date] DateTime;
  SELECT PlantInformation.Plant, PlantInformation.Name, Count(LabProductSample.LabProductSampleID) AS CountOfLabProductSampleID, LabPlantSample.SampleDate
  FROM PlantInformation INNER JOIN (LabPlantSample INNER JOIN LabProductSample ON LabPlantSample.PlantSampleID = LabProductSample.PlantSampleID) ON PlantInformation.Plant = LabPlantSample.Plant
  GROUP BY PlantInformation.Plant, PlantInformation.Name, LabPlantSample.SampleDate, LabProductSample.Deleted
  HAVING (((LabPlantSample.SampleDate) Between [Enter Lab Results start date] And [Enter Lab Results end date]) AND ((LabProductSample.Deleted)=False));
  </code></pre>