ACCESS| 意図した並び順でTBにオートナンバー列を追加する方法

Question

実現したいこと

ACCESS2019 を使用しています。

テーブルA

明細データがはいっています。

顧客No	商品No	日付	数量	単価
001	010	2024/03/01	2	100
001	010	2024/03/02	1	100
002	014	2024/03/01	1	150
002	014	2024/03/05	2	250
001	010	2024/03/07	1	100

テーブルA を 顧客No、商品No、日付 を各昇順に並べ替え
顧客No、商品No ごとの最終行を取得し、テーブルBを作成したいです。

テーブルB（今回作りたいテーブル）

顧客No	商品No	日付	数量	単価
002	014	2024/03/05	2	250
001	010	2024/03/07	1	100

データ型

顧客No・商品No：短いテキスト、日付：日付/時刻型、数量・単価：数値型

発生している問題・分からないこと

テーブル作成クエリで、

顧客No・商品No は [ 集計：グループ化 ] を設定。
日付・数量・単価 に [ 集計：最後 ] の設定をした際に、思う結果になる時とならない時がありました。

該当のソースコード

特になし

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

Googleで調べた中で、並べ替え順とテーブル作成時のデータ格納順はイコールではないので、今回のケースの場合、オートナンバーを振り最大値を取得する等の対策が良いと分かりました。

試したこと

流れとしては以下で作成しようと思っています。

1. テーブルA　顧客No、商品No、日付　昇順に並び替え（目的：２で意図したナンバリングにするため）
2. テーブルAにオートナンバー型のカラムを追加（調べていたら下記SQLの方法があると知りました）
3. テーブルAを元にテーブルBを作成（IDの抽出条件にDMax関数を使用）

ALTER TABLE [テーブルA] 
ADD COLUMN [ID] AUTOINCREMENT;

教えていただきたいこと

ここで２・３は出来たのですが、２を実施する前に、並べ替えをする方法が分からなくてつまづいております。（上記１）
説明不足の点がありましたらご指摘ください。ご教示のほど、お願いいたします。

また、そもそも処理の流れが良くないよ！と言う場合にも、別の方法をご教示いただけると助かります。

補足

特になし

Comments

[sazi]

> 顧客No、商品No ごとの最終行を取得し、テーブルBを作成したいです。
テーブルAの一意キーは「顧客No,商品No,日付」ですか？
そうでなければ、同じ日付が存在する可能性があり、最終行を一意に特定できません。

[wataame25]

sazi様

ご質問ありがとうございます。説明不足で失礼いたしました。

ご指摘の通り、テーブルAには一意キーがなく、現に「顧客No,商品No,日付」すべてのカラムに重複データがございます。

一意キーがある場合には最終行が特定できるのですね。
勉強になります。テーブルの設計についても精進します。ありがとうございます。

[sazi]

> 一意キーがある場合には最終行が特定できるのですね。
一意キーの有無ではなく、一意キーが「顧客No,商品No,日付」なら、最終の日付という条件で最終行を1件に特定できるという事です。

[wataame25]

sazi様

そう言うことでしたか。勘違いしてしまいました；；
ご説明くださり、ありがとうございます。

Answer 1

既に調べた通り、クエリの 集計：最後 はクエリで設定した並び替えの設定を無視します。
実際はディスクに格納されている順の最後を返す仕様のようです。
データベースの場合、ディスクに格納されいてる位置は不確定ですので今回のような用途には使えません。

First および Last 関数の仕様 - hatena chips

---

方法としては、いろいろありますが、
「顧客No、商品No ごとの最終行を取得する」というを「顧客No、商品No ごとの最大日付のレコードを取得する」というのに置き換えればいいでしょう。

具体的には、顧客No、商品No でグループ化、日付を最大 の集計クエリを作成します。
この集計クエリとテーブルAからクエリを作成して、顧客No、商品No、日付で結合すればご希望の結果が得られます。
テーブルにしたいなら、このクエリからテーブル作成クエリを作成して実行すればいいですが、このクエリで結果を得られるのでテーブルにする必要性はないように思います。

クエリが２つになるのが管理上面倒だという場合は、サブクエリを使って一つのクエリにまとめることはできます。

SELECT 
  a.*
FROM
 テーブルA AS a
 INNER JOIN
 (SELECT 顧客No, 商品No, Max(日付) AS 最大日付
  FROM テーブルA
  GROUP BY 顧客No, 商品No) AS b
 ON a.顧客No=b.顧客No AND a.商品No=b.商品No And a.日付=b.最大日付;

Comments

[wataame25]

hatena19様

ご回答いただきありがとうございます。
いつもブログ拝見させていただいてます。そんな方からご回答いただけてテンション上がってます！

日付の最大で取得すると 同じ日付があった場合、複数レコード得てしまうため避けたのですが、ご提案いただいたSQLに GROUP BY を付け加えることで、欲しかった結果を得ることが出来ました。
分かり易く親切なご説明に感謝いたします。

また関係ありませんが、記述いただいたSQLから テーブル名 も AS で名称を変更できることを知りました。サブクエリもとても便利ですね。とても勉強になりました。SQLも徐々に覚えていきたいと思います。

悩んでいたので本当に助かりました。この度はありがとうございました。

[sazi]

> 日付の最大で取得すると 同じ日付があった場合、複数レコード得てしまうため避けたのですが、ご提案いただいたSQLに GROUP BY を付け加えることで、欲しかった結果を得ることが出来ました。

何をGROUP BYに追加したのでしょう？

[wataame25]

sazi様

カラムすべてを追加いたしました。
下記修正後のSQLです。

```SQL
SELECT
a.顧客No, a.商品No, a.日付, a.数量, a.単価
INTO テーブルB
FROM
テーブルA AS a
INNER JOIN
(SELECT 顧客No, 商品No, Max(日付) AS 最大日付
FROM テーブルA
GROUP BY 顧客No, 商品No) AS b
ON (a.顧客No=b.顧客No) AND (a.商品No=b.商品No) AND (a.日付=b.最大日付)
GROUP BY a.顧客No, a.商品No, a.日付, a.数量, a.単価;
```

テーブル作成は必要ないとhatena19様よりアドバイスをいただきましたが、今回はこの後に処理が続くので作成することにしました。

[sazi]

その変更は日付の重複を避ける為には何の意味もないですし、逆に行が増えているかと思います。

[wataame25]

sazi様

ご心配くださりありがとうございます。

先程改めて実データと、今回相談時に記載したテストデータ（テーブルAの５行目と同じ内容を６行目に複製 ）の両方を検証しましたが、どちらも重複行や行が増えると言う結果は得られませんでした。

勝手ではありますが、こちらhatena19様のコメント返信欄でもありますし、望んだ結果をえることが出来ましたので、一旦こちらは締めさせていただきたいと思います。申し訳ございません。

進める中で、sazi様がご心配された結果や、他思わぬ結果により躓くことがありましたら、改めてテラテイルにて相談させていただきたいと思います。機会がありましたら、その際改めてご指導いただけますと幸いです。

[hatena19]

saziさんの指摘は私も思いました。同内容で投稿しようと思ったら先にsaziさんの指摘があったのでしませんでしたが。

全フィールドをグループ化したのなら、例えば下記のようなデータの場合、
同じ日付で2件表されます。

顧客No 商品No　日付　　　　数量　単価
001　　010　　2024/03/01　2　100
001　　010　　2024/03/01　1　100

売上データなら、こういうこともあり得ますよね。