Accessでタイムによるレーンの割り振り方法について

質問

お世話になります。

アクセスの抽出？についての質問です。

水泳競技のレーン割り振りをアクセスにて作成しております。

各種目ごとのエントリータイムの昇順までは表示させることができましたが、
レーンの割り振りをどのように行えばよいか全く見当もつきません。

各種目ごとのタイムから
１位は４レーン
２位は５レーン
３位は３レーン
４位は６レーン
５位は２レーン
６位は７レーン
７位は１レーン
と割り振りを行いたい。

しかし、参加者が６の倍数の場合は

１位は４レーン
２位は５レーン
３位は３レーン
４位は６レーン
５位は２レーン
６位は７レーン
と割り振りを行います。

ですが、６と７の最小公倍数が４２のため、参加者が４２を超えた場合には

１位は４レーン
２位は５レーン
３位は３レーン
４位は６レーン
５位は２レーン
６位は７レーン
７位は１レーン
と割り振りを行いたます。

単純に抽出を行うためのクエリは以下のようにしました。
SELECT Tエントリーマスター.生徒ID, Tエントリーマスター.生徒名, Tエントリーマスター.フリガナ, T学校マスター.学校名, T種目マスター.種目名 AS 種目名の合計, Tエントリーマスター.タイム
FROM T種目マスター INNER JOIN (T学校マスター INNER JOIN Tエントリーマスター ON T学校マスター.学校ID=Tエントリーマスター.学校ID) ON T種目マスター.種目ID=Tエントリーマスター.種目ID
GROUP BY Tエントリーマスター.生徒ID, Tエントリーマスター.生徒名, Tエントリーマスター.フリガナ, T学校マスター.学校名, T種目マスター.種目名, Tエントリーマスター.タイム, Tエントリーマスター.学校ID, Tエントリーマスター.種目ID
ORDER BY T種目マスター.種目名, Tエントリーマスター.タイム;

お忙しいとは思いますがよろしくお願いいたします。

回答No.7

CHRONOS_0 · ベストアンサー

それじゃ作成に取りかかりましょうか
レコード数はたかがしれているでしょうから
処理速度を気にする必要はないと考えてすべて式でやってしまいましょう

まず元のテーブルは下のようなものとします

「記録」（エントリーNO、名前、種目、タイム）

もう一つコース分け用のテーブルを作成します

「コース分け」
組内順位 コースNO
1　　　　 4
2　　　　 5
3　　　　 3
4　　　　 6
5　　　　 2
6　　　　 7
7　　　　 1

記録テーブルから下のようなクエリを作ります

順位: DCount("*","swim","種目=" & [種目] & " and (タイム< #" & [タイム] & "# or タイム=#" & [タイム] & "# and エントリーNO<" & [エントリーNO] & ")")

組NO: IIf(DCount("*","swim","種目=" & [種目]) Mod 6=0,[順位]\6,[順位]\7))+1

組内順位: IIf(DCount("*","swim","種目=" & [種目]) Mod 6=0,[順位] Mod 6,[順位] Mod 7)+1

このクエリとコース分けテーブルを組内順位で結合するクエリを作ります

お礼

お忙しい中本当にありがとうございます！
クエリのみでもここまでできるのですね！！！
もっと、もっと、関数の勉強もしなくては！　ですね・・・ orz
まだ、まだ、精進ですね！

諮問ばかりではなく、回答者になれるように頑張ります。

いつも、本当にありがとうございます。

回答No.6

30246kiku

> たとえば、「"レース名"＆"エントリーマスター"」などとして
> この過去データとして呼び出すクエリ？が必要になってきます・・・
> こちらも、ＶＢＡなのでしょうね・・・

フォーム表示で、フォーム１つで対象テーブルを切り替える方法（一例）

基本的なクエリが以下の様だとします。

SELECT Tエントリーマスター.生徒ID, Tエントリーマスター.生徒名, Tエントリーマスター.フリガナ, T学校マスター.学校名, T種目マスター.種目名, Tエントリーマスター.タイム
FROM T種目マスター INNER JOIN (T学校マスター INNER JOIN Tエントリーマスター ON T学校マスター.学校ID = Tエントリーマスター.学校ID) ON T種目マスター.種目ID = Tエントリーマスター.種目ID
ORDER BY T種目マスター.種目名, Tエントリーマスター.タイム;

