【最大10000ポイント】当たる!!質問投稿キャンペーン!

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

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

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

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

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

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

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

このQ&Aに関連する最新のQ&A

A 回答 (3件)

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


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

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

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

ありがとうございます!
なるほど、私のおバカな頭では、まず1をセットしてイコールじゃなくなるまで+1しながらループする・・・なんて無駄な処理を考えていたので、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

#1・#2です。



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

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

この回答への補足

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

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

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

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

補足日時:2010/04/03 16:03
    • good
    • 0

#1です。


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

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

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

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QAccessの最初にメニュー画面を出したい

初心者ながら、Accessを使って商品管理をしております。今度、売上げ管理と仕入れ先管理なども作成していく予定でおります。
そこで、Accessのファイルを開いた時点で、商品管理メニュー、売上管理メニュー…といった感じで、最初に見たい項目を選択できるようにしたいのですが、VBA等を使っているのでしょうか?
せっかく作るので、出来る限り使いやすく、見やすくしたいのです。私が持っている本には、それらに関する記述が見あたりません。
ネットを検索したのですが、どうも的はずれな検索をしているのか見つかりません。
どなたか、参考HPでも構いません、基本となる作成方法をご教授願えないでしょうか?
宜しくお願い申し上げます。

Aベストアンサー

No.1の方の回答の方法と違って応用はききませんが、
その分シンプルな方法として・・・

1)メニューで「ツール(T)→起動時の設定(U)」と選択
2)表示される「起動時の設定」ダイアログの右上にある、
 「フォーム/ページの表示(O)」で、最初に表示させたい
 フォームを指定

以上でOKです。

Q登録後、呼び出し元ページに戻るには

こんにちは。
ASP.NET2.0を使っています
データベースを閲覧したり修正したりするページを作成しています。

閲覧画面AとBがあり、どちらのページからも修正画面Cに行くことができます。
画面A→画面C
画面B→画面C
ここで修正画面Cでデータを修正後、呼び出されたページに戻りたいのですが、呼び出されたページを取得する方法に困っています
修正後の画面に戻りたいので、javascriptのhistory.back()は使えませんし、Request.UrlReferrerは修正時にポストバックされるからか修正画面Cのアドレスを示してしまっています。

なにか方法はないものでしょうか?よろしくお願いします。

Aベストアンサー

ページの遷移をどのように行っていますか?

ボタンのクリックイベントで Server.Transferで遷移しているならPreviousPageで取得できます

画面Cのメンバーに
dim myPreVious as Page
を追加

画面Cの Page_Lodeイベントで
if IsPostback = FALSE then
  if PreviousPage isnot nothing then
    Session("Previous") = PreviousPage
  end if
Else
  myPrevious = Seccion("Previous")
End if

画面Cの呼び出し元へ戻る場合
if myPrevious isnot Nothing then
  Session("Previous") = Nothing
  Response.Redirect( myPrevious.AppRelativeVirtualPath)
end if
といった具合いで ・・・

QACCESS 複雑な自動採番

ACCESS2007

どなたかよろしくお願いします。
工事台帳をACCESSにて作成しようとしております。
工事管理の為、一つ工事に工事CDを作成します。
得意先CD+401~499までの数字の組み合わせです。

例)AX工務店 得意先CD=AX AX401、AX402...AX499
  BX建設   得意先CD=BX BX401、BX402...BX499

上記のように、得意先CDごとに401~499の工事CDを持たせたいのです。
入力フォームにて得意先CD入力後、工事CDが取得できるように

Private Sub 得意先CD_AfterUpdate()
Me![工事CD] = Me![得意先CD] & Format(DCount("工事CD", "T工事台帳") + 1, "400")
End Sub

と記述しましたが、得意先CDにかかわらず番号のみが+1され続けるため、
望む形ではありません。
どのようにしたらよいでしょうか?

また、現在3つの事業部があり一つの現場に最大3つの工事CDができることもあります。
その場合、"D"または"S"をつけます(事業部の頭文字です)
AX401、DAX402、SAX403など、同一の現場であっても数字部分は重複させません。
その場合、2段階で採番(まどろっこしいですが)させようかと思ってますが、
良い方法がありますでしょうか?

なにとぞよろしくお願いします。

ACCESS2007

どなたかよろしくお願いします。
工事台帳をACCESSにて作成しようとしております。
工事管理の為、一つ工事に工事CDを作成します。
得意先CD+401~499までの数字の組み合わせです。

例)AX工務店 得意先CD=AX AX401、AX402...AX499
  BX建設   得意先CD=BX BX401、BX402...BX499

上記のように、得意先CDごとに401~499の工事CDを持たせたいのです。
入力フォームにて得意先CD入力後、工事CDが取得できるように

Private Sub 得意先CD_AfterUpd...続きを読む

Aベストアンサー

得意先CD は、2文字と仮定します。

以下でどうなりますか。(見難かったらすみません)

Me![工事CD] = Me![得意先CD] _
      & Format( _
        Nz( _
          DMax("Val(Right(工事CD,3))" _
            , "T工事台帳" _
            , "工事CD Like '*" & Right(Me![得意先CD], 2) & "*'") _
        , 400) + 1 _
      , "000")

やっている事は、
DMax で、Me![得意先CD] の右側2文字の 工事CD を「T工事台帳」で探して・・・
あったら 工事CD の右側3文字を数値にした最大を求めておきます。
対象のものが無かったら初期値の 400 にするように Nz を使っておきます。
その値を +1 して、3桁表示に Format を使い、元々の Me![得意先CD] に付ける。

Me![得意先CD] が、AX、DAX、SAX なら、 '*AX*' で探します。
右側3文字を数値にした最大を求めるので、AX、DAX、SAX の区別はありません。

Me![得意先CD] は2文字以上等、チェックが必要になると思います。
また、上記では 499 を超える判別はしていないので・・・


以下は雰囲気で
事業部の「D」「S」を、別途「txt事業部」に設けたとすると

Me![工事CD] = Me![txt事業部] & Me![得意先CD] _
      & Format( _
        Nz( _
          DMax("Val(Right(工事CD,3))" _
            , "T工事台帳" _
            , "工事CD Like '*" & Me![得意先CD] & "*'") _
        , 400) + 1 _
      , "000")

これであれば、得意先CD は2文字・・・という制限はなくなるかと

得意先CD は、2文字と仮定します。

以下でどうなりますか。(見難かったらすみません)

Me![工事CD] = Me![得意先CD] _
      & Format( _
        Nz( _
          DMax("Val(Right(工事CD,3))" _
            , "T工事台帳" _
            , "工事CD Like '*" & Right(Me![得意先CD], 2) & "*'") _
        , 400) + 1 _
      , "000")

やっている事は、
DMax で、Me![得意先CD] の右側2文字の 工事CD を「T工事台帳」で探して・・・
あ...続きを読む


人気Q&Aランキング