Frustrating SQL Query

[arpanmukherjee1]

how to use a value of other table as table name for another SELECT statement in Access?

i am currently using this statement

SELECT DISTINCT t.train_no, t.train_name 
FROM train_info_submit t, msg_info_submit m 
WHERE t.train_no=m.train_no 
ORDER BY t.train_no

now i have tables with table names Equal to entry in "train_info_submit"

eg- if "train_info_submit" has three trains, i will have three tables with table name equal to entry in "train_info_submit.train_no"

i have to check for row count in every table that has some values.

the query will look something like:

SELECT DISTINCT t.train_no, t.train_name 
FROM train_info_submit t, msg_info_submit m 
WHERE t.train_no=m.train_no 
    AND COUNT(***now select * from each t.train_no***)<>0  //error saying access cannot find table 't'
ORDER BY t.train_no

 

[manaskumar]

You cannot use table alias in inner query.
Replace t of inner query with train_info_submit