VBA部分で String 変数に「Tエントリーマスター」部分を「XXXXXX」に変更したものを宣言しておきます。

SELECT XXXXXX.生徒ID, XXXXXX.生徒名, XXXXXX.フリガナ, T学校マスター.学校名, T種目マスター.種目名, XXXXXX.タイム
FROM T種目マスター INNER JOIN (T学校マスター INNER JOIN XXXXXX ON T学校マスター.学校ID = XXXXXX.学校ID) ON T種目マスター.種目ID = XXXXXX.種目ID
ORDER BY T種目マスター.種目名, XXXXXX.タイム;

strSQL = "・・・" この部分に上記を記述

必要に応じて、フォームの RecordSource に XXXXXX を書き換えたものを設定します。

例）「Tエントリーマスター」テーブルを対象とする場合

Const strSQL = "・・・"

Private Sub Form_Load()
　　Me.RecordSource = Replace(srtSQL, "XXXXXX", "Tエントリーマスター")
　　Me.Requery
End Sub

どのテーブルを処理対象とするか/させるか
どのタイミングで切り替えるか・・・・・・いろいろ考えてみてください。

VBAは結構面白いと思います。

お礼

お忙しい中ありがとうございます。

これから、ＶＢＡを使いこなすべく勉強に励みます！

本当にありがとうございました。

回答No.5

30246kiku

#4 です

記述が間違ってました

誤）

　例）
　　　７人 -> 7 　　　８人 -> 6 + 2
　　　９人 -> 6 + 3　　１０人 -> 7 + 2
　　１１人 -> 7 + 3　　１２人 -> 6 + 6
　　１３人 -> 7 + 6　　１４人 -> 7 + 7
　　１５人 -> 7 + 6 + 2　・・・

正）

　例）
　　　７人 -> 7 　　　８人 -> 6 + 2
　　　９人 -> 6 + 3　　１０人 -> 7 + 3
　　１１人 -> 7 + 4　　１２人 -> 6 + 6
　　１３人 -> 7 + 6　　１４人 -> 7 + 7
　　１５人 -> 7 + 6 + 2　・・・

回答No.4

30246kiku

ちょっと興味があったので、ご迷惑かもしれません。失礼いたします。

ご質問で提示されているクエリは以下のものでも同じでしょうか
（グループ化は使っていないのですが：グループ化で何が変わるかわからなかったので）

SELECT Tエントリーマスター.生徒ID, Tエントリーマスター.生徒名, Tエントリーマスター.フリガナ, T学校マスター.学校名, T種目マスター.種目名, Tエントリーマスター.タイム
FROM T種目マスター INNER JOIN (T学校マスター INNER JOIN Tエントリーマスター ON T学校マスター.学校ID = Tエントリーマスター.学校ID) ON T種目マスター.種目ID = Tエントリーマスター.種目ID
ORDER BY T種目マスター.種目名, Tエントリーマスター.タイム;

同じということが前提で、２通りの方法を

方法1）ADOを使用したVBAでの記述（テーブルデータ書換え）

　前提条件）Tエントリーマスターに３フィールド追加
　　　　　　　順位、組、レーン　（それぞれ整数）
　　　　　　（順位、組は、後々ソートに使えるのではないか程度のもの）

　　7人体制で最終レースが２人以下であれば、前レースを６人とする例
　　（極端な例として、一人では泳がせない）
　　（不要であれば Start ～ End 削除してください）

　例）
　　　７人 -> 7 　　　８人 -> 6 + 2
　　　９人 -> 6 + 3　　１０人 -> 7 + 2
　　１１人 -> 7 + 3　　１２人 -> 6 + 6
　　１３人 -> 7 + 6　　１４人 -> 7 + 7
　　１５人 -> 7 + 6 + 2　・・・

Public Sub UpdateMatchRace()
　　Dim rs As New ADODB.Recordset
　　Dim rsUp As New ADODB.Recordset
　　Dim iModVal As Integer
　　Dim iRecCount As Long
　　Dim iCount As Long
　　Dim iRace As Integer
　　Dim iNum As Integer
　　Dim iTmp As Integer

