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

質問

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

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

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
(上記コードは使わないのであれば無視しても構いません。)

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

回答No.5

30246kiku · ベストアンサー

＃４です

＃４の補足にあった

> 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 後も維持されるようです。

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

回答No.4

30246kiku

＃３です

何か雰囲気ですが言いたいことがわかってきました。

＃２の補足にあったデータをテーブル「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.数<Nz(Q2.数,0),"NG",NULL) AS 判定 FROM
(SELECT ランク, Sum(担当数MAX) AS 数 FROM Tコーチ WHERE 順 Is Not Null GROUP BY ランク) AS Q1
LEFT JOIN
(SELECT ランク, Count(*) AS 数 FROM T選手 WHERE 順 Is Not Null GROUP BY ランク) AS Q2
ON Q1.ランク=Q2.ランク;

考え違いをしていたらごめんなさい。

補足

お世話になります。
一つ教えていただきたいことがあるのですが、下記の内容でランクが一緒でヒット数が変わったときに連番が1からふられるようになっているのはどこのコードでなっているのでしょうか？？今のコードはデータを昇順や降順にした後、ランクを見てAランクからBランクに切り替わる時1からふりなおしているコードですよね？(アウトプットはあっているのですが、なぜそうなるかが分かりません。)
ヒット数は見ていないようなきがするのですが…。(いまさら？って質問かもしれませんが…すみません)

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

ランク　ヒット数　 コーチ名 　担当者MAX
----------------------------------------
　A　　　10　　　　　佐藤　　　　3
　A　　　 3　　　　 　鈴木　　　　3

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

ランク　守備位置　ヒット数　選手名　　人数(連番) コーチ名
---------------------------------------------------------
　A　　　　投手　　　10　　　　加藤 1
　A　　　　捕手　　　10　　　 佐藤 2
　A　　　　投手　　　 3 　　　 田中 1　←ここでなぜ1になるのですか？どこのコードで？？
　　　　　　　　　　　　　　　　　　　　　　　(最後の3までふられない理由は？)
　B　　　　投手　　　 3　　　 高橋 1 ←ここで1になる理由はわかります。

お手数ですが、教えてください。よろしくお願いします。

回答No.3

30246kiku

＃２です

＃１で提示した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さんはすごすぎです。聞いている方がおっつかなくなるなんて…(汗)本当に感謝です。よろしくお願いいたします。

回答No.2

30246kiku

＃１です

まず、テーブルは添付図のように「TF_A」「TF_B」名にしました。

テーブル「TF_A」の担当数以上に「TF_B」に登録されているか・・・
これ、クエリで判別してみます。

SELECT Q1.ランク, IIF(Q1.数<Nz(Q2.数,0),"NG",NULL) AS 判定 FROM
(SELECT ランク, Sum(担当数MAX) AS 数 FROM TF_A GROUP BY ランク) AS Q1
LEFT JOIN
(SELECT ランク, Count(*) AS 数 FROM TF_B GROUP BY ランク) AS Q2
ON Q1.ランク=Q2.ランク;

これを表示してみると、「ランク」と「判定」の表示になり、
各ランクの担当数以上に「TF_B」の登録者が多ければ、判定 "NG" と表示されます。
VBA上で判別する時には、DLookup を使い、上記クエリ名を「Q_TF」と仮定すると

Dlookup("判定","Q_TF","判定 Is Not Null")

の戻り値が Null であれば条件を満たしています。
（ NG があれば "NG" が得られます）

※ 「TF_B」のランクに、「TF_A」のランク以外があるか・・・・は判別していません。
必要なら、それなりに修正してください。（後述VBAも同様です）

本題ですが、以下を標準モジュールに記述し実行すると、テーブル「TF_B」が更新されます。
（上記クエリで、判定欄に NG がない場合に実行するものです。エラーは端折ってます）
なお、順を決定するものの提示がなかったので、コーチ名、選手名の昇順としています。

また、ADO の方が Filter 操作等やりやすかったので DAO じゃなくなってます。

Public Sub ADO_Numbers()
　　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 TF_A ORDER BY ランク, コーチ名;"
　　rsA.Open , CurrentProject.Connection, adOpenStatic, adLockReadOnly
　　rsB.Source = "SELECT * FROM TF_B 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("人数(連番)") = 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

※ 不都合あれば、修正してください。（補足でも・・・）

お礼

お世話になっております。補足で間違いがありましたのでここで訂正させて下さい。
最後の質問で"守備位置"はここでは無視(消)してください。実際は2つ以上で3つ位で抽出後連番をふりたかったので記載したのですが、消すのを忘れていました。よろしくお願いいたします。

補足

有難うございます。本当に感謝しています。また、下記内容についてご教授いただけますでしょうか。
お願いいたします。

> 実際はAランク以外に複数条件で絞り込んだ後に次から次へと連番をふることができれば最高
下記の場合についてを伝えたかったのですが、言葉足らずですみません。

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

ランク　　　選手名　　人数(連番) コーチ名
--------------------------------------------

　　　　　↓

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

ランク　守備位置　ヒット数　選手名　　人数(連番) コーチ名
---------------------------------------------------------

上記の様に守備位置とヒット数を追加した場合、ランクと守備位置、ヒット数のように複数で
フィルタをかけた後に連番を振りたかったということだったんです。

ランク　守備位置　ヒット数　選手名　　人数(連番) コーチ名
---------------------------------------------------------
　A　　　　投手　　　3　　　　加藤
　A　　　　捕手　　　10　　　 佐藤
　A　　　　投手　　　3　　　　田中
　A　　　　内野　　　10　　　 鈴木
　B　　　　投手　　　3　　　　高橋
　B　　　　捕手　　　10　　　 中村
　B　　　　投手　　　3　　　　高橋
　B　　　　捕手　　　10　　　 中村

となっていたとして
ランクだけでなくランクAの人でヒット数10のグループとランクAの人でヒット数3のグループと
ランクだけでなくランクBの人でヒット数10のグループとランクBの人でヒット数3のグループをフィルタした後連番したいんですが、どうすればよいでしょうか。拙い文で申し訳ありませんが、よろしくお願い致します。下記コードが肝だと思うのですが…。お手数お掛けしますがご教授お願いいたします。

　　　If (sS <> rsB("ランク")) Then
　　　　　　sS = rsB("ランク")
　　　　　　rsA.Filter = "ランク='" & sS & "'"
。

回答No.1

30246kiku

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

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

１）上記で、どのコーチから割り当てるか・・・この規則には何が使えますか？

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

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

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

補足

お世話になります。
説明不足すみません。補足は下記となります。お手上げ状態なのでぜひともお力をお貸しください。
よろしく願いいたします。

＞１）上記で、どのコーチから割り当てるか・・・この規則には何が使えますか？
今考えているのは上から下を優先順位と考えています。他に良い手があればお願いします。

＞２）上記で割り当てできる Aランクの方は４人までと思われますが、
T_選手マスタ(テーブルB)　で、Aランクの方が４人を超えた場合にはどうするのですか？
超えないと確認した後、マクロを回したいと考えています。間違って超えてしまったらメッセージとして表示するのもいいかもしれません。

＞また、Aランクの方で割り当て優先・・・・の様なものはあるのでしょうか？
優先順位はありません。ただ実際はAランク以外に複数条件で絞り込んだ後に次から次へと連番をふることができれば最高だと思っています。(イメージですが・・・)

その他良いアイデアがあれば教えていただければ幸いです。

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