[Access/SQL][SOLVED] same column, different tables

PrzemoF · 12-07-2016 01:39PM #1

I have to deal with a badly designed database and fixing it is not an option.
The db:

table_1:
field_a   field_b   field_c
AAA       AAA_b     AAA_c
BBB       BBB_b     BBB_c
CCC       CCC_b     CCC_c
..
..

table_2:
field_a   field_b   field_d
DDD       DDD_b     DDD_d
EEE       EEE_b     EEE_d
FFF       FFF_b     FFF_d
..
..

There are 20 tables in total.
Expected result of the query:
field_a    field_b
AAA        AAA_b
BBB        BBB_b
CCC        CCC_b
..
..
DDD        DDD_b
EEE        EEE_b
FFF        FFF_b
..
..

Any idea how to make it? I could try append query, but I'd have to do it for every single table and MS access doesn't allow to run multiple SQL queries (or I didn't find it yet). Migrating to LibreOffice Base to run multiple queries is my only option right now.

TL;DR: 20 tables with 2 common columns with data of the same category - how to pull it all together into one query?

FSL · 12-07-2016 01:54PM

Can you not use UNION or UNION ALL?

Tom Dunne · 12-07-2016 01:58PM

Do you mean something like:

table_1.field_b, table_2.field_b, table_3.field_b (and so on)

PrzemoF · 12-07-2016 02:05PM

Tom Dunne wrote: »

Do you mean something like:

table_1.field_b, table_2.field_b, table_3.field_b (and so on)

Yes, all results in two columns and I need to pull field_a and field_b

PrzemoF · 12-07-2016 02:07PM

FSL wrote: »

Can you not use UNION or UNION ALL?

Thanks! Looks like that's what I need!

I'll be back with the results if it works as expected

PrzemoF · 12-07-2016 02:20PM

Thanks again FSL!

S_LECT table_1.field_a,  table_1.field_b FROM table_1
UNION ALL S_LECT table_2.field_a,  table_2.field_b FROM table_2
UNION ALL S_LECT table_3.field_a,  table_3.field_b FROM table_3;

I had to change SELECT --> S_LECT as some protection system kicked in with a big red sign suggesting that I try to hack boards.ie

[Access/SQL][SOLVED] same column, different tables

Comments