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 · ベストアンサー

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勉強不足でわからない事だらけなのですが。

回答No.3

bin-chan

＃１・＃２です。

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

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

補足

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

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

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

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

回答No.2

bin-chan

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

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

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