Clever Geek Handbook

How to combine more than two Microsoft Access tables into one table?

Let's say I have 10 tables, each table has an ID and a ValueN. I want to combine all of them into one table that has the following content:

ID Value1 Value2 Value3 ...

The IDs are unique, but each table can have any number of records, so the result row can be:

001 3.1 Null 4.6 ...

I know how to do this simple query when there are only two tables. But dont know how best to compare id values ​​from 10 tables.

+3


source to share


1 answer


Let's say you have 3 tables (or any number of tables)

Table1:          Table2:           Table3
ID  Value1       ID   Value2       ID   Value3
1   A            1    AAA          1    111
2   B            2    BBB          2    222
3   C            5    EEE          3    333
                                   4    444
                                   5    555

You can use a query like this to make sure you are getting all possible data from all tables:

SELECT U.ID,
       Table1.Value1,
       Table2.Value2,
       Table3.Value3
FROM   (((SELECT ID FROM Table1
          UNION
          SELECT ID FROM Table2
          UNION
          SELECT ID FROM Table3) AS U
         LEFT JOIN Table1 ON U.ID=Table1.ID)
        LEFT JOIN Table2 ON U.ID=Table2.ID)
       LEFT JOIN Table3 ON U.ID=Table3.ID;


Result:

ID    Value1    Value2     Value3
1     A         AAA        111
2     B         BBB        222
3     C                    333
4                          444
5               EEE        555

Basically, this is just a sequence LEFT JOIN

for each table, they are all combined by concatenating all possible identifiers across all tables:

SELECT ID FROM Table1
UNION
SELECT ID FROM Table2
UNION
SELECT ID FROM Table3
+3


source






More articles:


All Articles