*_Check Field EDD for Duplicate Testsite IDs and Report Types

SELECT DISTINCT [*_sub List Testsites Sampled].WACS_Testsite_ID, [*_sub List Testsites Sampled].WACS_Testsite_Name, [*_sub List Testsites Sampled].Date_Sampled, [*_sub List Testsites Sampled].WACS_Report_Type
FROM [*_sub List Testsites Sampled] INNER JOIN [*_sub List Duplicate Testsite IDs and Report TYpes] ON ([*_sub List Testsites Sampled].WACS_Report_Type = [*_sub List Duplicate Testsite IDs and Report TYpes].WACS_Report_Type) AND ([*_sub List Testsites Sampled].WACS_Testsite_ID = [*_sub List Duplicate Testsite IDs and Report TYpes].WACS_Testsite_ID)
ORDER BY [*_sub List Testsites Sampled].WACS_Testsite_ID, [*_sub List Testsites Sampled].WACS_Testsite_Name, [*_sub List Testsites Sampled].Date_Sampled, [*_sub List Testsites Sampled].WACS_Report_Type;

*_Check Field EDD for Duplicate Testsite IDs

SELECT [*_sub List Testsites Sampled].WACS_Testsite_ID, [*_sub List Testsites Sampled].WACS_Testsite_Name, [*_sub List Testsites Sampled].Date_Sampled, [*_sub List Testsites Sampled].WACS_Report_Type
FROM [*_sub List Testsites Sampled] INNER JOIN [*_sub List Duplicate Testsite IDs] ON [*_sub List Testsites Sampled].WACS_Testsite_ID = [*_sub List Duplicate Testsite IDs].WACS_Testsite_ID
ORDER BY [*_sub List Testsites Sampled].WACS_Testsite_ID, [*_sub List Testsites Sampled].WACS_Testsite_Name, [*_sub List Testsites Sampled].Date_Sampled, [*_sub List Testsites Sampled].WACS_Report_Type;


*_sub List Duplicate Testsite IDs

SELECT [*_sub List Testsites Sampled].WACS_Testsite_ID, Count([*_sub List Testsites Sampled].WACS_Testsite_Name) AS CountOfWACS_Testsite_Name
FROM [*_sub List Testsites Sampled]
GROUP BY [*_sub List Testsites Sampled].WACS_Testsite_ID
HAVING ((([*_sub List Testsites Sampled].WACS_Testsite_ID) Is Not Null) AND ((Count([*_sub List Testsites Sampled].WACS_Testsite_Name))>1))
ORDER BY [*_sub List Testsites Sampled].WACS_Testsite_ID;


*_sub List Duplicate Testsite IDs and Report TYpes

SELECT [*_sub List Testsites Sampled].WACS_Testsite_ID, [*_sub List Testsites Sampled].WACS_Report_Type, Count([*_sub List Testsites Sampled].WACS_Testsite_Name) AS CountOfWACS_Testsite_Name
FROM [*_sub List Testsites Sampled]
GROUP BY [*_sub List Testsites Sampled].WACS_Testsite_ID, [*_sub List Testsites Sampled].WACS_Report_Type
HAVING ((([*_sub List Testsites Sampled].WACS_Testsite_ID) Is Not Null) AND ((Count([*_sub List Testsites Sampled].WACS_Testsite_Name))>1))
ORDER BY [*_sub List Testsites Sampled].WACS_Testsite_ID;


*_sub List Testsites Sampled

SELECT DISTINCT tblHoldSolidWasteEdd.WACS_Testsite_ID, tblHoldSolidWasteEdd.WACS_Testsite_Name, tblHoldSolidWasteEdd.Date_Sampled, tblHoldSolidWasteEdd.WACS_Report_Type
FROM tblHoldSolidWasteEdd
GROUP BY tblHoldSolidWasteEdd.WACS_Testsite_ID, tblHoldSolidWasteEdd.WACS_Testsite_Name, tblHoldSolidWasteEdd.Date_Sampled, tblHoldSolidWasteEdd.WACS_Report_Type
ORDER BY tblHoldSolidWasteEdd.WACS_Testsite_ID, tblHoldSolidWasteEdd.WACS_Testsite_Name, tblHoldSolidWasteEdd.Date_Sampled;


