条件別に連番を振る方法を教えてください。

Question

お世話になります。
下記の様な条件による連番をふるマクロを組みたいと考えており、
色々考えてたのですがやり方が分からなく困っています。多少複雑だとは思いますが、すみませんが、詳しい方よろしくお願い致します。

内容として、例えばですが野球チームの選手をランク別にあるコーチに割り当てたいとします。

T_コーチマスタ(テーブルＡ)
ランク　　コーチ名　　担当数MAX
--------------------------------
　A　　　　鈴木　　　　　2←Aランク選手に割り当てたいコーチ
　A　　　　橋本　　　　　2←Aランク選手に割り当てたいコーチ
　B　　　　田中　　　　　2←Bランク選手に割り当てたいコーチ
　B　　　　青木　　　　　3←Bランク選手に割り当てたいコーチ
　C　　　　佐藤　　　　　2←Cランク選手に割り当てたいコーチ

(補足：ランク順に昇順しています)

T_選手マスタ(テーブルB)

ランク　　　選手名　　人数(連番)   コーチ名
--------------------------------------------
　A　　　　　加藤
　B　　　　　山本 
　B　　　　　渡辺 
　B　　　　　吉田 
　A　　　　　佐々木
　A　　　　　木村 
　C　　　　　太田

(＊得たい結果)

ランク　　　選手名　　担当数(連番)  コーチ名
--------------------------------------------
　A　　　　　加藤　　　　　1　　　　　　　鈴木
　A　　　　　佐々木　　　 2　　　　　　　鈴木
　A　　　　　木村　　　　　1　　　　　　　橋本
　B　　　　　山本　　　　　1　　　　　　　田中
　B　　　　　渡辺　　　　　2　　　　　　　田中
　B　　　　　吉田　　　　　1　　　　　　　青木

※選手ランク(T_選手マスタ)別に、コーチ名と連番を割り当てたいのですが、コーチにも教えられる人数が限られており、担当数MAXまでしか連番をふれません。担当数を超えると次(下)のコーチ(ランク別)に割り当てたいです。担当者MAXがなく一人のコーチが全員分連番をふれればいいのですが・・・。

連番を振るだけのコードだけは分かったのですが、どうすれば上手い具合にできますでしょうか？？(SQLで方法がありますか？)

'連番のみのコード
Sub DAO_numbers()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fd As DAO.Field
    Dim SQL As String
    Dim inCount As Integer
    Set db = CurrentDb()

SQL = "SELECT * FROM T_選手マスタ  ORDER BY ランク"
    Set rs = db.OpenRecordset(SQL, dbOpenDynaset)
    Set fd = rs.Fields("人数")
    If rs.BOF = False Then
        rs.MoveFirst
        inCount = 1
        Do Until rs.EOF
            rs.Edit
                fd = inCount
            rs.Update
            inCount = inCount + 1
            rs.MoveNext
        Loop
    End If
    rs.Clone: Set rs = Nothing
    db.Close: Set db = Nothing
End Sub
(上記コードは使わないのであれば無視しても構いません。)

以上よろしくお願い致します。

30246kiku · Accepted Answer

＃４です

＃４の補足にあった

> T_コーチマスタ(テーブルＡ)

> ランク　ヒット数　 コーチ名 　担当者MAX
> ----------------------------------------
> 　A　　　10　　　　　佐藤　　　　3
> 　A　　　 3　　　　 　鈴木　　　　3
 
は構成がおかしい(?)ので、上記「ヒット数」は考えないことにします。

＃２でのVBAで

While (Not rsB.EOF)
　　　　If (sS <> rsB("ランク")) Then　' ★
　　　　　　sS = rsB("ランク")　' ★
　　　　　　rsA.Filter = "ランク='" & sS & "'"　' ★
　　　　　　i = 1　' ★
　　　　End If　' ★
　　　　rsB("人数(連番)") = i
　　　　rsB("コーチ名") = rsA("コーチ名")
　　　　rsB.Update
　　　　i = i + 1　' ★☆★
　　　　If (i > rsA("担当数MAX")) Then　' ★☆★
　　　　　　rsA.MoveNext　' ★☆★
　　　　　　i = 1　' ★☆★
　　　　End If　' ★☆★
　　　　rsB.MoveNext
　　Wend

★：選手側「ランク」が変わったか判別して、
　変わっていたら、その「ランク」のコーチを求め直して・・・
　連番を 1 に

★☆★：連番をカウントアップして、
　そのコーチの「担当数MAX」を超えたら次のコーチにして、連番を 1 に

なので

> (最後の3までふられない理由は？)

確認された時の「担当数MAX」が 2 だったのでは・・・と思います。
（ 3 を 2 にする処理は入っていないので）

※ 以前は「担当数MAX」でしたが、＃４補足では「担当者MAX」になってますね
テーブルは新しく作って変えたけど、VBAで参照しているテーブルは前のまま・・・とか？

