Microsoft Access 掲示板

Accessのテーブルでしょうか。Accessではテーブル名にピリオドを含めることはできません。

table001.tbl は table001、Search.tbl は Search としたら、下記のようなSQLで可能です。
（ただしitem#に重複があっても1件のデータしか返しません。 ）

SELECT TableName, item#, DLookUp("qty",TableName,"item#='" & item# & "'") AS qty
FROM Search;

ただ、同じ構造のテーブルが数百もあるという設計はデータベースとしては完全にNGです。

上記のSQLも標準のSQLではないDLookupというAccess独自の関数が使えたのでシンプルにできましたが、本来のSQLではないです。

データベースの機能を活かすなら設計を変更すべきです。

設計の問題点と解決手法は hatenaさんが完璧な回答をなさっているので、あくまで補足として。
　　

• フィールドの名前に記号や予約語が含まれる

• TableNameフィールドに
  　カレントデータベースに存在しないテーブル名が誤って記述されていた場合、
  　DLookupを使った演算フィールドはエラーになる

　　
という理由で、SQLは念入りに記述したほうがいいかもしれないですね。

SELECT x.TableName
     , x.[item#] As item
     , dlookup(
               "[qty]"
             , "[" & x.TableName & "]"
             , "[item#] = '" & x.[item#] & "'"
       ) As qty
FROM Search x
INNER JOIN MSysObjects y
        ON x.TableName = y.[Name]
WHERE y.[Type] IN ( 1, 4, 6 )
;

皆様のアドバイスありがとうございます。
Dlookupを勉強します。
悪い例でも解説、本当にありがとうございます。

背景として、①各テーブルは数GBあり一つにできないのと、②テーブル数がたくさん有るのと、③一つのテーブルから取り出すデータは２０万行ぐらい、④一度に検索するテーブル数は２０程度、となっています。

例と出したのが、よくないことも理解できました。

実態ですが、このProjectではテーブルはCSVで特定のパスにあります。

このProjectでは、数百ある測定器で、その測定器のデータが、「計測ポイントIDと年」をファイル名として、特定のフォルダに入っており、月日時分秒の時間と、item#が計測器内の温度、湿度等の種類、QTYが計測値です。

どのような解析をするかが毎回変わるため、今回のような使い方を想定しています。

上記の測定器の例ですが、テーブルが同じ構造の複数のCSVの場合を教えて頂けないでしょうか。

CSVをリンクテーブルとして取り込んでおけば、前回の回答のSQLでできるはずです。
ただし、実用的な速度になるかどうかはわかりません。

もし、実用的な速度がでないなら、SQLでやるのはあきらめて、
VBAでCSVを読み込んで、処理する方向で検討したほうがいいでしょう。

テーブルが同じ構造の複数のCSVの場合を教えて頂けないでしょうか。

リンク元となっているファイルの種別をCSVに限定したいということでしたら
MSysObjects テーブルの connect フィールドあたりを参照すればいいでしょう。
( 厳密な判定なら他のシステムテーブルも併用する必要がありそうですが面倒です )

テーブルはCSVで特定のパスにあります。

同様に、MSysObjects テーブルの database フィールドの値を参照すればいいでしょう。

SELECT [name]       as リンクテーブル名
     , foreignname  as 参照元ファイル名
     , [database]   as リンク元のパス
     , [connect]    as ファイルの種類
FROM msysobjects
WHERE [type] IN ( 4, 6 )
;

といった感じで各フィールドの値を確認した後、
任意の抽出条件を指定すればいいのではないでしょうか。

例えば、CSVリンクテーブルに限定して Lookup するのでしたら、以下のような感じになるでしょう。

SELECT x.TableName
     , x.[item#] As item
     , dlookup(
               "[qty]"
             , "[" & x.TableName & "]"
             , "[item#] = '" & x.[item#] & "'"
       ) As qty
FROM Search x
INNER JOIN MSysObjects y
        ON x.TableName = y.[Name]
WHERE y.[Type] = 6
  AND y.[connect] Like '*FMT=Delimited*'
  AND y.foreignname Like '*[#]csv'
;

　　
ちなみに、DLookUp関数を使ったクエリは
Searchテーブルのデータが 1,000 件程度でも画面のちらつきと重さが半端ないですね。

扱うデータ件数が20万ということですと、運用構築とも
どんなに高性能な端末であっても、ちょっと現実的ではないと思います。