Append Query (1 Viewer)

[Anna Jewell]

Hi, I'm having trouble with an append query. I've got one table (table2) in which i have done an unmatched query to extract rows that are different from the original table (table1). I want to put the unmatched rows into the original table. i.e. original table = table1, new table = table2. I've set up the query with:

INSERT INTO table1 IN 'C:\WINDOWS\Desktop\access1.mdb'

(SELECT DISTINCTROW [table2].[fieldname1] AS [fieldname1] etc

FROM [table2] LEFT JOIN table1 ON iif (([table2].[fieldname1] Is Null) AND ([table1].[fieldname1] Is Null), True, [table2].[fieldname1] = [table1].[fieldname1]) etc

FROM table2 IN 'C:\WINDOWS\Desktop\access1.mdb'

WHERE ((([table1].[fieldname1]) Is Null)));

I keep getting an error message when I try to run this. Has anyone got any ideas??

 

[Pat Hartman]

You have two FROM clauses and you are using the IIf() function incorrectly. I can't even figure out where you're going with the join so I can't suggest an alternative.

Why not link the tables and use the query builder to build the query? You're making this more complicated than it has to be. If you append all the rows from table2 to table1, the duplicate rows will be discarded and the others will be appended.