お世話になります。
下記の様な条件による連番をふるマクロを組みたいと考えており、
色々考えてたのですがやり方が分からなく困っています。多少複雑だとは思いますが、すみませんが、詳しい方よろしくお願い致します。
内容として、例えばですが野球チームの選手をランク別にあるコーチに割り当てたいとします。
T_コーチマスタ(テーブルA)
ランク コーチ名 担当数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ベストアンサー
- 回答日時:
#4です
#4の補足にあった
> T_コーチマスタ(テーブルA)
> ランク ヒット数 コーチ名 担当者MAX
> ----------------------------------------
> A 10 佐藤 3
> A 3 鈴木 3
は構成がおかしい(?)ので、上記「ヒット数」は考えないことにします。
#2での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」でしたが、#4補足では「担当者MAX」になってますね
テーブルは新しく作って変えたけど、VBAで参照しているテーブルは前のまま・・・とか?
この処理での大前提は、選手側のデータは「ランク」で並んでいる必要があります。
「ランク」で並んでいるのなら、そのランク内の並びは???
それを指定してあげればよいです。(それが#3の冒頭に記述したものになります)
割り当てしたい順に並んでいる・・・・並んだ後は「ヒット数」等、確認はしません。
(なお、この前提以前に、1選手1データであること・・・・があります)
コーチの方も、コーチの並び順を指定しておけば、その順は Filter 後も維持されるようです。
回答になっていたでしょうか?
No.4
- 回答日時:
#3です
何か雰囲気ですが言いたいことがわかってきました。
#2の補足にあったデータをテーブル「TF_C」としましたが、
その中のランク「B」では、選手は2名なんだけどデータは4・・・・と ???
であれば、データの持ち方がおかしいですね
以下一例(自信なし)
★ 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(長整数)
ヒット数(長整数)
上記のテーブル構成にしたとして、#2で提示した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_コーチマスタ(テーブルA)
ランク ヒット数 コーチ名 担当者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
- 回答日時:
#2です
#1で提示したVBAでは rsB を得た時の順に、コーチを順に割り当てるものになっています。
なので、
> rsB.Source = "SELECT * FROM TF_B ORDER BY ランク, 選手名;"
この、ランク昇順、選手名昇順部分を変更すれば良さそうです。
ランク昇順、ヒット数降順、選手名昇順であれば
rsB.Source = "SELECT * FROM TF_B ORDER BY ランク, ヒット数 DESC, 選手名;"
※ 連番っていうイメージは、各コーチが担当できるMAX内でのものですよね。
#1でのVBAでは、テーブル「TF_B」を更新しており、
「人数(連番)」と、得たい結果の「担当数(連番)」の解釈に間違いあったかも??
もし「人数(連番)」が単純な通番であれば、ご質問にあった DAO での処理で良いと思います。
※ 補足部分にあったコード部分ですが、
テーブル「TF_B」を得られた順に見ていく時にランクが変わったら、
そのランクを担当するコーチを求めておくものになります。
例えば、ランク「A」から「B」に変わったら、ランク「B」担当のコーチに・・・
で、選手の方のコーチに担当コーチを転記して・・・
で、カウントアップして、担当コーチの「担当数MAX」を超えたら次のコーチに・・・
> ランクだけでなくランクAの人でヒット数10のグループとランクAの人でヒット数3のグループと
> ランクだけでなくランクBの人でヒット数10のグループとランクBの人でヒット数3のグループをフィルタした後連番したいんですが
ここが今一つ理解できていないのですが、
各ランク内でグループ化した際、担当コーチを、また初めからふり直す???
補足にあったデータをテーブル「TF_C」として作成し、#1VBAの以下を変更した結果が添付図になります。
添付図下側はコーチの割り当て順がわかりやすいように、ランク昇順、ヒット数降順、選手名昇順で表示しています。
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
- 回答日時:
#1です
まず、テーブルは添付図のように「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
> >1)上記で、どのコーチから割り当てるか・・・この規則には何が使えますか?
> 今考えているのは上から下を優先順位と考えています。他に良い手があればお願いします。
上記処理では、コーチ名の昇順にしていましたが、
提示した「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
※ 不都合あれば、修正してください。(補足でも・・・)
この回答への補足
有難うございます。本当に感謝しています。また、下記内容についてご教授いただけますでしょうか。
お願いいたします。
> 実際は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 & "'"
。
お世話になっております。補足で間違いがありましたのでここで訂正させて下さい。
最後の質問で"守備位置"はここでは無視(消)してください。実際は2つ以上で3つ位で抽出後連番をふりたかったので記載したのですが、消すのを忘れていました。よろしくお願いいたします。
No.1
- 回答日時:
補足をお願いしていいですか
(補足されても、回答出来るのかは??・・・??)
T_コーチマスタ(テーブルA)
ランク コーチ名 担当数MAX
--------------------------------
A 鈴木 2←Aランク選手に割り当てたいコーチ
A 橋本 2←Aランク選手に割り当てたいコーチ
1)上記で、どのコーチから割り当てるか・・・この規則には何が使えますか?
2)上記で割り当てできる Aランクの方は4人までと思われますが、
T_選手マスタ(テーブルB) で、Aランクの方が4人を超えた場合にはどうするのですか?
また、Aランクの方で割り当て優先・・・・の様なものはあるのでしょうか?
(加藤、佐々木、木村 の順はどう決めるのでしょうか?)
※ 他に曖昧な部分があるのであれば、これこれは△△順・・・を補足してもらえませんか??
この回答への補足
お世話になります。
説明不足すみません。補足は下記となります。お手上げ状態なのでぜひともお力をお貸しください。
よろしく願いいたします。
>1)上記で、どのコーチから割り当てるか・・・この規則には何が使えますか?
今考えているのは上から下を優先順位と考えています。他に良い手があればお願いします。
>2)上記で割り当てできる Aランクの方は4人までと思われますが、
T_選手マスタ(テーブルB) で、Aランクの方が4人を超えた場合にはどうするのですか?
超えないと確認した後、マクロを回したいと考えています。間違って超えてしまったらメッセージとして表示するのもいいかもしれません。
>また、Aランクの方で割り当て優先・・・・の様なものはあるのでしょうか?
優先順位はありません。ただ実際はAランク以外に複数条件で絞り込んだ後に次から次へと連番をふることができれば最高だと思っています。(イメージですが・・・)
その他良いアイデアがあれば教えていただければ幸いです。
以上、よろしくお願いいたします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
「いち~として」の「いち」と...
-
表彰式で表彰される人は、何と...
-
体育祭の障害物競争…。
-
人名や地名の「渕」&「淵」→電...
-
「ワカチコ」の意味を教えてく...
-
選手宣言で面白いネタあります...
-
「タマランチ会長」とか「そう...
-
スイミングスクールで選手選抜...
-
「感謝の意を表す」と「感謝の...
-
なぜ女子より男子の方が足、細...
-
試合前(前夜)のセックス
-
リレーで勝手にインコースに入...
-
職業に付ける「者」「家」「士...
-
友達が野球選手と付き合いたい...
-
弘法筆を選ばずの反対語
-
150センチの人より頭ひとつ高い...
-
リオ五輪でみなさんが期待・応...
-
全国の「亮」さんへ!どのよう...
-
最近、「筋トレ」を始めた女で...
-
高3です 50M走5秒8って早いです...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
高飛び込みの競技後
-
飛び込みの後にシャワーを浴び...
-
「いち~として」の「いち」と...
-
人見絹枝が男性でなかった証拠...
-
「ワカチコ」の意味を教えてく...
-
柔道100キロ超級って99キ...
-
人名や地名の「渕」&「淵」→電...
-
表彰式で表彰される人は、何と...
-
柔道着の下はパンツをはかない?
-
釣りキチ三平はお父さんに会え...
-
試合前(前夜)のセックス
-
女性の陸上選手って何であんな...
-
「タマランチ会長」とか「そう...
-
競技飛び込み、水とのインパク...
-
海外スポーツ選手の刺青は本物?
-
なぜ女子より男子の方が足、細...
-
基礎代謝を2000にするにはマッ...
-
女子スポーツでも野球とサッカ...
-
オリンピックは本名しかダメで...
-
「感謝の意を表す」と「感謝の...
おすすめ情報