[Access]別テーブルの最大値以降の連番振り

質問

過去の質問を色々調べたのですが、結局以下の連番の振り方が分からず・・・困っています。
どなたか助けて下さい。お願いいたします。

テーブルA（蓄積されている過去テーブル）
伝票番号　　顧客NO　　注文日
001 111111 2012/11/10
001 111111 2012/11/10
002 222222 2012/11/25
002 222222 2012/11/25
002 222222 2012/11/25
003 333333 2012/12/31

テーブルB（新規にインポートするテーブル）
004 444444 2013/02/01
004 444444 2013/02/01
005 222222 2013/02/15
005 222222 2013/02/15
006 555555 2013/02/15
006 555555 2013/02/15

上記のように、テーブルBをインポートした時に、伝票番号を「004,004,005・・・」という風に
顧客NO毎に連番を振りたいです。
DmaxやDcount関数を使う事はなんとなく分かったのですが、うまくいきません。
出来れば、クエリで実現したいと思ってます。
テーブルAにある顧客がテーブルBに出てきたとしても、関係なく連番が振れれば問題無いので、
テーブルAからは最大値＋１の値だけ取れればいいです。

宜しくお願いいたします！！

回答No.1

30246kiku

> 出来れば、クエリで実現したいと思ってます。

VBA でやった方が簡単かと思いますが、クエリで挑戦してみます。

テーブルAの名前を「★★」、テーブルBの名前を「■■」
伝票番号：テキスト
と、仮定します。

クエリのSQLビューに以下を記述し実行してみます。

INSERT INTO ★★(伝票番号, 顧客NO, 注文日)
SELECT FORMAT(Val(StrConv(Nz(DMax("伝票番号","★★"),"0"),8))+Q3.CT,"000"),
Q0.顧客NO, Q0.注文日 FROM ■■ AS Q0 INNER JOIN
(SELECT Q1.顧客NO, Q1.注文日, Count(*) AS CT FROM
(SELECT 顧客NO, 注文日 FROM ■■ GROUP BY 顧客NO, 注文日) AS Q1
INNER JOIN
(SELECT 顧客NO, 注文日 FROM ■■ GROUP BY 顧客NO, 注文日) AS Q2
ON (Q1.注文日>Q2.注文日) OR (Q1.注文日=Q2.注文日 AND Q1.顧客NO>=Q2.顧客NO)
GROUP BY Q1.顧客NO, Q1.注文日) AS Q3
ON Q0.顧客NO=Q3.顧客NO AND Q0.注文日=Q3.注文日
ORDER BY Q0.注文日, Q0.顧客NO;

やっている事は、まず Q1 Q2 はテーブル■■を 顧客NO, 注文日 でグループ化して、
Q3 として自分の順番（ CT ）を求めています。
順番は、注文日、同じ注文日なら顧客NO　これの昇順としています。
その Q3 を大元の ■■ と結び付けて・・・・

伝票番号の最大値を求めるところは、
DMax で最大値を求め（空だったら "0" に）
念のため半角に変換（ StrConv で 8 vbNarrow ）したものを Val で数値に
その数値に、Q3 で求めた順番を足し込んだものを Format で "000" ３桁に・・・

※ 処理性能はわかりません

※ 解釈に間違いがあるかも
ワーキング用に読み込んだ ■■ を ★★ に追加する・・・・上記内容はそうです

もし、テーブル■■上で伝票番号を設定するのなら

SELECT Q1.顧客NO, Q1.注文日, Count(*) AS CT FROM
(SELECT 顧客NO, 注文日 FROM ■■ GROUP BY 顧客NO, 注文日) AS Q1
INNER JOIN
(SELECT 顧客NO, 注文日 FROM ■■ GROUP BY 顧客NO, 注文日) AS Q2
ON (Q1.注文日>Q2.注文日) OR (Q1.注文日=Q2.注文日 AND Q1.顧客NO>=Q2.顧客NO)
GROUP BY Q1.顧客NO, Q1.注文日;

の内容で、クエリ▲▲を作っておきます。
これは、テーブル■■内の順番を求めるだけ・・・
この▲▲を DLookup で参照して、以下の更新クエリを・・・・

UPDATE ■■ SET 伝票番号 =
FORMAT(Val(StrConv(Nz(DMax("伝票番号","★★"),"0"),8))
+ DLookup("CT","▲▲","顧客NO='" & 顧客NO & "' AND 注文日=#" & 注文日 & "#")
,"000");

※ 上記は、顧客NO（テキスト）、注文日（日付/時刻）と仮定してます。

※ これも同様に、処理性能はわかりません