ACCESS同一日付内の特定レコード抽出

質問

ACCESS初級者です。

例えば以下のテーブルがあります。

日付 住所　　　　　　氏名　　　配送費
2/1　東京品川区　　Ａ商店　　　2000
2/2 東京大田区　　Ｂ商店　　　1500
2/2 東京大田区　　Ｃ商店　　　1500
2/2 東京江東区　　Ｄ商店　　　2500
2/3 東京新宿区　　Ｆ商店　　　1000
2/3 東京大田区　　Ｃ商店　　　1500

住所が大田区であれば別途料金を自動計算させたいと思います。
但し、同一日付内で大田区が何件あったとしても大田区全体での別途料金となることから
１レコードのみに別途料金を付加させたいのです。

上記例の場合には、2/2に2件の大田区（Ｂ・Ｃ商店）があるので、いずれかに別途料金を
追加したいのです。2/3の場合は1件しかないので簡単なクエリで計算できます。
当然、2/1は該当がないのでゼロとなります。

まず大田区のみを抽出し、日付の比較を行う等の複雑なコーディングがいるのでしょうか。

難しく考えすぎなのでしょうか。

アドバイスをお願い出来ればと思います。

回答No.2

30246kiku · ベストアンサー

＃１です

その後、解決に向かっているのでしょうか。

＃１では
１）クエリ「Q2」を作っておく方法
２）１）をサブクエリとして展開する方法
３）別途料金を分配する方法
４）一意に特定できるフィールドを使う方法

の４つを回答してみました。
上記１）～４）は、提示されたサンプルで動作すると思いますが、
実際のデータでは、どうなっているでしょうか。
１）２）であれば問題ないと思いますが、例えば、住所が微妙に異なる以下の様な場合

日付　　住所　　　　　氏名　　配送費
2/1　　東京品川区　　Ａ商店　　2000
2/2　　東京大田区★　Ｂ商店　　1500
2/2　　東京大田区▲　Ｃ商店　　1500
2/2　　東京江東区　　Ｄ商店　　2500
2/3　　東京新宿区　　Ｆ商店　　1000
2/3　　東京大田区　　Ｃ商店　　1500

３）４）は意図した動きになっていないと思います。
以下の様な結果になっているかと思います。

日付　　住所　　　　　氏名　　配送費　別途料金
2/1　　東京品川区　　Ａ商店　　2000　　
2/2　　東京大田区★　Ｂ商店　　1500　　1500
2/2　　東京大田区▲　Ｃ商店　　1500　　1500
2/2　　東京江東区　　Ｄ商店　　2500　　
2/3　　東京新宿区　　Ｆ商店　　1000　　
2/3　　東京大田区　　Ｃ商店　　1500　　1500

これは、サブクエリ内の GROUP BY 指定を、日付, 住所 にしているため
「東京大田区★」と「東京大田区▲」は違うものとして扱われます。

４）の場合、GROUP BY から 住所 を削除することで動くと思いますが、
３）の場合は削除するだけでは意図した動きにはなりません。

やりたいことは、１）２）及び修正の４）でできているとは思います。

３）が必要であれば、解決に向けての進捗等補足ください。
（実際の住所はどんな感じになっているのかも併せて頂ければ・・・）

なお、４）は、１）２）と異なり、同一日付内に同一氏名があっても大丈夫なはずです。

お礼

30246kiku様

度重なるご配慮、誠に有難うございます。

お陰をもちまして無事に完成致しました。

取りあえずＱ２方式で作成してみました。

何せＣＯＢＯＬの経験が長かったものですから理屈では理解できのですが

実際となると奥が深いと感じています。

その他の方法も試して挑戦してみたいと思います。

回答No.1

30246kiku

クエリのSQLビュー表示での回答で良いですか。

テーブル名を ★★ と仮定します。
以下、SQLビューに記述して、テーブル名を正式なものにして、各記述を
データシートビューで表示したり、デザインビューで表示してみてください。

> いずれかに別途料金を

まず、同一日付、住所に「大田区」がある「氏名」を求めておきます。（※１）

SELECT 日付, First(氏名) AS S
FROM ★★
WHERE 住所 Like '*大田区*'
GROUP BY 日付;

