1

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?

  • What do you see if you run just the SELECT statement (i.e. without the INSERT INTO clause? – Max Vernon Aug 20 '14 at 19:38
  • @MaxVernon I get a "Syntax error in INSERT INTO statement" if I remove the "INTO". – pheidlauf Aug 20 '14 at 19:40
  • No... I meant this: 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)); That will show you rows that would be inserted. I suspect there will be no rows displayed by that query. – Max Vernon Aug 20 '14 at 19:48
  • 1
    @MaxVernon Aaah gotcha. No records show up in the select query. – pheidlauf Aug 20 '14 at 19:50
  • 1
    Let us continue this discussion in chat. – pheidlauf Aug 21 '14 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.