この処理での大前提は、選手側のデータは「ランク」で並んでいる必要があります。
「ランク」で並んでいるのなら、そのランク内の並びは？？？
それを指定してあげればよいです。（それが＃３の冒頭に記述したものになります）
割り当てしたい順に並んでいる・・・・並んだ後は「ヒット数」等、確認はしません。
（なお、この前提以前に、１選手１データであること・・・・があります）

コーチの方も、コーチの並び順を指定しておけば、その順は Filter 後も維持されるようです。

回答になっていたでしょうか？

30246kiku · Answer

＃３です何か雰囲気ですが言いたいことがわかってきました。＃２の補足にあったデータをテーブル「TF_C」としましたが、その中のランク「B」では、選手は２名なんだけどデータは４・・・・と ??? であれば、データの持ち方がおかしいですね以下一例（自信なし） ★ Tコーチ　（コーチ用テーブル）コーチID（長整数：主キー）ランク（テキスト）順（長整数）名前（テキスト）担当数MAX（長整数）サンプルコーチID　ランク　順　　名前　担当数MAX 100　　　　A　　　　2　　　橋本　　　2 103　　　　A　　　　　　　　遠藤　　　2 104　　　　A　　　　1　　　鈴木　　　2 「順」が Null なら現在コーチから外れている「順」が埋まっていれば、昇順で選手に割り当てていく ★ T選手　（選手用テーブル）選手ID（長整数：主キー）ランク（テキスト）順（長整数）　ランク内の順位名前（テキスト）守備位置（テキスト）コーチID（長整数）サンプル選手ID　ランク　順　名前　　守備位置　コーチID 1001　　　A　　　2　　佐々木　　捕手 1002　　　A　　　1　　加藤　　　投手 1003　　　A　　　　　　佐藤　　　投手 1004　　　A　　　3　　木村　　　内野「順」が Null なら現在選手から外れている「順」が埋まっていれば、昇順でコーチを割り当てていく ★ T試合　（試合用テーブル）試合ID（長整数）日時（日付/時刻）対戦相手（テキスト）場所（テキスト） ★ T成績 an（オートナンバ）試合ID（長整数）選手ID（長整数）ヒット数（長整数）上記のテーブル構成にしたとして、＃２で提示したVBAにはほとんど変更はなく以下（T選手テーブルにコーチIDを割付けていくもの） Public Sub ADO_Members() 　　Dim rsA As New ADODB.Recordset 　　Dim rsB As New ADODB.Recordset 　　Dim sS As String 　　Dim i As Long 　　sS = "" 　　rsA.Source = "SELECT * FROM Tコーチ WHERE 順 Is Not Null ORDER BY ランク, 順;" 　　rsA.Open , CurrentProject.Connection, adOpenStatic, adLockReadOnly 　　rsB.Source = "SELECT * FROM T選手 WHERE 順 Is Not Null ORDER BY ランク, 順;" 　　rsB.Open , CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic 　　While (Not rsB.EOF) 　　　　If (sS <> rsB("ランク")) Then 　　　　　　sS = rsB("ランク") 　　　　　　rsA.Filter = "ランク='" & sS & "'" 　　　　　　i = 1 　　　　End If 　　　　rsB("コーチID") = rsA("コーチID") 　　　　rsB.Update 　　　　i = i + 1 　　　　If (i > rsA("担当数MAX")) Then 　　　　　　rsA.MoveNext 　　　　　　i = 1 　　　　End If 　　　　rsB.MoveNext 　　Wend 　　rsB.Close 　　rsA.Close End Sub なお、上記テーブル構成にした場合、T選手で順が埋められている人に対して、ランク、順を元に通番を求めるクエリは以下 SELECT Q1.選手ID, Count(*) AS 通番 FROM (SELECT 選手ID, ランク, 順 FROM T選手 WHERE 順 Is Not Null) AS Q1 INNER JOIN (SELECT 選手ID, ランク, 順 FROM T選手 WHERE 順 Is Not Null) AS Q2 ON (Q1.ランク>Q2.ランク) OR (Q1.ランク=Q2.ランク AND Q1.順>=Q2.順) GROUP BY Q1.選手ID; また、判定のクエリは以下 SELECT Q1.ランク, IIF(Q1.数

30246kiku · Answer

＃２です

＃１で提示したVBAでは rsB を得た時の順に、コーチを順に割り当てるものになっています。
なので、

>　　rsB.Source = "SELECT * FROM TF_B ORDER BY ランク, 選手名;"

この、ランク昇順、選手名昇順部分を変更すれば良さそうです。
ランク昇順、ヒット数降順、選手名昇順であれば

rsB.Source = "SELECT * FROM TF_B ORDER BY ランク, ヒット数 DESC, 選手名;"

※ 連番っていうイメージは、各コーチが担当できるMAX内でのものですよね。
＃１でのVBAでは、テーブル「TF_B」を更新しており、
「人数(連番)」と、得たい結果の「担当数(連番)」の解釈に間違いあったかも??
もし「人数(連番)」が単純な通番であれば、ご質問にあった DAO での処理で良いと思います。

※ 補足部分にあったコード部分ですが、
テーブル「TF_B」を得られた順に見ていく時にランクが変わったら、
そのランクを担当するコーチを求めておくものになります。
例えば、ランク「A」から「B」に変わったら、ランク「B」担当のコーチに・・・
で、選手の方のコーチに担当コーチを転記して・・・
で、カウントアップして、担当コーチの「担当数MAX」を超えたら次のコーチに・・・

> ランクだけでなくランクAの人でヒット数10のグループとランクAの人でヒット数3のグループと
> ランクだけでなくランクBの人でヒット数10のグループとランクBの人でヒット数3のグループをフィルタした後連番したいんですが

ここが今一つ理解できていないのですが、
各ランク内でグループ化した際、担当コーチを、また初めからふり直す？？？

補足にあったデータをテーブル「TF_C」として作成し、＃１VBAの以下を変更した結果が添付図になります。
添付図下側はコーチの割り当て順がわかりやすいように、ランク昇順、ヒット数降順、選手名昇順で表示しています。

rsA.Source = "SELECT * FROM TF_A ORDER BY ランク, an;"
　　rsA.Open , CurrentProject.Connection, adOpenStatic, adLockReadOnly
　　rsB.Source = "SELECT * FROM TF_C ORDER BY ランク, ヒット数 DESC, 選手名;"
　　rsB.Open , CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

この結果と違うものが欲しいのでしょうか・・・補足ください

30246kiku · Answer

＃１ですまず、テーブルは添付図のように「TF_A」「TF_B」名にしました。テーブル「TF_A」の担当数以上に「TF_B」に登録されているか・・・これ、クエリで判別してみます。 SELECT Q1.ランク, IIF(Q1.数 rsB("ランク")) Then 　　　　　　sS = rsB("ランク") 　　　　　　rsA.Filter = "ランク='" & sS & "'" 　　　　　　i = 1 　　　　End If 　　　　rsB("人数(連番)") = i 　　　　rsB("コーチ名") = rsA("コーチ名") 　　　　rsB.Update 　　　　i = i + 1 　　　　If (i > rsA("担当数MAX")) Then 　　　　　　rsA.MoveNext 　　　　　　i = 1 　　　　End If 　　　　rsB.MoveNext 　　Wend 　　rsB.Close 　　rsA.Close End Sub > ＞１）上記で、どのコーチから割り当てるか・・・この規則には何が使えますか？ > 今考えているのは上から下を優先順位と考えています。他に良い手があればお願いします。上記処理では、コーチ名の昇順にしていましたが、提示した「TF_A」の「an」（オートナンバ）を用いる手もあります。（ an はオートナンバにしていましたが、自分で採番するようにしても良いかと・・・）その際には、　　rsA.Source = "SELECT * FROM TF_A ORDER BY ランク, コーチ名;" ↓ 　　rsA.Source = "SELECT * FROM TF_A ORDER BY ランク, an;" とすれば、"上から下" の意味合いになると思います。 > 実際はAランク以外に複数条件で絞り込んだ後に次から次へと連番をふることができれば最高これについては、イメージできてません。 ※ フィールド名に ( ) 等使わない方が後々良いかも・・・・ Access データベースでの作業時に使用すべきでない特殊文字 http://support.microsoft.com/kb/826763/ja ※ 不都合あれば、修正してください。（補足でも・・・）

30246kiku · Answer

補足をお願いしていいですか
（補足されても、回答出来るのかは？？・・・？？）

T_コーチマスタ(テーブルＡ)
ランク　　コーチ名　　担当数MAX
--------------------------------
　A　　　　鈴木　　　　　2←Aランク選手に割り当てたいコーチ
　A　　　　橋本　　　　　2←Aランク選手に割り当てたいコーチ
 
１）上記で、どのコーチから割り当てるか・・・この規則には何が使えますか？

２）上記で割り当てできる Aランクの方は４人までと思われますが、
T_選手マスタ(テーブルB)　で、Aランクの方が４人を超えた場合にはどうするのですか？

また、Aランクの方で割り当て優先・・・・の様なものはあるのでしょうか？
（加藤、佐々木、木村 の順はどう決めるのでしょうか？）

※ 他に曖昧な部分があるのであれば、これこれは△△順・・・を補足してもらえませんか？？

条件別に連番を振る方法を教えてください。

＃４です

＃３です

この回答への補足

＃２です

この回答への補足

＃１です

この回答への補足

補足をお願いしていいですか

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング