I would like to write a query that merges two Access 2000 databases into one. Each has 35 tables with identical fields and mostly unique data. There are some rows which will have the same "primary key" in which case the row from database A should always take precedence over database B. I use quotes around "primary key" because the databases are generated without any keys or relationships. For example:
Database A, table1
col1 col2 Frank red Debbie blue
Database B, table1
col1 col2 Harry orange Debbie pink
And the results I would like:
col1 col2 Frank red Harry orange Debbie blue
These databases are generated and downloaded by non-sql-savvy users, so I would like to just give them a query to copy and paste. They will obviously have to start by importing or linking one DB [in]to another.
I'm guessing I will have to make a third table with the combined results query and then delete the other two. Ideally, though, it would just take database B's tables and add in database A's (overriding where necessary).
I'm of course not looking for a complete answer, just hoping for some advice on where to start. I have some mySQL experience and understand the basics of joins. Is it possible to do this all in one query, or will I have to have a separate one for each table?