MS Access: How can i select columns from different tables

Question

In MS Access, I need to select a single row with many data from different tables with this query:

select top 1 a.colname,b.colname,c.colname 
from tba a, tbb b, tbc c 
where a.colname = 'efg' or
      b.colname ='efg' or
      c.colname ='efg'

I will get data perfectly when 'efg' is matched with at least 1 from the 3 tables BUT if it does not match with any of them I will get MS Access frozen with wait cursor. I guessed it is looping for some reason. I only able to stop it with END TASK in Windows's task manager.

How is my query and can any expert explain or suggest different techniques to avoid this?

Thank you.

You are using not joined tables, it means that if you have 5000 records in each table, select may return 5000*5000*5000=125,000,000,000 rows, of course Access will hang and even operating system may crash. Join tables first. — Sergey S., Nov 15 '16 at 06:58
Another question: because of my expected result is as a single return row, how do you suggest to do after getting the joined table? — Jackth, Nov 15 '16 at 07:20

score 0 · Answer 1 · answered Nov 15 '16 at 07:18

If the field in each of the 3 tables is the same field then you could use unions then select the first record returned.

select top 1 d.*
FROM (      SELECT a.colname  as colName
            FROM tba a
            WHERE a.colname = 'efg'
        UNION ALL
            SELECT b.colnamee as colName      
            FROM tbb b 
            WHERE B.colName='efg' 
        UNION ALL
            SELECT c.colname as colName      
            FROM tbc c
            WHERE c.colName='efg'
     ) as d

Or if they are 3 different fields then like others have suggested you could join the three tables to reduce the servers load.

thank you so much ClintB. It seems that this is my bad idea of getting result data. — Jackth, Nov 15 '16 at 07:29

MS Access: How can i select columns from different tables

1 Answers1