ACCESS＋SQL Serverで主キーを自動採番する

ACCESS＋SQL Serverで主キーを自動採番する

ACCESSを独学で勉強を開始しているものです。
顧客管理のようなものを作りたいと思っているのですが、
オートナンバーではない主キー（顧客コード）を設定しているテーブルがあります。
顧客コードの構成はアルファベット1文字＋数字8桁です。
そのうち、数字は西暦2桁＋月2桁＋日2桁＋下2桁は連番です。

ACCESS2007プロジェクトで、SQL Server 2005 Express Editionへ接続しテーブルは作ってあり、既に顧客データが10件ほど入っています。

新規に顧客データを追加する際に、必要事項を入力し、登録ボタンを押した段階で顧客コードを自動生成し、重複チェックし、採番したいと考えています。

アルファベットは必要事項入力時に入力済み、日付もその日の日付を6桁設定し、とりあえず「01」と下2桁を採番して、現在のデータに同じものがないかチェックします。

そして、同じものがあれば「01」＋1を繰り返しながらチェックしていくというようなコードはどのようにすればいいでしょうか？

わかりづらいとは思いますが、よろしくお願いします。

No.1 ベストアンサー 回答者： bin-chan 回答日時： 2010/04/03 11:53

select max(mid([顧客コード], 8, 2)) as [連番最大値]

from 顧客テーブル
where [顧客コード] like アルファベット1文字 & 西暦2桁 & 月2桁 & 日2桁
group by mid([顧客コード], 1, 8) <--- left([顧客コード], 8)でも可
;

これで[連番最大値]が取得できたなら１加算。取得できなければ1を代入。
formar([次の連番], "00")で整形して連結。

select投げる前に、連番までのキー情報を作成して変数に入れとくほうがよさそうですね。

この回答へのお礼

ありがとうございます！
なるほど、私のおバカな頭では、まず１をセットしてイコールじゃなくなるまで＋１しながらループする・・・なんて無駄な処理を考えていたので、SQL一発でスッキリしてすごく参考になりました。

ちなみに、SQL ServerではMID関数がありませんでしたので、SUBSTRING関数を代わりに使用しました。あと、ワイルドカードも「＊」ではなく「％」でした。
最終的なSQLは以下のようにして動作確認しました。

SELECT MAX(SUBSTRING([顧客コード],8,2)) AS [連番最大値]
FROM 顧客テーブル
WHERE ([顧客コード] LIKE 'アルファベット1+日付6桁%')
GROUP BY SUBSTRING([顧客コード],1,8)

有り難うございました。

ちなみに、GROUP BY がないと、どのようなときに困りますか？
まだSQL勉強不足でわからない事だらけなのですが。

お礼日時：2010/04/03 12:55

No.3 回答者： bin-chan 回答日時： 2010/04/03 15:49

＃１・＃２です。

さらなる訂正があります。
mid(substrでしたね）で、8という値は誤りです、。正しくは 7。
A100402NN の先頭ですよね。8桁だとA1004020まで拾うので、11件目で重複が生じてしまいます。

> ちなみに、GROUP BY がないと、どのようなときに困りますか？
今回は無くてもOKでしたね。ここも勘違い。
「同日のアルファベット別」や「同一アルファベットの日付別」といった一覧作成時に用いてください。

この回答への補足

> mid(substrでしたね）で、8という値は誤りです、。正しくは 7。

SUBSTRINGしか使用してなかったので、気づいていませんでしたｗ

> 「同日のアルファベット別」や「同一アルファベットの日付別」といった一覧作成時に用いてください。

なるほど、そういう時に利用できるのですね。
覚えておきます。有り難うございました。

補足日時：2010/04/03 16:03

No.2 回答者： bin-chan 回答日時： 2010/04/03 12:00

＃１です。

likeにワイルドカードの記述が不足してました。

where [顧客コード] like アルファベット1文字 & 西暦2桁 & 月2桁 & 日2桁 & "*"

group by mid([顧客コード], 1, 8) <--- left([顧客コード], 8)でも可
とは
group by mid([顧客コード], 1, 8)
または
group by left([顧客コード], 8)
です。念のため。