*_Check Field Parameters Reported

TRANSFORM Max([tblHoldSolidWasteEdd]![Result] & " " & [tblHoldSolidWasteEdd]![Field_Parameter_Qualifier_Code]) AS Expr1
SELECT tblHoldSolidWasteEdd.WACS_Testsite_ID, tblHoldSolidWasteEdd.WACS_Testsite_Name, tblHoldSolidWasteEdd.Date_Sampled, tblHoldSolidWasteEdd.WACS_Report_Type
FROM tblHoldSolidWasteEdd
GROUP BY tblHoldSolidWasteEdd.WACS_Testsite_ID, tblHoldSolidWasteEdd.WACS_Testsite_Name, tblHoldSolidWasteEdd.Date_Sampled, tblHoldSolidWasteEdd.WACS_Report_Type
ORDER BY tblHoldSolidWasteEdd.WACS_Testsite_Name, tblHoldSolidWasteEdd.Date_Sampled
PIVOT tblHoldSolidWasteEdd.Field_Parameter_Name;


*_CHeck Lab Analytes Reported

TRANSFORM Min([tblHoldDepEdd]![Result] & " " & [tblHoldDepEdd]![LabQualifiers]) AS Expr1
SELECT tblHoldDepEdd.AnalyteName, tblHoldDepEdd.ResultUnits
FROM tblHoldDepEdd
WHERE (((tblHoldDepEdd.AnalyteType) Like "TRG") AND ((tblHoldDepEdd.QCType) Like "N"))
GROUP BY tblHoldDepEdd.AnalyteName, tblHoldDepEdd.ResultUnits, tblHoldDepEdd.AnalyteType, tblHoldDepEdd.QCType
PIVOT tblHoldDepEdd.ClientSampleId;

*_Check Lab(s) reporting data in the lab EDD

SELECT DISTINCT tblHoldDepEdd.LabID AS DOHE, tblSvlLabId.Description
FROM tblHoldDepEdd LEFT JOIN tblSvlLabId ON tblHoldDepEdd.LabID = tblSvlLabId.LabID;

*_Generate Lab EDD without Field Parameters

SELECT tblHoldDepEdd.ClientSampleId, tblHoldDepEdd.LabAnalysisRefMethodID, tblHoldDepEdd.LabSampleId, tblHoldDepEdd.LabID, tblHoldDepEdd.ClientAnalyteID, tblHoldDepEdd.AnalyteName, tblHoldDepEdd.Result, "" AS Error, tblHoldDepEdd.ResultUnits, tblHoldDepEdd.LabQualifiers, tblHoldDepEdd.DetectionLimit, tblHoldDepEdd.AnalyteType, tblHoldDepEdd.Dilution, tblHoldDepEdd.PercentMoisture, tblHoldDepEdd.PercentRecovery, tblHoldDepEdd.RelativePercentDifference, tblHoldDepEdd.ReportingLimit, tblHoldDepEdd.ProjectNumber, tblHoldDepEdd.ProjectName, tblHoldDepEdd.DateCollected, tblHoldDepEdd.MatrixID, tblHoldDepEdd.QCType, tblHoldDepEdd.ShippingBatchID, tblHoldDepEdd.Temperature, tblHoldDepEdd.PreparationType, tblHoldDepEdd.AnalysisType, tblHoldDepEdd.ReportableResult, tblHoldDepEdd.DatePrepared, tblHoldDepEdd.DateAnalyzed, tblHoldDepEdd.TotalOrDissolved, tblHoldDepEdd.PrepBatchID, tblHoldDepEdd.MethodBatchID, tblHoldDepEdd.PreservationIntact, tblHoldDepEdd.QCSpikeAdded, tblHoldDepEdd.ResultComments, tblHoldDepEdd.LabReportingBatchID
FROM tblHoldDepEdd
WHERE (((tblHoldDepEdd.ClientAnalyteID) Not Like "2030" And (tblHoldDepEdd.ClientAnalyteID) Not Like "1880" And (tblHoldDepEdd.ClientAnalyteID) Not Like "1900" And (tblHoldDepEdd.ClientAnalyteID) Not Like "FL-PHYS-007" And (tblHoldDepEdd.ClientAnalyteID) Not Like "2055" And (tblHoldDepEdd.ClientAnalyteID) Not Like "EPA E1647296" And (tblHoldDepEdd.ClientAnalyteID) Not Like "1610" And (tblHoldDepEdd.ClientAnalyteID) Not Like "*GW*"));


