I have two tables of interest linked in a MS Access 2007 database. The master table called EMPL_MASTER (from which I only have read access, I cannot make changes) updates every time a system administrator adds a new employee. The table which I populate and store some data in is called AllEmployees. The AllEmployees table already has several hundred employee records loaded in, but I need to (automatically) insert new records every time the EMPL_MASTER has new records. The two tables have different column names, as I don't need all of the columns from the master table. This is the query I have tried so far, without success:
INSERT INTO AllEmployees ( OPERATOR_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, etc.. ) SELECT DISTINCT EM.OPERATOR_ID, EM.LAST_NAME, EM.FIRST_NAME, EM.MIDDLE_NAME, EM.etc FROM dbo_TBL_SQL_OHCMS_EMPL_MASTER EM WHERE NOT EXISTS (SELECT * FROM AllEmployees WHERE (AllEmployees.OPERATOR_ID=EM.OPERATOR_ID));
When run, however, this query shows "You are about to append 0 row(s)," although I know that there are employees in the EMPL_MASTER table that do not yet exist in the AllEmployees table. How can I fix this query?