ACCESS ２つのテーブルの最大値を選択クエリで抽出

質問

１週間以上悩んでいます。よろしかったらお知恵をお貸し下さい。

３つのテーブルがあります。
１．商品データT（商品ID、商品名）２０件
２．売り上げデータT（商品ID、日付、場所、備考）９０００件・・１日２回更新されます。
３．商品更新T（商品ID、更新内容、日付）１０００件・・・１日１回更新されます。

この中から、１の商品名、２の最新の日付とそれに対応している場所と備考、３の最新の日付とのその更新内容を取り出したいのです。
３のテーブルにデータがない商品IDもあります。その場合は２の売り上げデータの最新のみ表示し、商品更新Tの更新内容や日付は空白にしたいです。
商品IDでグループ化にすると、２０件の商品IDに、９０００件もの場所や更新内容が表示されてしまったりしてうまくいきません・・・

なにぶん初心者ですので、クエリで作りたいです。
どなかたお分かりになる方、助けて下さい！！！

回答No.3

noname#245936 · ベストアンサー

左様で。…困ったですねェ。（笑）

私もDBは仕事柄いろいろな種類やってまして…えーと。
Accessあまり詳しくないんですね。

Accessってテーブル・クエリ・レポート・マクロの４要素
ありましたっけ？その、クエリというところに書きます。

私が書いたやりかたは、一撃でドカンと出力する方法で。
Accessの簡単ウィザードのお絵かきモードだけで実現しようと
すると、多分クエリを最低親子の２個作る必要があります。

１．まず１つめに子のクエリーを作成します。
　　売上テーブルで商品IDごとにグループにした、日付最近のもの
　　だけをとってくる基準テーブルとなるクエリを作ります。
　　列は、商品IDと最新日付だけの2列だけです。
　　「場所」と「備考」も取得すると多分グループ化できないので
　　商品IDの種類数と最新日付だけ抽出します。
　　ここは、
　　SELECT 売上T.商品ID,MAX(日付) FROM 売上T GROUP BY 商品ID
　　と表現できます。
　　これが最終的に取得したいレコード数になります。
２．次いで２つめに親のクエリーを作成します。
　　これは全部をくっつけてまとめあげるクエリーです。
　　先ほど作った商品IDと日付だけの最新売上クエリーに対し
　　商品マスタを商品IDでリレーション（JOIN）します。
　　また、同様に、最新売上クエリーに対し売上明細を再度
　　商品IDと日付でリレーションします。
　　更に、更新Tも同様にくっつけます。
　　くっつけるときは、最初に子クエリーありきで、あとから
　　それにくっつける商品T、更新Tは存在しないかもしれない
　　扱いでくっつけます。
　　よって、何らかのNULL処理する関数でくくります。
３．できた親クエリーを呼び出すと、欲しい結果が
　　一撃で取得できます。
　　（４つも結合しているため参照のみで更新はできません。）

こうすることで欲しい結果がもらえるかと思います。

SQL文は、確かに最初に見ると難解で難しいですね。

でも、一度分かってしまうと、表を自在にデザインできますので
非常に便利ですし、SQLで制御できるDBは他にもわんさかあります
ので後々とても便利です。
変なプログラムよりトライする価値はあるかと思います。

ちなみに、我々IT屋さんがAccessを滅多に使わないのは、
100万件レベルの処理が遅いのと、複数人同時参照に弱い性質、
データが業務用よりは壊れやすいからです。
なので、あまり触っていないので詳しくないんです。
すいません。

お礼

この度は何度もありがとうございました。
また機会がありましたら宜しくお願いします。

補足

何度もお手数お掛けしてすみません、そしてありがとうございます！
昨日１日試行錯誤し、yama-takuさんのおかげで出来ました！！
売り上げデータTと商品更新Tの日付をMAXにし、商品IDでグループ化したクエリをそれぞれ作り、
新たに新しいクエリ（その２つのクエリの商品IDと商品データTの商品IDをリレーション）に「場所」や「備考」といったデータをくっつけました。
SQLビューで見てみると、「INNER JOIN」「RIGHT JOIN」という言葉があったりして、こういった言葉で結果が得られるなんてとても面白いですね。
すごく興味がわきました。
確かにACCESSは共有設定には弱いので、私もいずれは、SQLを勉強して、ORACLEなど使えるようになりたいです！（そのためにはあと１０年くらい必要かも知れませんが・・・）
この度は本当にありがとうございました！

回答No.2

noname#245936

