SQL Query remove duplicate records?

[bg3075]

A previous co-worker created an Access database, which is now creating duplicate rows of records for only one of the values selected. I am trying to incorporate Method 1 from here, in order to remove the duplicate records from within a SQL query (below), but am not sure how to include the method into the query. Please help.

strSQL = "SELECT IIf(Right([qsptTemp]![LOC_CODE],2)='-F',Left([qsptTemp]![LOC_CODE],Len([qsptTemp]![LOC_CODE])-2),[qsptTemp]![LOC_CODE]) AS [Well ID], IIf([qsptTemp]![AQUIFER_CODE] Like 'PER*',""Perched"",""Major"") AS Aquifer, qsptTemp.SAMPLE_ID AS [Sample ID], DateSerial(Left([qsptTemp]![SAMPLE_DATE],4),Mid([qsptTemp]![SAMPLE_DATE],5,2),Right([qsptTemp]![SAMPLE_DATE],2)) AS [Sample Date], " & _
            "IIf([qsptTemp]![LOC_CODE] Like '*-F', IIf([qsptTemp]![SAMPLE_TYPE]='N',""F"",""F"" & [qsptTemp]![SAMPLE_TYPE]),[qsptTemp]![SAMPLE_TYPE]) AS [Sample Type], qsptTemp.ANALYSIS_METHOD AS [Analysis Method], qsptTemp.ANALYTE_TYPE_CODE AS [Analyte Group], qsptTemp.RUN_ID AS [Run ID], qsptTemp.CAS_NUMBER AS [CAS #], ProperCase([qsptTemp]![IUPAC_NAME],1) AS Analyte, " & _
            "qsptTemp.IRPIMS_CL_CODE AS IRPIMS, IIf([qsptTemp]![VALUE_UOM]='mg/l',[GWPS]![GWPS_mg/L],[GWPS]![GWPS_ug/L]) AS [GWPS], [qsptTemp]![MEASURED_VALUE] AS [Measured Value], [qsptTemp]![DETECTION_LIMIT] AS [Detection Limit], qsptTemp.VALUE_UOM as [UOM], qsptTemp.RESULT_CODE AS [Result Code], " & _
            "qsptTemp.LAB_QUALIFIER_CODE AS [Lab Qualifier], qsptTemp.REVIEW_QUAL_CODE AS [PTX Qualifier], qsptTemp.VALIDATION_CODE AS [Validation Code], IIf([qsptTemp]![VERIFIED_DATE] Is Null,'',DateSerial(Left([qsptTemp]![VERIFIED_DATE],4),Mid([qsptTemp]![VERIFIED_DATE],5,2),Right([qsptTemp]![VERIFIED_DATE],2))) AS [Verified Date], qsptTemp.VERIFIED_COMMENTS AS [Verified Comments], " & _
            "qsptTemp.VERIFIED_CODE AS [Verified Code], qsptTemp.DILUTION_FACTOR AS [Dilution Factor], qsptTemp.RAD_ERR_CNT AS [Rad Error Count], qsptTemp.FIELD_TURBIDITY AS Turbidity, qsptTemp.DESCRIPT AS [Sample Description], qsptTemp.CASE_NARRATIVE AS [Case Narrative] " & _
            "FROM (qsptTemp LEFT JOIN GWPS ON qsptTemp.IRPIMS_CL_CODE = GWPS.IRPIMS_CL_CODE) " & _
            "ORDER BY IIf([qsptTemp]![AQUIFER_CODE] Like 'PER*',""Perched"",""Major""), IIf(Right([qsptTemp]![LOC_CODE],2)='-F',Left([qsptTemp]![LOC_CODE],Len([qsptTemp]![LOC_CODE])-2),[qsptTemp]![LOC_CODE]), qsptTemp.SAMPLE_DATE, qsptTemp.ANALYTE_TYPE_CODE, qsptTemp.IRPIMS_CL_CODE, qsptTemp.SAMPLE_ID, qsptTemp.RUN_ID, qsptTemp.VERIFIED_DATE;"

 

[GinaWhipp]

This might help...

Posted in the NNTP newsgroup by John P. Spencer

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you expect.

You can try a query that looks like the following where PK is the primary key field and field1 and field2 are the fields that determine if there are duplicate records.

DELETE *
FROM

WHERE [PK] in (SELECT Min([PK])
FROM

GROUP BY [Field1], [Field2]
HAVING Count([PK]) > 1)

IF there are multiple duplicates, then you would need to run this query multiple times.

 

[bg3075]

This might help...

Posted in the NNTP newsgroup by John P. Spencer

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you expect.

You can try a query that looks like the following where PK is the primary key field and field1 and field2 are the fields that determine if there are duplicate records.

DELETE *
FROM

WHERE [PK] in (SELECT Min([PK])
FROM

GROUP BY [Field1], [Field2]
HAVING Count([PK]) > 1)

IF there are multiple duplicates, then you would need to run this query multiple times.

Thank you, Gina.

But, I don't know what is the primary key in this case, because it is querying from an Oracle database table. It is a "Pass-thru Query", so I also do not know what to replace with the table name in the "FROM

" portion of the script, as it is only creating a temporary table in the pass-thru query.

 

[GinaWhipp]

Well, what field contains the duplicate data?