Access: select common values with an OR

Question

In Access I have four tables T1, T2, T3 and T4, with Fields from 1 to 10. One of their field is "Field1" so that I have the followings:

[T1].Field1, [T2].Field1, [T3].Field1, [T4].Field1

I would like to select all the values for which at least one Field1 is equal.

In other words I would like to do something like

SELECT Field1,
       Field2,
       Field4,
       Field5 
  FROM all the table before 
 WHERE [T1].Field1 = [T2].Field1 
    OR [T1].Field1 = [T3].Field1 
    OR [T1].Field1 = [T4].Field1 
    OR [T2].Field1 = [T3].Field1 
    OR [T2].Field1 = [T4].Field1 
    OR [T3].Field1 = [T4].Field1

-

My main difficulty is the FROM part because SELECT is fine and I can put there all the Fields I am interested in of the different tables, also the WHERE should be fine because I can say as I reported above. The issue is in selecting the data with the FROM command in Access. I believe it could be done with an Outer Join but I do not know how to implement it.

Suppose I have:

TABLE1
1 Field1: 0012
  Field2: TEST
  Field3: TEST3
2 Field1: 0010
  Field2: TEST
  Field3: TEST3

TABLE2
1 Field1: 0012
  Field2: TEST2
  Field3: TEST3
2 Field1: 0044
  Field2: TEST
  Field3: TEST3

TABLE3
1 Field1: 0012
  Field2: TEST2
  Field3: TEST3
2 Field1: 0044
  Field2: TEST
  Field3: TEST3

TABLE4
1 Field1: 0011
  Field2: TEST2
  Field3: TEST3
2 Field1: 0010
  Field2: TEST
  Field3: TEST3
3 Field1: 0044
  Field2: TEST2
  Field3: TEST3

At this point I would like to generate an output

TABLE1.Field1 TABLE2.Field1 TABLE3.Field1 TABLE4.Field1 TABLE1.Field2 TABLE2.Field2 TABLE3.Field2 TABLE4.Field2

In this case the first row will the one associated with Field1 0012:

0012 0012 0012 NULL TEST TEST2 TEST2 NULL

The second to 0010:

0010 NULL NULL 0010 TEST NULL NULL TEST

And so on. Could you help me formalizing my idea?

Thanks a lot

Add some sample table data and the expected result - all as formatted text (not images.) — jarlh, Sep 12 '17 at 07:47
So if I find Field1 = 'ABC' once in T1 and twice in T3, you expect three result rows, namely those three rows from the tables. But if Field1 = 'ABC' only occurs twice in T3, but in no other table, you don't want the records in the result. Is this correct? — Thorsten Kettner, Sep 12 '17 at 08:10
If [T1].Field1 = [T2].Field1, should Fields2, Fields4, and Fields5 come from T1 or T2 (or somewhere else altogether) — Jonathan Willcock, Sep 12 '17 at 08:15
Please use http://ozh.github.io/ascii-tables/ to generate better readable tables (you can copy&paste from Excel or Access to the Input box). — Andre, Sep 12 '17 at 09:14
Ah, now I properly understand your question. You need to use a full outer join, but you can't in Access. See [this question](https://stackoverflow.com/questions/19615177/how-do-i-write-a-full-outer-join-query-in-access) for an explanation how to work around that. However, with more than 2 tables, it will become way more complex than this. [This question](https://stackoverflow.com/questions/24700881/simulate-full-outer-join-with-access-on-more-than-two-tables) has an excellent answer by Gord Thompson on how to achieve that (but as he said, quite messy) — Erik A, Sep 12 '17 at 09:21
Thanks. So do you have any suggestion to make it work for 4? I have seen it's beyond my knowledge to build Gord's approach for 4 tables — DB_994, Sep 12 '17 at 09:33
I'm fairly certain that your database design / table structure is at fault. If you can add some realistic (not real) data instead of "Field2", "TEST3" it would be easier to help. — Andre, Sep 12 '17 at 09:37
The only suggestion I have is: don't! The complexity and execution time increases exponentially when adding more tables. Gord's approach was more of a proof that you could do this through great effort. But he stated that he isn't interested in doing it for 4 tables, and nor am I. If you can't write and maintain it, you will run into trouble with it as soon as you make any changes. — Erik A, Sep 12 '17 at 09:45

score 1 · Accepted Answer · answered Sep 12 '17 at 09:47

1