　　rs.Source = "SELECT * FROM T種目マスター ORDER BY 種目名;"
　　rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
　　rsUp.Source = "SELECT * FROM Tエントリーマスター ORDER BY タイム, 生徒ID;"
　　rsUp.Open , CurrentProject.Connection, adOpenKeyset, adLockOptimistic

　　While (Not rs.EOF)
　　　　rsUp.Filter = "[種目ID]=" & rs("種目ID")
'　　　　rsUp.Filter = "[種目ID]='" & rs("種目ID") & "'" '文字なら
　　　　If (Not rsUp.EOF) Then
　　　　　　iRecCount = rsUp.RecordCount
　　　　　　If ((iRecCount Mod 6) = 0) Then
　　　　　　　　If ((iRecCount Mod 7) = 0) Then
　　　　　　　　　　iModVal = 7
　　　　　　　　Else
　　　　　　　　　　iModVal = 6
　　　　　　　　End If
　　　　　　Else
　　　　　　　　iModVal = 7
　　　　　　End If
　　　　　　iNum = 1
　　　　　　iRace = 0
　　　　　　iCount = iModVal + 1
　　　　　　iRecCount = iRecCount + iModVal
　　　　　　rsUp.MoveFirst
　　　　　　While (Not rsUp.EOF)
　　　　　　　　If (iCount > iModVal) Then
　　　　　　　　　　iRace = iRace + 1
　　　　　　　　　　iCount = 1
' Start 一人では泳がせない
　　　　　　　　　　iRecCount = iRecCount - iModVal
　　　　　　　　　　iTmp = iRecCount Mod iModVal
　　　　　　　　　　If ((iTmp > 0) And (iTmp <= 2) And _
　　　　　　　　　　　　(iRecCount < (iModVal * 2))) Then
　　　　　　　　　　　　iModVal = 6
　　　　　　　　　　End If
' End 一人では泳がせない
　　　　　　　　End If
　　　　　　　　rsUp("順位") = iNum
　　　　　　　　rsUp("組") = iRace
　　　　　　　　rsUp("レーン") = Choose(iCount, 4, 5, 3, 6, 2, 7, 1)
　　　　　　　　rsUp.Update
　　　　　　　　iNum = iNum + 1
　　　　　　　　iCount = iCount + 1
　　　　　　　　rsUp.MoveNext
　　　　　　Wend
　　　　End If
　　　　rs.MoveNext
　　Wend

　　rs.Close
　　rsUp.Close
End Sub

方法2）同タイムがない＆７人体制という前提でのクエリ（雰囲気だけで）

レーンを管理するテーブル「Tレーンマスター」を作ります。
フィールドは、RANK、レーンNO の２つ（整数）
テーブル内容
　RANK　レーンNO
　　1　　　4
　　2　　　5
　　3　　　3
　　4　　　6
　　5　　　2
　　6　　　7
　　7　　　1

SELECT Tエントリーマスター.生徒ID, Tエントリーマスター.生徒名, Tエントリーマスター.フリガナ, T学校マスター.学校名, T種目マスター.種目名, Tエントリーマスター.タイム,
(SELECT COUNT(*)+1 FROM Tエントリーマスター AS T1 WHERE T1.種目ID=Tエントリーマスター.種目ID AND T1.タイム<Tエントリーマスター.タイム) AS 順位,
(SELECT CINT((COUNT(*)+4)/7) FROM Tエントリーマスター AS T2 WHERE T2.種目ID=Tエントリーマスター.種目ID AND T2.タイム<Tエントリーマスター.タイム) AS 組,
(SELECT レーンNO FROM Tレーンマスター WHERE Tレーンマスター.RANK =
(SELECT (COUNT(*) MOD 7)+1 FROM Tエントリーマスター AS T3 WHERE T3.種目ID=Tエントリーマスター.種目ID AND T3.タイム<Tエントリーマスター.タイム)
) AS レーン
FROM T種目マスター INNER JOIN (T学校マスター INNER JOIN Tエントリーマスター ON T学校マスター.学校ID=Tエントリーマスター.学校ID) ON T種目マスター.種目ID=Tエントリーマスター.種目ID
ORDER BY T種目マスター.種目名, Tエントリーマスター.タイム;

