'Art Grant Code. Last update 4/18/2015. Sub Update1() CurrentDb.Execute "Delete from Grades_A" CurrentDb.Execute "Insert into Grades_A(StuNumTerm,StuNum, TermCode, Code, Grade, CrAtt, CrErn) Select StuNumTerm, StuNum, TermCode, Code, Grade, CrAtt, CrErn from TG_" CurrentDb.Execute "Insert into Grades_A(StuNumTerm,StuNum, TermCode, Code, Grade, CrAtt, CrErn) Select StuNumTerm, StuNum, Term as TermCode, Code, Grade, CrAtt, CrErn from StarFG_" MsgBox "Update 1 Done" End Sub Sub Update2() CurrentDb.Execute "Delete from Grades_E" CurrentDb.Execute "Insert into Grades_E(StuNumTerm,StudentName, StuNum, ProgVersCode, FirstTerm, AGFirstTerm, GradTerm, TermCode, PrevTerm, FT, TermCount, GT, CrAtt, CrErn) Select StuNumTerm,StudentName, StuNum, ProgVersCode, FirstTerm, AGFirstTerm, GradTerm, TermCode, PrevTerm, FT, TermCount, GT, CrAtt, CrErn from Grades_D" MsgBox "Update 2 Done" End Sub Sub Update3() Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("Grades_E") If rs.BOF() And rs.EOF() Then Exit Sub End If rs.MoveFirst x = 1 Do Until rs.EOF rs.Edit rs!rec = x rs.Update x = x + 1 rs.MoveNext Loop s = 0 A = 0 strStuNum = "N" rs.MoveFirst Do Until rs.EOF() If rs!StuNum = strStuNum Then s = 1 A = A rs.Edit rs!StuID = A rs.Update Else s = 0 A = A + 1 rs.Edit rs!StuID = A rs.Update End If strStuNum = rs!StuNum rs.MoveNext Loop Set rs = Nothing MsgBox "Update 3 Done" End Sub Sub Update4() 'Below code will update CumCrErn (Cumulative Credits Earned). Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("Grades_E") If rs.BOF And rs.EOF Then Exit Sub End If rs.MoveFirst Cumulate = 0 Same_StuNum = 0 strStuNum = "N" rs.MoveFirst Do Until rs.EOF() If rs!StuNum = strStuNum Then Same_StuNum = 1 'If the StuNum is same as prev Cumulate = Cumulate + rs!CrErn rs.Edit rs!CumCrErn = Cumulate rs.Update Else Same_StuNum = 0 'If not same StuNum as prev Cumulate = 0 Cumulate = Cumulate + rs!CrErn rs.Edit rs!CumCrErn = Cumulate rs.Update End If strStuNum = rs!StuNum rs.MoveNext Loop rs.MoveLast rs.MoveFirst Set rs = Nothing MsgBox "Update 4 Done" End Sub Sub Update5() 'Below code will update CumCrErn_FFT (Cumulative Credits Earned from FirstTerm). Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("Grades_E") If rs.BOF And rs.EOF Then Exit Sub End If rs.MoveFirst Cumulate = 0 Same_StuNum = 0 strStuNum = "N" rs.MoveFirst Do Until rs.EOF() If rs!StuNum = strStuNum Then Same_StuNum = 1 'If the StuNum is same as prev If rs!TermCount = 1 Then Cumulate = Cumulate + rs!CrErn rs.Edit rs!CumCrErn_FFT = Cumulate rs.Update End If Else Same_StuNum = 0 'If not same StuNum as prev Cumulate = 0 If rs!TermCount = 1 Then Cumulate = Cumulate + rs!CrErn rs.Edit rs!CumCrErn_FFT = Cumulate rs.Update End If End If strStuNum = rs!StuNum rs.MoveNext Loop rs.MoveLast rs.MoveFirst Set rs = Nothing MsgBox "Update 5 Done" End Sub Sub Update6() Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("Grades_E") If rs.BOF And rs.EOF Then Exit Sub End If rs.MoveFirst Do Until rs.EOF() If rs!TermCount = 1 Then rs.Edit rs!ErnAGCr = Int((((rs!CumCrErn_FFT - rs!CrErn) Mod 12) + rs!CrErn) / 12) * 12 rs.Update Else rs.Edit rs!ErnAGCr = 0 rs.Update End If rs.MoveNext Loop rs.MoveFirst Cumulate = 0 Same_StuNum = 0 strStuNum = "N" rs.MoveFirst Do Until rs.EOF() If rs!StuNum = strStuNum Then Same_StuNum = 1 'If the StuNum is same as prev Cumulate = Cumulate + rs!ErnAGCr rs.Edit rs!CumErnAGCr = Cumulate rs.Update Else Same_StuNum = 0 'If not same StuNum as prev Cumulate = 0 Cumulate = Cumulate + rs!ErnAGCr rs.Edit rs!CumErnAGCr = Cumulate rs.Update End If strStuNum = rs!StuNum rs.MoveNext Loop rs.MoveLast rs.MoveFirst Set rs = Nothing MsgBox "Update 6 Done" End Sub Function CourseCR(code) Dim R As Integer R = 3 If code Like "CUL1108*" Or code Like "CUL1116*" Or code Like "CUL1126*" Or code Like "CUL1146*" Or code Like "CUL1201*" Or code Like "CUL1204*" Or code Like "CUL1260*" Or code Like "CUL2301*" Or code Like "CUL2304*" Then R = 6 End If If code Like "HU*" Or code Like "MS*" Or code Like "SB*" Then R = 4 End If If code Like "CM4405*" Or code Like "PS1010*" Then R = 4 End If If code Like "ART*" Or code Like "BIO*" Or code Like "COM*" Or code Like "ECO*" Or code Like "ENG*" Or code Like "HIS*" Or code Like "MTH*" Or code Like "PHI*" Or code Like "PSY*" Or code Like "SOC*" Then R = 4 End If If code Like "HU090*" Or code Like "MS090*" Or code Like "ENG095*" Then R = 3 End If If code Like "EM4414*" Or code Like "FD3337*" Or code Like "FM3337*" Or code Like "FS497*" Or code Like "GA1121*" Or code Like "GD4413*" Or code Like "ID4415*" Or code Like "IT4413*" Or code Like "MA4411*" Or code Like "MM4403*" Or code Like "PH4204*" Or code Like "SD4333*" Or code Like "VG1102*" Then R = 2 End If If code Like "FRM331*" Then R = 2 End If If code Like "CS001*" Or code Like "MS111L*" Or code Like "MSLAB100*" Or code Like "MS114L*" Or code Like "RS*" Then R = 0 End If CourseCR = R End Function