*_Solid Waste (field) EDD with Field Parameters

SELECT DISTINCT tblWacs_Svl_SolidWaste_Testsite_Inventory.WACS_Testsite_ID, tblWacs_Svl_SolidWaste_Testsite_Inventory.WACS_Testsite_Name, tblWacs_Svl_SolidWaste_Testsite_Inventory.WACS_Facility_ID, tblWacs_Svl_SolidWaste_Testsite_Inventory.WACS_Facility_Name, "E" AS Sample_Type, "W" AS Matrix_ID, tblHoldDepEdd.LabAnalysisRefMethodID AS Field_Measurement_Method, tblHoldDepEdd.AnalyteName AS Field_Parameter_NameAnalyteName, tblHoldDepEdd.Result, tblHoldDepEdd.ResultUnits AS Result_Units, "D" AS Field_Parameter_Qualifier_Code, tblHoldDepEdd.ResultComments AS Field_Comments, "" AS Sampling_Personnel, "" AS Collection_Agency, tblHoldDepEdd.DateCollected AS Date_Sampled, tblHoldDepEdd.ShippingBatchID AS Shipping_Batch_ID, "Y" AS Well_Purged_Flag, "SEMGW" AS WACS_Report_Type, tblHoldDepEdd.ClientAnalyteID
FROM tblWacs_Svl_SolidWaste_Testsite_Inventory INNER JOIN tblHoldDepEdd ON (tblHoldDepEdd.ProjectNumber = tblWacs_Svl_SolidWaste_Testsite_Inventory.WACS_Facility_ID) AND (tblWacs_Svl_SolidWaste_Testsite_Inventory.WACS_Testsite_Name = tblHoldDepEdd.ClientSampleId)
WHERE (((tblHoldDepEdd.ClientAnalyteID) Like "2030" Or (tblHoldDepEdd.ClientAnalyteID) Like "1880" Or (tblHoldDepEdd.ClientAnalyteID) Like "1900" Or (tblHoldDepEdd.ClientAnalyteID) Like "FL-PHYS-007" Or (tblHoldDepEdd.ClientAnalyteID) Like "2055" Or (tblHoldDepEdd.ClientAnalyteID) Like "EPA E1647296" Or (tblHoldDepEdd.ClientAnalyteID) Like "1610") AND ((tblHoldDepEdd.QCType)="N") AND ((tblHoldDepEdd.AnalyteType)="TRG"));


*_Solid Waste (field) EDD without Field Parameters