「氏名」の順は考えないので、First() で求めておきます。
これを、クエリ「Q2」名で保存しておきます。

上記のクエリを使って最終表示にしていきます。（※２）
（別途料金を 1500 と仮定します）

SELECT Q1.*, IIF(IsNull(Q2.S),Null,1500) AS 別途料金
FROM ★★ AS Q1 LEFT JOIN Q2 ON (Q1.日付=Q2.日付) AND (Q1.氏名=Q2.S);

この結果は

日付　　住所　　　　氏名　　配送費　別途料金
2/1　　東京品川区　Ａ商店　　2000　　
2/2　　東京大田区　Ｂ商店　　1500　　1500
2/2　　東京大田区　Ｃ商店　　1500　　
2/2　　東京江東区　Ｄ商店　　2500　　
2/3　　東京新宿区　Ｆ商店　　1000　　
2/3　　東京大田区　Ｃ商店　　1500　　1500

の様になると思います。

クエリ「Q2」として保存していましたが、クエリの個数が多くなるのも嫌なので
クエリ「Q2」をサブクエリとして組み込みます。

SELECT Q1.*, IIF(IsNull(Q2.S),Null,1500) AS 別途料金
FROM ★★ AS Q1 LEFT JOIN
(SELECT 日付, First(氏名) AS S FROM ★★ WHERE 住所 Like '*大田区*' GROUP BY 日付) AS Q2
ON (Q1.日付=Q2.日付) AND (Q1.氏名=Q2.S);

※２の
LEFT JOIN Q2 の Q2 部分を
(SELECT 日付, First(氏名) AS S FROM ★★ WHERE 住所 Like '*大田区*' GROUP BY 日付) AS Q2
として展開します。

※※ これらの場合、同一日付内に同一氏名がないことが前提にあります。

なお、実際に別途料金を、その氏名先に請求することはないと思いますが、
請求する行為があったとしたら、公平に請求する以下の様なものでも良いのかも。

SELECT Q1.*, IIF(IsNull(Q2.CT),Null,1500/Q2.CT) AS 別途料金
FROM ★★ AS Q1 LEFT JOIN
(SELECT 日付, 住所, Count(*) AS CT FROM ★★ WHERE 住所 Like '*大田区*' GROUP BY 日付, 住所) AS Q2
ON (Q1.日付=Q2.日付) AND (Q1.住所=Q2.住所);

この結果は、※３

日付　　住所　　　　氏名　　配送費　別途料金
2/1　　東京品川区　Ａ商店　　2000　　
2/2　　東京大田区　Ｂ商店　　1500　　750
2/2　　東京大田区　Ｃ商店　　1500　　750
2/2　　東京江東区　Ｄ商店　　2500　　
2/3　　東京新宿区　Ｆ商店　　1000　　
2/3　　東京大田区　Ｃ商店　　1500　　1500

また、テーブル ★★ 内にレコードを一意に特定できるものがあったら、
（以下では、オートナンバの「an」フィールドがあったとして）

SELECT Q1.*, IIF(IsNull(Q2.A),Null,1500) AS 別途料金
FROM ★★ AS Q1 LEFT JOIN
(SELECT Min(an) AS A FROM ★★ WHERE 住所 Like '*大田区*' GROUP BY 日付, 住所) AS Q2
ON (Q1.an=Q2.A);

この結果は ※２と同様な

an　日付　　住所　　　　氏名　　配送費　別途料金
1　　2/1　　東京品川区　Ａ商店　　2000　　
2　　2/2　　東京大田区　Ｂ商店　　1500　　1500
3　　2/2　　東京大田区　Ｃ商店　　1500　　
4　　2/2　　東京江東区　Ｄ商店　　2500　　
5　　2/3　　東京新宿区　Ｆ商店　　1000　　
6　　2/3　　東京大田区　Ｃ商店　　1500　　1500

になると思います。

※ Min() 部分は First() でも Max() でも、いかようにでも

要は、Q2 としてどのレコードのもの・・・と限定して
その限定したものとどのように結びつけるか、だと思います。

お礼

回答有難うございます。