I have two strange table where I cannot have unique fields, But I need to update daily transactions from temp table to the main table with out any duplicates..
Code:
INSERT INTO tblB_main ( FName,FType,Results,FileName, Date_Received, comments )
SELECT I.FName,I.FType,I.Results,I.FileName, I.Date_Received, I.comments
FROM B_temp AS I INNER JOIN tblB_main AS o ON (i.Date_Received =o.Date_Received) AND (i.FName= o.FName)
WHERE ((((i.FName) Is not Null)) AND ((i.Date_Received) is Not Null)) AND ((o.FName) is Null) AND ((o.Date_Received) is Null);
My temp file have records from 2 to 50, which are different from the records in the main table. Since I do not have unique values in both table to compare and append. I joined my tables with file name and date received fields.
Some times my inner join query does not work.
Any thoughts and suggestions?