OK I think I'm having a moment of madness, I cannot figure out why this Insert Into Where Not In is not working, there are records in the tbl_distributor_isa table that are not in the tbl_catergory table but its not seeing them? What am I doing wrong?

INSERT INTO tbl_catergory
(tbl_catergory.scat1, tbl_catergory.scat1_desc, tbl_catergory.scat2, tbl_catergory.scat2_desc, tbl_catergory.distributor)
SELECT [Item Class], [Item Class Desc], [Product Type], [Product Type Desc], distributor
FROM tbl_distributor_isa
WHERE [Item Class] NOT IN
(SELECT scat1
  FROM tbl_catergory) AND[ Product Type]NOT IN
(SELECT scat2
 FROM tbl_catergory)

Any help gratefully received.

Stuart

Try something like this (untested)

INSERT INTO tbl_catergory
(tbl_catergory.scat1, tbl_catergory.scat1_desc, tbl_catergory.scat2, tbl_catergory.scat2_desc, tbl_catergory.distributor)
SELECT [Item Class], [Item Class Desc], [Product Type], [Product Type Desc], distributor
FROM tbl_distributor_isa i left join tbl_catergory c on i.[Item Class] =c.scat1
and i.[Product Type] =c.scat2
where c.scat2 is null

SQLMenace,

Is it correct that the 'AND' is before the 'WHERE' in the code you have given?

Yes, it is, it's part of the join statement