0

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.

  • Adding a few sample rows from the 2 tables in the question would be better. (so we know if there are rows in the 2 tables with same PK values - and what you want to do with them) – ypercubeᵀᴹ Jun 8 '15 at 11:43
5

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;
| improve this answer | |
  • This Works OK I guess. But is there a way to achieve same through Microsoft Access 2010 visual query dessigner like you could design database relations? – SilverWarior Jun 8 '15 at 13:01
  • Yes, you can! You just need to save a SELECT and that's your view. Check this out: stackoverflow.com/questions/6163836/… – Alessandro Cappello Jun 8 '15 at 13:27
  • I was hoping for a way to avoid writing nay code by hand but I guess I'll still have to do some work for this. Thanks any way your answer has already helped me getting the first results. And yes I have saw that article before and combine that with your answer to get roughly what I want. – SilverWarior Jun 8 '15 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.