Append without duplicating with condition on many fields

[anil]

hi all
I am using this query in VBA to append records from tblImport into
tblResults:

STR="INSERT INTO tblResults ( ResultRemarks, LabsampleNO, SampleID,
ResultDate, Comparator, ResultValue, ParameterID )
SELECT tblImport.[BATCH NO], tblImport.[LAB SAMPLE NO], tblImport.[REF
NO], tblImport.[RECEIVE DATE], tblImport.QUALIFIER, tblImport.RESULT,
tblParameter.ParameterID
FROM tblImport INNER JOIN tblParameter ON tblImport.[PA NAME]
=tblParameter. ParameterName WHERE (((tblImport.[REF NO]) Is Not Null))
ORDER BY tblParameter.ParameterID;"

In this ResultID is generated when I append the records.It means that
labSampleNo, SampleID, ResultDate and parameterID can be
multiple.Therefore I want that before appending records it should check
that no duplicates records are added for combination of all the above
parameters i.e like

if not exists((tblimport.[LAb Sample NO] = tblResults.LabSampleNo) and
tblimport.[REF NO] = tblResults.SampleID) and (tblimport.[Recievedate]
= tblResults.ResultDate) and (tblimport.[PA NAME] =
tblResults.parameterID) then

Docmd.run STR
ELSE
msgbox"Records already exists"
end if

I have tried using above but did not work,can some one please help me
on that or refer me to same problem

thanks
anil

 

[Stefan Hoffmann]

hi Anil,

Therefore I want that before appending records it should check
that no duplicates records are added for combination of all the above
parameters i.e like

if not exists((tblimport.[LAb Sample NO] = tblResults.LabSampleNo) and
tblimport.[REF NO] = tblResults.SampleID) and (tblimport.[Recievedate]
= tblResults.ResultDate) and (tblimport.[PA NAME] =
tblResults.parameterID) then

That's the right way:

SELECT *
FROM tblImport I
WHERE NOT Exists(
SELECT *
FROM tblResults R
WHERE I.[LAb Sample NO]= R.LabSampleNo AND ...
)

mfG
--> stefan <--

 

[anil]

Thanks Stefan
I tried same way except using I and R,may be that was the mistake.
Thanks a lot
Anil

hi Anil,

Therefore I want that before appending records it should check
that no duplicates records are added for combination of all the above
parameters i.e like

if not exists((tblimport.[LAb Sample NO] = tblResults.LabSampleNo) and
tblimport.[REF NO] = tblResults.SampleID) and (tblimport.[Recievedate]
= tblResults.ResultDate) and (tblimport.[PA NAME] =
tblResults.parameterID) then

That's the right way:

SELECT *
FROM tblImport I
WHERE NOT Exists(
SELECT *
FROM tblResults R
WHERE I.[LAb Sample NO]= R.LabSampleNo AND ...
)

mfG
--> stefan <--