Have－Stone

成績処理8

最終的なコードを示す前に、もう一度準備段階の確認をしておく。今回は、連載6で科目名固定の処理として掲載したコードをどんな科目であっても処理できるよう一般化する。


1.集計対象となる生徒集団を抽出したテーブルを作成する。
　T_seito
2.1の生徒集団について、特定の試験についての成績情報を抽出したテーブルを作成する。
T_seiseki
3.2のテーブルから、集計対象の科目を抽出(重複クエリ)したテーブルを作成する。
T_kamoku
この3つのテーブルが作成できたら、今回紹介するVBAのコードを実行すればよい。
従って、3つのテーブルとコード実行中に新規作成されるテーブル(T_ichiran)で合計4つのテーブルを扱うことになる。

もちろん、実際の現場ではこれらすべてを自動化して、一覧表印刷まで行うことになる。
例えば、次のようなフォームから指定をしてボタンを押すだけで処理されるようにする。

直接入力はミスのもと、年度を指定し適切なクラス範囲の指定しかできないようにすべきである。　　　　

準備段階の3つのテーブル(読み取り専用)
T_seito
T_kamoku
T_seiseki
新規作成されるテーブル
T_ichiran

それでは、以下にVBAコードを示します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
Dim cn As ADODB.Connection
‘—- 4つのテーブルを使用します —-
Dim r1 As ADODB.Recordset
Dim r2 As ADODB.Recordset
Dim r3 As ADODB.Recordset
Dim r4 As ADODB.Recordset
‘—————————————————–
Dim h_avg As Double      ‘平均点
Dim i, j, k              ‘カウンター
Dim st_SQL As String     ‘sql用文字列
Dim all_k As String      ‘科目名をフィールド名にするための文字列、sql文で使用
Dim all_k_i As String    ‘科目名をフィールド名にするための文字列、sql文で使用
Dim all_k_h As String    ‘科目名をフィールド名にするための文字列、sql文で使用
Dim k_name(30) As String   ‘科目名を入れる配列
Dim k_n As Integer       ‘科目数を入れる変数
‘科目数を取得
k_n = DCount(“*”, “T_kamoku”)
all_k_i = “生徒名 varchar,”        ‘フィールド定義
all_k_h = “平均点 Double”         ‘フィールド定義
Set cn = CurrentProject.Connection
Set r1 = New ADODB.Recordset
Set r2 = New ADODB.Recordset
Set r3 = New ADODB.Recordset
Set r4 = New ADODB.Recordset
r1.Open “T_seiseki”, cn, adOpenForwardOnly, adLockReadOnly ‘得点テーブル
r3.Open “T_kamoku”, cn, adOpenForwardOnly, adLockReadOnly  ‘科目名テーブル
i = 1
‘科目名を取得
Do Until r3.EOF
    k_name(i) = r3!科目名
    r3.MoveNext
    i = i + 1
Loop
‘———————–
i = 1
‘———–###ここがポイント###——-
‘科目名を一般化してフィールド名に入れる準備
For i = 1 To 25
all_k = all_k & “kn” & i & ” double,”
Next
‘最終的にフィールド名とするための処理
all_k = all_k_i & all_k & all_k_h
‘処理前にテーブルを削除
On Error Resume Next
DoCmd.DeleteObject acTable, “T_ichiran”
On Error GoTo 0
‘テーブル作成のSQL文およびSQL実行
st_SQL = “CREATE TABLE T_ichiran(“ & all_k & “);”
DoCmd.RunSQL st_SQL
r2.Open “T_ichiran”, cn, adOpenKeyset, adLockOptimistic    
‘新規に作成したテーブル
‘生徒名を取り出し、新規テーブルに登録する処理
r4.Open “T_seito”, cn, adOpenKeyset, adLockReadOnly      
‘生徒名を参照するテーブル
      Do Until r4.EOF
            r2.AddNew
            r2!生徒名 = r4!生徒名
            r2.Update
            r4.MoveNext
      Loop
‘———————————————————————————————–
‘ここから点数を書き込む処理
‘参照テーブルは得点テーブルT_seideki（r1.open）→T_ichiran（r2.open）
‘—————————————————————
r1.MoveFirst
r2.MoveFirst
Do Until r2.EOF
    h_avg = 0
    k = 0
    r1.MoveFirst
    
    Do Until r1.EOF
    
        j = 1
    
        Do Until j > r2.Fields.Count – 1
            
            If r1!生徒名 = r2!生徒名 And k_name(j) = r1!科目名 Then
                
                       r2(“kn” & j) = r1!点数  ‘— r2.Fields(j).Value = r1!点数 としても可 —–
                       h_avg = h_avg + Nz(r1!点数)
                       k = k + 1
                       If h_avg <> 0 Then r2!平均点 = Round((h_avg / k), 2)
                       r2.Update
                
            Else
            End If
            j = j + 1
  
        Loop
     r1.MoveNext
     Loop
r2.MoveNext
Loop
‘———–レコードセットを閉じる———
r1.Close
Set r1 = Nothing
r2.Close
Set r2 = Nothing
r3.Close
Set r3 = Nothing
r4.Close
Set r4 = Nothing
cn.Close
Set cn = Nothing
End Sub

いしもち

, ,