Ms-access – Joining two database tables into one

ms access

I have a database with two tables. Each table has different structure but they do share few common fields.

Simplified example:

Table1 field structure:

 Field1, Field2, Field3, Field4

Table2 field structure:

 Field1, Field4, Field3, Field5, Field6

Of course my database has better named fields and much more of them. As you can see both tables have Field1 (master key), Field3 and Field4.

Now I'm wondering if there is any possibility to somehow merge the data from these two tables into one, preferably without actually copying and duplicating the data (use of database relations or queries).

The third table should contain all common fields and some others (blank cells when there is no data available or perhaps some default data).

I know that in the long run it would be best to modify the database structure so that all common fields are stored in one table and all other fields in other relational tables but at this time I'm not allowed to make any changes to existing tables as my boss is afraid that this could lead to existing systems to crash.

There is a plan to update and finally merge those systems into one (that includes database restructuring) but I do need to do some statistical analysis before then so I'm looking for quickly implementable solution.

Performance isn't of critical importance for this as this would be only a temporary solution but is still desired because I don't want to wait for too long for retrieving the data nor do I want to cause any significant slowdowns to the whole database system.

So any suggestions are welcome.

Best Answer

I would use a view. Just prepare a select in the form of:

SELECT Field1, Field2, Field3, Field4, null as Field5, null as Field6
  FROM Table1

UNION 

SELECT Field1, null, Field3, Field4, Field5, Field6
  FROM Table2;

You can refine the select to filter or extend, as you like. When you're satisfied, just define a view as:

CREATE VIEW MergedTable AS 
SELECT Field1, Field2, Field3, Field4, null as Field5, null as Field6
  FROM Table1

UNION 

SELECT Field1, null, Field3, Field4, Field5, Field6
  FROM Table2;

Then you can always run your selects against the view, nearly as it was a table.

SELECT * FROM MergedTable;
Related Question