Stop/SKIP Appending Query if Duplicate Found

[ishaqani]

I want to stop/skip appending of those records who match in main data table "MCIGMMS" on the basis [PORTCD] [IGMNO] fields in "PCIGMMS2". When Match found Msg Box appear and appending skip or stop of those records.

Can any one help me or give me code.

Regards.

SQL CODE:

INSERT INTO MCIGMMS ( PORTCD, IGMNO, IGMYY, IGMDAT, SHPANO, VSLFLT, VOYAGE, ARVDAT, SLOCCD, CNTCOD, LSTPRT, CAPTAN, SHPCMP, TOTIDX, BOTTOM, BEGIDX )
SELECT PCIGMMS2.PORTCD, PCIGMMS2.IGMNO, PCIGMMS2.IGMCY, PCIGMMS2.IGMDAT, PCIGMMS2.SHPANO, PCIGMMS2.VSLFLT, PCIGMMS2.VOYAGE, PCIGMMS2.ARVDAT, PCIGMMS2.SLOCCD, PCIGMMS2.CNTCOD, PCIGMMS2.LSTPRT, PCIGMMS2.CAPTAN, PCIGMMS2.SHPCMP, PCIGMMS2.TOTIDX, PCIGMMS2.SBOTTOM, PCIGMMS2.BEGIDX
FROM MCIGMMS RIGHT JOIN PCIGMMS2 ON MCIGMMS.IGMNO = PCIGMMS2.IGMNO;

 

[boblarson]

If you want to not allow duplicates and bring up a message if the record already exists, then using the SQL statement you have listed will not work as that will insert all records that match the SELECT part of the statement.

You would need to assign that to a recordset and move through each and use either a DLookup, or another SQL statement to select a single record based on a key field and compare the result between the two and then move to the next record.

This makes me think that your database, and the way you are updating the data, isn't necessarily designed the best way.