Duplicate table structure with different data I am looking for a way to resolve a design issue with a Microsoft Access project that is being converted to use SQL Server 2014 as a backend.

We have several Access backend files, two of them have the same table structure, but different data. In the "Quotes" MDB backend file, we have tables containing sales quotes. In the "Jobs" MDB backend file, we have tables containing jobs (quotes that have been sold), but also the same sales quotes tables are duplicated. When a quote is sold, it's copied from one backend to the other. Which means the data exists in two places and is not kept in sync.

When I originally started upsizing tables from the MDB backends, i didn't think about this issue but now it's becoming a problem that needs to be resolved. We don't know how the structure should be fixed. It's my understanding that the combined set of all the tables in the various MDB backends for this software, should be in one database in SQL Server. The only reason for splitting them up as they are now is to overcome limitations of the Access MDB format.

To complicate matters further, the tables have exactly the same names. The Access front-end application switches between backends on-the-fly, when the user changes between quoting or working with sold orders.
Since you have determined that the two database approach is not right for you, you want to merge the two databases. (And I agree for all the little value that is)

(1) One suggestion, the simplest, is to rename one of the tables and make the needed changes to the code to use the proper table instead of the proper database and table.

(2) Another suggestion would be to merge the two tables into a single table. You would need to add a column to differentiate between the "Quotes" rows and the "Jobs" rows.  

Also, you would need to make sure that your primary keys are unique between the two tables. If these rows were generated with Access ascending keys it is likely that you have duplicate keys between the two tables.

If this is the case, you would need to change the Primary Keys in, let us say "Jobs", to avoid any PK conflicts with "Quotes" rows.  Then also change all the referencing tables to have Foreign Key values that reference the new Primary Key values.  

If there are many duplicate tables, then repeat as needed to bring everything into the new united database schema.

This will be a bit of work to set up, but once completed you should not need to think about that any more.