SELECT DISTINCT tblWacs_Svl_SolidWaste_Testsite_Inventory.WACS_Testsite_ID, tblWacs_Svl_SolidWaste_Testsite_Inventory.WACS_Testsite_Name, tblWacs_Svl_SolidWaste_Testsite_Inventory.WACS_Facility_ID, tblWacs_Svl_SolidWaste_Testsite_Inventory.WACS_Facility_Name, "E" AS Sample_Type, "W" AS Matrix_ID, "" AS Field_Measurement_Method, "" AS Field_Parameter_NameAnalyteName, "" AS Result, "" AS Result_Units, "" AS Field_Parameter_Qualifier_Code, "" AS Field_Comments, "" AS Sampling_Personnel, "" AS Collection_Agency, tblHoldDepEdd.DateCollected AS Date_Sampled, tblHoldDepEdd.ShippingBatchID AS Shipping_Batch_ID, "Y" AS Well_Purged_Flag, "SEMGW" AS WACS_Report_Type
FROM tblWacs_Svl_SolidWaste_Testsite_Inventory INNER JOIN tblHoldDepEdd ON (tblHoldDepEdd.ProjectNumber = tblWacs_Svl_SolidWaste_Testsite_Inventory.WACS_Facility_ID) AND (tblWacs_Svl_SolidWaste_Testsite_Inventory.WACS_Testsite_Name = tblHoldDepEdd.ClientSampleId)
WHERE (((tblWacs_Svl_SolidWaste_Testsite_Inventory.WACS_Testsite_Name) Not Like "*blank*") AND ((tblHoldDepEdd.ClientAnalyteID) Is Not Null) AND ((tblHoldDepEdd.QCType)="N") AND ((tblHoldDepEdd.AnalyteType)="TRG"));


*__WACS_Compare_Testsite_Names

SELECT [*__WACS_Field EDD].Field_EDD_Testsite_Name AS [Field EDD Testsite Name], [*__WACS_Field EDD].Field_EDD_Testsite_ID, [*__WACS_Testsite_Inv].Inventory_Testsite_Name, [*__WACS_Testsite_Inv_1].Inventory_Testsite_ID AS [Suggested WACS_TestSite_ID], [*__WACS_Field EDD].Field_EDD_Testsite_Name
FROM ([*__WACS_Field EDD] LEFT JOIN [*__WACS_Testsite_Inv] ON [*__WACS_Field EDD].Field_EDD_Testsite_ID = [*__WACS_Testsite_Inv].Inventory_Testsite_ID) INNER JOIN [*__WACS_Testsite_Inv] AS [*__WACS_Testsite_Inv_1] ON ([*__WACS_Field EDD].Field_EDD_Testsite_Name = [*__WACS_Testsite_Inv_1].Inventory_Testsite_Name) AND ([*__WACS_Field EDD].Field_EDD_Facility_ID = [*__WACS_Testsite_Inv_1].Inventory_Facility_ID)
WHERE (((StrComp([*__WACS_Field EDD]![Field_EDD_Testsite_Name],[*__WACS_Testsite_Inv]![Inventory_Testsite_Name],1)) Not Like "0"));



*__WACS_Field EDD

SELECT DISTINCT tblHoldSolidWasteEdd.WACS_Testsite_Name AS Field_EDD_Testsite_Name, CLng([tblHoldSolidWasteEdd]![WACS_Testsite_ID]) AS Field_EDD_Testsite_ID, [tblHoldSolidWasteEdd]![WACS_Facility_ID] AS Field_EDD_Facility_ID
FROM tblHoldSolidWasteEdd
WHERE (((tblHoldSolidWasteEdd.WACS_Testsite_Name) Not Like "*Blank*"))
ORDER BY tblHoldSolidWasteEdd.WACS_Testsite_Name;


*__WACS_Testsite_Inv

SELECT DISTINCT tblWacs_Svl_SolidWaste_Testsite_Inventory.WACS_Testsite_Name AS Inventory_Testsite_Name, CLng([tblWacs_Svl_SolidWaste_Testsite_Inventory]![WACS_Testsite_ID]) AS Inventory_Testsite_ID, tblWacs_Svl_SolidWaste_Testsite_Inventory.WACS_Facility_ID AS Inventory_Facility_ID
FROM tblWacs_Svl_SolidWaste_Testsite_Inventory
WHERE (((tblWacs_Svl_SolidWaste_Testsite_Inventory.[Facility Type]) Like "3" Or (tblWacs_Svl_SolidWaste_Testsite_Inventory.[Facility Type]) Like "7"));