First glue all records together with UNION ALL keeping the table names. Then aggregate per field1 and use consitional aggregation to get the single fields. Use HAVING to only show field1 with more than one table entry:

select
  max(iif(table_name = 'TABLE1', field1, null)) as table1_field1,
  max(iif(table_name = 'TABLE2', field1, null)) as table2_field1,
  max(iif(table_name = 'TABLE3', field1, null)) as table3_field1,
  max(iif(table_name = 'TABLE4', field1, null)) as table4_field1,
  max(iif(table_name = 'TABLE1', field2, null)) as table1_field2,
  max(iif(table_name = 'TABLE2', field2, null)) as table2_field2,
  max(iif(table_name = 'TABLE3', field2, null)) as table3_field2,
  max(iif(table_name = 'TABLE4', field2, null)) as table4_field2,
  max(iif(table_name = 'TABLE1', field3, null)) as table1_field3,
  max(iif(table_name = 'TABLE2', field3, null)) as table2_field3,
  max(iif(table_name = 'TABLE3', field3, null)) as table3_field3,
  max(iif(table_name = 'TABLE4', field3, null)) as table4_field3
from
(
  select 'TABLE1' as table_name, field1, field2, field3 from table1
  union all
  select 'TABLE2' as table_name, field1, field2, field3 from table2
  union all
  select 'TABLE3' as table_name, field1, field2, field3 from table3
  union all
  select 'TABLE4' as table_name, field1, field2, field3 from table4
) alldata
group by field1
having count(*) > 1;

answered Sep 12 '17 at 09:47

Thorsten Kettner

89,309
7
49
73

Too good to be true? It seems it works perfectly! Thanks a lot. In the select you are saying "take the value if it exists otherwise take null"? What is alldata for? Could you just explain better the having part? Thx a lot – DB_994 Sep 12 '17 at 10:02
Moreover, I have some records in the query output where field1 is present just for table1 and empty for the others. Why is this? Shouldn't the query exclud these records from the output? – DB_994 Sep 12 '17 at 10:44
If `Field1` is present only for table1 in some result rows, then this indicates that `HAVING` found more than one record for the `EmpId`, but obviously all in table1. In your exaple you made it look like there is always only one record per `Field1` value in each table. If this is not the case: how is your result to look then? Say you have Filed1 = 'ABC' twice in table1 and thrice in table3, which rows and values shall be shown? Six records for all combinations (2 x 3 = 6)? Or one row only? But if one row, which values? The Maximums? The averages? Something else? – Thorsten Kettner Sep 12 '17 at 11:08
You can suppress employees that have several rows, but only in one table with: `having count(distinct table_name) > 1`. But be aware that you'd still have result records with multiple matches in a table, where you'd only show certain values for `Field2` and `Field3` (the maximums) as just explained. – Thorsten Kettner Sep 12 '17 at 11:11
`alldata` is just the name for the subquery (also called "derived table") by the way. And yes, "MAX(CASE ...)`is meant to "take the value if it exists otherwise take null". As now it seems there can be multiple entreis per `EmpID` in a table, though, that would change to "take one of the values if one or more exist otherwise take null". – Thorsten Kettner Sep 12 '17 at 11:16
Thanks. If they are present twice but they are not common with the other tables is there a shortcut so that they are not displayed? – DB_994 Sep 12 '17 at 11:29
As mentioned, use `having count(distinct table_name) > 1` (or `having min(table_name) <> max(table_name)` for that matter). However, as also mentioned, one problem persists: if you have `emp1 | A | B` in table1 and both `emp1 | X | C` and `emp1 | D | Y` in table2, the query would result in `emp1 |emp1 | . | . | A | X | . | . | B | Y | . | . | `. The values C and D would be suppressed (by X and Y). Think this over. Is this what you want? – Thorsten Kettner Sep 12 '17 at 11:44
Do I need to assign a value to `table_name`? Like `count(distinct table1)>1`? Because I cannot make it work. And finally (last question) is there a way in case there are duplicates to take only the highest one? – DB_994 Sep 12 '17 at 12:43
We *are* only taking the highest one with `MAX`. MS Access is the worst DBMS I know personally. It differs in many, many ways from standard SQL and there are many things it doesn't support. Among them `COUNT(DISTINCT ...)` obviously. Sorry, I didn't know that. So use `having min(table_name) <> max(table_name)` instead. – Thorsten Kettner Sep 12 '17 at 13:18

Access: select common values with an OR

1 Answers1