クエリで・・・、私には考えられませんでした。すぐVBAに頼っちゃいます

お礼

ありがとうございます。

ＶＢＡで動作確認できました。
あとは、決勝タイムを入力して順位付けです。

と、現状では１回こっきりのものになってしまうので、Ｔエントリーマスターを過去データとして保存、
たとえば、「"レース名"＆"エントリーマスター"」などとして
この過去データとして呼び出すクエリ？が必要になってきます・・・
こちらも、ＶＢＡなのでしょうね・・・

これから頑張って勉強します！

補足

お忙しい中ありがとうございます。

今から試してみます！

回答No.3

CHRONOS_0

>６で割り切れるときの参加人数のときは、６レーンを使用します。
こういう例外があるのならそのときだけ別の処理をするしかないですね
この場合も自動化したいとなるとマクロというよりVBAで処理することになりますが
どこまでの自動化をお望みなんでしょう

念のために確認したいのですが
１９人の時に６人で２レース、７人で１レースではなく
７人で２レース、５人で１レースでいいのですね

補足

押しそがしい中いつもありがとうございます。

やはりＶＢＡでの処理になりますか・・・

＞念のために確認したいのですが
＞１９人の時に６人で２レース、７人で１レースではなく
＞７人で２レース、５人で１レースでいいのですね

はい、７人で２レース、５人で１レースの処理になります。

ＶＢＡもやはり勉強しないとですね！
いままで、避けていた分野でありますが、がんばります。

回答No.2

CHRONOS_0

>レース数は最少レース数としたいので、基本的には７人体制でのレースとなります。
最初の質問の６の倍数云々というのは関係ないのですね
それなら簡単な計算だけでできますね

ただしレースですから同タイムというケースが出てきますよね
その場合でも出走順を分ける必要が出てきますが
それはどのようにするのでしょうか

補足

いつもいつも、お世話になります。
前回の時にもお世話になりっぱなしで本当にありがとうございます。

最初の６の倍数というのは、基本７人態勢でレースを組みますが、
６で割り切れるときの参加人数のときは、６レーンを使用します。
6・12・18・24・30・36・42・48・54・60・66・72・78・84・90・96 の場合です。
ただし、1種目の参加人数が42人・84人の場合は７人態勢でレースを組みます。

あぁーー　同タイム・・・ですか・・・ orz
絶対に出てくるケースですよね・・・

同タイムの場合は、名前IDで判定をしてレーンを割り振る・・・　が妥当なところでしょうか・・・

回答No.1

CHRONOS_0

まだ仕様がはっきりしていませんね

45人が参加し、１位から４５位まで整列させたとして
それをどのようにレーン割り付けすればいいのですか？

１０人しか参加していないときにはどう割り振るのですか？

補足

返信ありがとうございます。

水泳競技の種目は「１７種目」あります。
各競技のエントリー人数はその時々で違ってきます。

人数を割り振ったときの余った人数の処理は以下のようになります。

レース数は最少レース数としたいので、基本的には７人体制でのレースとなります。

１０人の場合は、７人を１レース目残りの３人は２レース目とします。
４５人の場合は、７人ずつをレースに割り振り、残りの３人を最終組とします。

１レース
　１位は４レーン
　２位は５レーン
　３位は３レーン
　４位は６レーン
　５位は２レーン
　６位は７レーン
　７位は１レーン

２レース
　８位は４レーン
　９位は５レーン
　１０位は３レーン
　１１位は６レーン
　１２位は２レーン
　１３位は７レーン
　１４位は１レーン

３レース
　１５位は４レーン
　１６位は５レーン
　１７位は３レーン
　１８位は６レーン
　１９位は２レーン
　２０位は７レーン
　２１位は１レーン

４レース
　２２位は４レーン
　２３位は５レーン
　２４位は３レーン
　２５位は６レーン
　２６位は２レーン
　２７位は７レーン
　２８位は１レーン

といった感じでレース数を組み立てていきます。

最後に新規テーブルに書き出したいと思います。
新規テーブルに書き出したデータに決勝レースタイムを入力し、順位を決めます。
その順位から、レポートを使用して賞状を作成したいと考えております。

お忙しとは思いますがよろしくお願いいたします。

やっぱり、マクロでないとダメでしょうか・・・