Insert rows (2002)

[Kevin]

How do I copy all the rows from table 2 into table 1 leaving off all duplicate rows? Can I do this in the Access UI with some wizard or do in need to write an Insert query?

In other words, how do I merge two tables? (The columns are identical)

[Kevin]

Ah! found it. Click around long enough and you're bound to find something that works. Here's how:

1) Open Project File. Click Tables.
2) Right click table to copy, click Copy
3) Right click white space, click Paste
4) In "Paste Table As" dialog, click "Append Data..." and type the destination table name.
5) Click Ok.
6) At the nasty looking "warning" dialog, click Yes to proceed. (You'll only see this warning if you have duplicate records and a primary key set on the destination table.)

[Kevin]

Whoops. That don't work. Missing records. Not sure why... Something to do with those duplicates...

[charlotte]

Missing records? That isn't very specific. Was there an autonumber column involved perchance?

[Kevin]

When the append finished, the destination table did not contain a "merge" of all records from table1 and table2. Some records were appended, most were not.

[charlotte]

Um, you didn't ask for a merge of all the records. I thought you were appending from table2 to table1 or vice versa. And is there an autonumber in there? If there is and you tried to append it, the value would have also been appended. If it already existed in the destination table, it wouldn't be appended again. Or are we again dealing with an ADP and SQL tables, which are different from Access tables in many ways.