おっと、失礼しました。
先の回答にバグが…。
商品更新テーブルの日付と、売上データの日付を。
単語が一緒なので一緒に結合してました。

多分、商品マスターなくてもいい場合のSQLはこんな
感じかと。

SELECT
　売上最新T_KEY.商品ID,
ISNULL(商品T.商品名,""),
売上最新T_KEY.日付,
売上T.場所,
売上T.備考,
　ISNULL(更新T.更新内容,"")
FROM
(SELECT 売上T.商品ID,MAX(日付) FROM 売上T GROUP BY 商品ID)
AS 売上最新T_KEY　　<--日付とIDで絞る
LEFT OUTER JOIN 商品T
ON 売上最新T_KEY.商品ID = 商品T.商品ID　<--商品をくっつける
LEFT OUTER JOIN 更新T
ON 売上最新T_KEY.商品ID = 更新T.商品ID <-- 更新Tをくっつける
LEFT OUTER JOIN 売上T
ON 売上最新T_KEY.商品ID = 売上T.商品ID <-- 売上T付帯情報をくっつける
AND 売上最新T_KEY.日付 = 売上T.日付
[WHERE [絞りたい条件]]
ORDER BY 商品T.商品ID ASC

商品が確実にあるなら、ちょっとシンプルになり高速になり。
こうなります。

…略（上と一緒）
FROM 商品T
LEFT OUTER JOIN (SELECT 売上T.商品ID,MAX(日付) FROM 売上T GROUP BY 商品ID)
AS 売上最新T_KEY　　<--日付とIDで絞る
ON 商品T.商品ID = 売上最新T_KEY.商品ID <-- 商品をくっつける
…以下同文

要は集合の考え方でして、一番絞れる、一番最終的にほしい
レコードを絞れるきっかけのテーブルをFromに近い所に書き、
あとは、ごそごそ付帯情報をくっつけます。

お礼

重ね重ね、ありがとうございます。
商品データTは必ずデータがあります。ですが、このいただいた例をどうやってACCESSに加工したらよいのかがさっぱりわからないのです・・・
丁寧にご説明いただいているのに申し訳ありません。
こんなに難しいことだとは思っていませんでした。。。。
どうもありがとうございました。

回答No.1

noname#245936

手抜きで、動作確認してませんのでバグってたらすみません。

売上明細ありきで出力するのが基本という考えで、
商品T情報(質問１項)がない場合も考慮、また、
更新T情報(質問３項)を考慮しますと。

要するに、２項の売り上げ明細の商品ID毎の最近日付取得は
SELECT 売上T.商品ID,MAX(日付) FROM 売上T GROUP BY 商品ID
ORDER BY 商品ID
でまとめられ、それに全部くっつけれると思いますので。

解答は以下に近ーいカタチじゃないかなと。

SELECT
　売上最新T_KEY.商品ID,
ISNULL(商品T.商品名,""),
売上最新T_KEY.日付,
売上T.場所,
売上T.備考,
　ISNULL(更新T.更新内容,"")
FROM
(SELECT 売上T.商品ID,MAX(日付) FROM 売上T GROUP BY 商品ID)
AS 売上最新T_KEY　　<--日付とIDで絞る
LEFT OUTER JOIN 商品T
ON 売上最新T_KEY.商品ID = 商品T.商品ID　<--商品をくっつける
LEFT OUTER JOIN 更新T
ON 商品T.商品ID = 更新T.商品ID <-- 更新Tをくっつける
AND 商品T.日付 = 更新T.日付
LEFT OUTER JOIN 売上T
ON 商品T.商品ID = 売上T.商品ID <-- 売上T付帯情報をくっつける
AND 商品T.日付 = 売上T.日付
[WHERE [絞りたい条件]]
ORDER BY 商品T.商品ID ASC

ただし、当方SQL Server派なのでAccessでは方言があるかも
しれませんので通らない関数は変換してください。
（Join定義が書けない場合はWhere句で、ON句以下を定義。）

また、売上ありきではなく商品マスターが必ず存在すると
信頼できる場合はJOIN順を変えるとパフォーマンスが出ます。

難しいですか？

お礼

詳細なご回答、どうもありがとうございます。
しかしながら、私はSQLがわかりませんので、教えていただいた内容も、申し訳ないのですが、さっぱりわからないのです・・・
LEFT OUTER　とか　JOINとか初めて聞きました。
これからSQLを学んでいって、理解できるようになりたいと思います。
ありがとうございました。