How do I Insert Values w/ Not Exists sub select?

[toddbailey]

Hi All

I'm trying to execute this sql stmt via vb/a code

Insert into tblRate
(PN,LevelID,CategoryID,SubcategoryID,Hrs,LaborRate ,LaborCost,FringeRate,FringeCost,MaterialOther,[Total Direct Costs])
select 'default',1,3,1,0,0,0,0,0,0,0 from tblRate where
not exists (select * from tblRate where PN = 'default' and LevelID = 1 and CategoryID = 3 and SubCategoryID = 1)



basically I want to seed a table with hardvalues if the keys (Pn, Levelid, categoryid and subcategoryid) do not exists

this is a small part of a larger do until loop, where levelid, categoryid and subcategoryid are stored in different tables and extracted 1 row at a time to get all unique possible combinations,

so PN will always = 'default', but level, cat and sub cat can be any integer value.

I want to store any new id entries found in the master tables into the rate table, but only if they do not already exist.

The sql stmt works on my test sql server box, but when I try to run it as part of a vb/a code module where the code eventually calls a DoCmd.RunSQL qstr, nothing is ever inserted, running manually I always see you are about to append 0 rows...

If I replace the "...select 'default',1,3,1,0,0,0,0,0,0,0 from tblRate..."
with values ('default',1,3,1,0,0,0,0,0,0,0) and eliminate the not exist sub qry the query runs,

but I need a way to only insert the values if the keys do not exists.

Is there a Access syntax error here or it what I'm trying to accomplish difficult in access and perhaps I need to get a row count and if 0 proceed with the insert + values code.


Ideas/suggestions?

thanks

[toddbailey]

Sorry forgot to mention Access 2010 XP i386