MS Access Two tables return records where value from Table 1 found in more than one column table 2

Question

I can't seem to figure out the below using MS Access.

Summary is that I have Two tables. I would like to compare column E in table to column A, B, or C and return the records on table1 where the values match. Basically the criteria to match (table2.column E) can be in more than one the columns in table1.

Ex. Table1

Column A | Column B | Column C

Dan Stan XXX

David XXX XXX

Roger XXX XXX

Ricco XXX XXX

Wilbert XXX Dingo

Table2

Column D | Column E | Column F

1 Roger North

2 Stan South

3 Michael South

4 Colo East

5 Kanye East

6 Dingo West

Return

Column A | Column B | Column C | Column F

Dan Stan XXX South

Roger XXX XXX North

Wilbert XXX Dingo West

I have tired below below SQL string in MS Access (2013)

SELECT table1.* FROM table2 INNER JOIN table1 ON table2.column E = table1.[column A] OR table2.column E = table1.[column B] OR table2.column E = table1.[column C];

I don't get an error but something is seriously wrong - queries on this DB usually take 20-40 seconds (tops) (3 million records - linked tables to MS SQL Server 2016) - the query has been going for 40 minutes. When I view the table view in MS Access itself it shows the records but the application keeps freezing and stuttering. I tried going to end of the records and I had to kill MS Access. Tried pulling the data into a Pivot in Excel and I have never seen the data pull so slowly (we are talking 50 records a minute).

Any help would be appreciated.

June7 June7 · Accepted Answer · 2017-07-19T03:17:00

I don't think a join clause can have OR operation. Also, need [ ] around [column E].

Try:

SELECT table1.*, tabl2.columnF FROM table1, table2 WHERE [column e] = [column a] OR [column e] = [column b] OR [column e] = [column c];

MS Access Two tables return records where value from Table 1 found in more than one column table 2

1 Answers