Help Creating an Apend SQL Statement

[George Too]

Hello all,

I am moving records from one temp table to my main table using a SQL INSERT INTO statement. I got that part right. What I need help on is creating the criteria aspect of it. The query should look into the values of the main table and not import the record if it will be duplicated. Here is the stament:

"INSERT INTO tblMstrRollInfo( Reference, MasterRoll, Set_, Position )" & _
"SELECT RefNo, Field9, Field10, Field11 " & _
"FROM tblTemp; "

I need to check for duplication on Reference and MasterRoll.

Thanks for your imput,
George.

 

[FoFa]

INSERT INTO tblMstrRollInfo( Reference, MasterRoll, Set_, Position )
SELECT RefNo, Field9, Field10, Field11
FROM tblTemp T
left join tblMstrRollInfo M on M.reference = T.refno and M.masterroll = T.Field9
Where M.masterroll is null AND M.reference IS NULL

 

[George Too]

FoFa, thanks for your reply. I tested your code but it does not filter duplicate records. I tried modifying it with other tables but did not work. Do you mind explaining a bit the "T left join" line?

Thanks,
George

 

[George Too]

I solved it with this:

"INSERT INTO tblMstrRollInfo( LMPReference, MasterRoll, Set_, Position )" & _
"SELECT RefNo, Field9, Field10, Field11 " & _
"FROM tblTemp " & _
"LEFT JOIN tblMstrRollInfo ON tblTemp.Field9 = tblMstrRollInfo.MasterRoll " & _
"WHERE (((tblTemp.Field9) Not In ([tblMstrRollInfo]![MasterRoll])));"

Now on to the next one...

Thanks FoFa for steering me in the rite direction...