Accessの集計クエリについて

Question

Access初心者です。ご教授下さい。

---

下記のようなテーブルがあります。

●担当テーブル
担当コード 担当者名 住所
T0001 すずき
T0002 さとう 東京都2番地
T0003 やまだ 東京都3番地
T0004 すずき 東京都1番地
T0005 すずき

---

担当者名の"すずき"が複数登録されてしまっているので、住所が存在する"すずき"で単一化したいと思い
集計クエリを作って担当者でグループ化したところ、下記のように"すずき"の住所が未定義となり単一化されました。

●集計クエリ（表示）
担当者名 住所の最後
すずき
さとう 東京都2番地
やまだ 東京都3番地

●集計クエリ（デザインビュー）
担当者名 住所の最後: 住所
担当テーブル 担当テーブル
グループ化 最後
昇順

---

下記のように集計するには、どうしたら良いのでしょうか？

●集計クエリ（表示）
担当者名 住所の最後
すずき 東京都1番地
さとう 東京都2番地
やまだ 東京都3番地

Comments

[sazi]

同一担当者の場合の「最後」の定義は何ですか？
サンプルデータだと、住所の入力がある、担当コードの最大で良さそうですが？

Answer 1

集計クエリなら、

●集計クエリ（デザインビュー）

フィールド	担当者名	住所の最大: 住所
テーブル	担当テーブル	担当テーブル
集計	グループ化	最大
並べ替え	昇順

あるいは、選択クエリで、

フィールド	担当者名	住所の最大: 住所
テーブル	担当テーブル	担当テーブル
並べ替え	昇順
抽出条件		Is Not Null

---

他の回答者へのコメントから、下記のような要件だと推測しました。

担当コードがユニークキー(主キー)である。
担当者名と住所で一意になるようにする。
担当者名に重複がある場合は、住所は未定義(Null)以外で、担当者コードが最大のものを抽出。

SELECT a.担当者名, a.住所
FROM
 担当テーブル a INNER JOIN
 (SELECT 担当者名, Max(担当コード) AS bmax
  FROM 担当テーブル
  WHERE 住所 Is Not Null
  GROUP BY 担当者名) AS b
  ON a.担当コード = b.bmax

Comments

[xpa]

沢山のご回答ありがとうございます。サンプルが悪くすみません。
最大でもできたのですが、事情があり、今回は最後を使って集計したいと思っています。いかがでしょうか？

Answer 2

以下のようなクエリで実現できるかと思います。

フィールド	[担当者名]	[住所]
テーブル	担当テーブル	担当テーブル
並べ替え	昇順
表示	☑	☑
抽出条件	Is Not Null
または

---

2021-12-28 23:30 追記

• 担当者ごとに行を1行決めたい。
• 決め方としては、まず住所が入力されていない行(住所がNULLの行)は無視する。
• 住所が入力されている行が1行だけの場合は、その行を選択する。
• 住所が入力されている行が複数存在する場合は、昇順で最後の住所が入力されている行を選択する。

追記していただいた内容をもとに、やりたいことを整理すると、上のようになるという認識で正しいでしょうか?

あっているのであれば以下のようなクエリで実現できるかと思います。

SELECT *
FROM 担当テーブル T1
WHERE  T1.住所 IS NOT NULL
AND NOT EXISTS (
  SELECT *
  FROM 担当テーブル T2
  WHERE T2.住所 IS NOT NULL
  AND T1.担当者名 = T2.担当者名
  AND T1.住所 < T2.住所
)

Comments

[xpa]

すみません。サンプルが悪かったです。
未定義のものだけでなく、定義されているものも含まれていまして、その場合には、昇順で並べたときに最後に来る住所を集計で抽出したいと思っています。下記の場合だと‘東京都4番地’になります。

●担当テーブル
担当コード 担当者名 住所
T0001 すずき
T0002 さとう 東京都2番地
T0003 やまだ 東京都3番地
T0004 すずき 東京都1番地
T0005 すずき
T0006　すずき　東京都4番地

[xpa]

上のパターンでは、最大
で可能ですが、実際のデータには複数のフィールドがあり、その各フィールドで別々で最大値を取るのではなく、最後の値を取る必要があります。
集計クエリの‘最後’で、出来るのかと思ったのですが、なかなか上手くいかず質問させていただきました。

Answer 3

こんばんわ。

担当者テーブルの住所が登録されていないレコードを

削除すればいいと思うのですが、いかがでしょうか。

Comments

[xpa]

ご教授ありがとうございます。
すみません。サンプルが悪かったです。
未定義のものだけでなく、定義されているものも含まれていまして、その場合には、昇順で並べたときに最後に来る住所を集計で抽出したいと思っています。下記の場合だと‘東京都4番地’になります。

●担当テーブル
担当コード 担当者名 住所
T0001 すずき
T0002 さとう 東京都2番地
T0003 やまだ 東京都3番地
T0004 すずき 東京都1番地
T0005 すずき
T0006　すずき　東京都4番地

[srsnsts]

ご返答ありがとうございます。
要件は理解できました。
それでしたら、
担当者名で集約し、住所の最大値を取ればよいのではないでしょうか。

[xpa]

ありがとうございます。
最大で可能ですが、実際のデータには複数のフィールドがあり、その各フィールドで別々で最大値を取るのではなく、最後の値を取る必要があります。
その場合、集計クエリの‘最後’なのかと思いテーブルと集計クエリの間に選択クエリ(住所て昇順ソートする)を入れて、色々やってみたのですが、どうもうまくいかず質問させていただきました。
住所を昇順ソートしたクエリに対して、集計クエリで最後を指定なのかと思ったのですが、間違いでしょうか？

[srsnsts]

お返事ありがとうございます。

>最大で可能ですが、実際のデータには複数のフィールドがあり、その各フィールドで別々で最大値を取るのではなく、
>最後の値を取る必要があります。

各フィールドで別々で最大値を取ってはあなたの仕様を満たしませんし、そのようなことを私は言っていません。
担当者名のみを基準に集約します。その上で住所の最大値を取ります。
そうするとこんなデータになりますよね。

担当者コード MAX(住所)
さとう 東京都2番地
やまだ 東京都3番地
すずき 東京都4番地

担当者名を基準に集約したので、担当者名と集約関数の項目以外が落ちます。
もし担当者マスタの他のフィールドに用があるなら、この集約結果と
担当者マスタを結合すればよいです。

> 住所を昇順ソートしたクエリに対して、集計クエリで最後を指定なのかと思ったのですが、間違いでしょうか？
そのやり方で可能なのかは、私にはわかりかねます。

[srsnsts]

失礼しました、上のコメントのデータ、ヘッダ名が適切ではないですね。
"担当者コード" ではなく "担当者名" が正しいです。
申し訳ない。