Sub Update1() 'Code last updated on 1/22/2011 'Need Master, TranscriptGrade, and FinalGrade. 'Colume need for TranscriptGrade: StudentName, StudentName2, SSN, Code, TermCode, AdGradeLetterCode, and CreditEarned. CurrentDb.Execute "Update TranscriptGrade set TermCode = trim(TermCode), Code = trim(Code), adGradeLetterCode = trim(adGradeLetterCode), ssn=trim(ssn)" CurrentDb.Execute "UPdate FinalGrade set TermCode = trim(TermCode), Code = trim(Code), adGradeLetterCode = trim(adGradeLetterCode), ssn=trim(ssn)" CurrentDb.Execute "Update TranscriptGrade set Termcode = left(Termcode,7)" CurrentDb.Execute "Update FinalGrade set TermCode = left(Termcode,7)" CurrentDb.Execute "Update FinalGrade set TermCode = '200704Q' where TermCode = '200705Q'" CurrentDb.Execute "Update FinalGrade set TermCode = '200707Q' where TermCode = '200708Q'" CurrentDb.Execute "Update FinalGrade set TermCode = '200710Q' where TermCode = '200711Q'" CurrentDb.Execute "Update FinalGrade set TermCode = '200801Q' where TermCode = '200802Q'" CurrentDb.Execute "Update FinalGrade set TermCode = '200407Q' where TermCode = '200408M'" CurrentDb.Execute "Update FinalGrade set TermCode = '200410Q' where TermCode in ('200410M', '200411M', '200412M')" CurrentDb.Execute "Update FinalGrade set TermCode = '200501Q' where TermCode in ('200501M', '200503M')" CurrentDb.Execute "Update FinalGrade set TermCode = '200504Q' where TermCode in ('200504M', '200505M', '200506M')" CurrentDb.Execute "Update FinalGrade set TermCode = '200507Q' where TermCode in ('200508M', '200509M')" CurrentDb.Execute "Update FinalGrade set TermCode = '200510Q' where TermCode in ('200510M', '200511M')" CurrentDb.Execute "Update FinalGrade set TermCode = '200607Q' where Termcode = '200607M'" CurrentDb.Execute "Update FinalGrade set TermCode = '200803Q' where Termcode = '200805Q'" CurrentDb.Execute "Update FinalGrade set TermCode = '200807Q' where Termcode = '200808Q'" CurrentDb.Execute "Update FinalGrade set TermCode = '200809Q' where Termcode = '200811Q'" CurrentDb.Execute "Update FinalGrade set TermCode = '200901Q' where Termcode = '200902Q'" CurrentDb.Execute "Update FinalGrade set TermCode = '200904Q' where Termcode = '200905Q'" CurrentDb.Execute "Update FinalGrade set TermCode = '200907Q' where Termcode = '200908Q'" CurrentDb.Execute "Update FinalGrade set TermCode = '200910Q' where Termcode = '200911Q'" CurrentDb.Execute "Update FinalGrade set TermCode = '201001Q' where Termcode = '201002Q'" CurrentDb.Execute "Update FinalGrade set TermCode = '201004Q' where Termcode = '201005Q'" CurrentDb.Execute "Update FinalGrade set TermCode = '201007Q' where Termcode = '201008Q'" CurrentDb.Execute "Update FinalGrade set TermCode = '201010Q' where Termcode = '201011Q'" CurrentDb.Execute "Update FinalGrade set TermCode = '201101Q' where Termcode = '201102Q'" CurrentDb.Execute "Update TranscriptGrade set TermCode = TermCode & 'F'" 'QF: Quarter Full CurrentDb.Execute "Update FinalGrade set TermCode = TermCode & 'F'" 'QF: Quarter Full CurrentDb.Execute "Delete from TranscriptGrade where Termcode like '2*T*'" CurrentDb.Execute "Delete from TranscriptGrade where Code like 'RS90*'" CurrentDb.Execute "Delete from FinalGrade where Code like 'RS90*'" CurrentDb.Execute "Update TranscriptGrade set Termcode = '00TRAN' where Termcode like '*Trans*'" CurrentDb.Execute "Update TranscriptGrade set Termcode = '01PREV' Where Termcode like '*PREV*'" CurrentDb.Execute "Update FinalGrade set Termcode = '00TRAN' where Termcode like '*Trans*'" CurrentDb.Execute "Update FinalGrade set Termcode = '01PREV' Where Termcode like '*PREV*'" CurrentDb.Execute "Update TranscriptGrade set Termcode = '00TRAN' where adGradeLetterCode in ('P','PR','TR')" CurrentDb.Execute "Update FinalGrade set Termcode = '00TRAN' where adGradeLetterCode in ('P','PR','TR')" CurrentDb.Execute "Update TranscriptGrade set adGradeLetterCode='' where adGradeLetterCode is null" CurrentDb.Execute "Update FinalGrade set adGradeLetterCode='' Where adGradeLetterCode is null" CurrentDb.Execute "Delete from TranscriptGrade where Termcode = 'None' and adGradeLetterCode =''" CurrentDb.Execute "Delete from FinalGrade where Termcode = 'None' and adGradeLetterCode =''" 'CurrentDb.Execute "Delete from TranscriptGrade where EnrollStatus = 'Dropped' and adGradeLetterCode =''" CurrentDb.Execute "Delete from FinalGrade where EnrollStatus = 'Dropped' and adGradeLetterCode =''" CurrentDb.Execute "Delete from CombineGrade2" CurrentDb.Execute "Insert into CombineGrade2(SSN, TermCode, Code, AdgradeLetterCode) Select distinct SSN, TermCode, Code, AdgradeLetterCode from CombineGrade" CurrentDb.Execute "Update CombineGrade2 set B='', C=''" CurrentDb.Execute "Update CombineGrade2 set txtID=Left(ssn,3) + Mid(ssn,5,2) + Mid(ssn,8,4)" CurrentDb.Execute "Update CombineGrade2 set StuID=val(txtID)" CurrentDb.Execute "Update CombineGrade2 set Credit=3" CurrentDb.Execute "Update CombineGrade2 set Credit=4 where code like 'HU*' or code like 'SB*' or code like 'MS*' or code like 'IS*'" CurrentDb.Execute "Update CombineGrade2 set Credit=3 where code like '*090*'" CurrentDb.Execute "Update CombineGrade2 set Credit=2 where code in ('FD3337', 'FS497', 'GD4413', 'MM4403', 'ID4415')" CurrentDb.Execute "Update CombineGrade2 set Credit=6 where code in (select CourseCode from SixCR)" CurrentDb.Execute "Update CombineGrade2 set CrAtt=Credit where adGradeLetterCode in ('A+','A','A-','B+','B','B-','C+','C','C-','D+','D','D-','F','I','W','WF','')" CurrentDb.Execute "Update CombineGrade2 set CrAtt=0 where adGradeLetterCode in ('T','TR','P','PR','K')" CurrentDb.Execute "Update CombineGrade2 set CrErn=Credit where adGradeLetterCode in ('A+','A','A-','B+','B','B-','C+','C','C-','D+','D','D-','T','TR','P','PR','K')" CurrentDb.Execute "Update CombineGrade2 set CrErn=0 where adGradeLetterCode in ('F', 'I','W','WF','R','')" Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("CombineGrade2") 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 CurrentDb.Execute "Delete from CombineGrade4" CurrentDb.Execute "Insert into CombineGrade4(StudentName, SSN, Progverscode, Req, Termcode, Code, adGradeLetterCode, Credit, CrAtt, CrErn, TruErn, Taking, StuID, Rec, A, B, C, Cum, Tem, GL) Select StudentName, SSN, Progverscode, Req, Termcode, Code, adGradeLetterCode, Credit, CrAtt, CrErn, TruErn, Taking, StuID, Rec, A, B, C, Cum, Tem, GL from CombineGrade3" MsgBox "Update 1 Done" End Sub Sub Update2() '--CULAS must take course CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'CUL_AS*' and code in (select REQ from REQ_CULAS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'CUL_AS*' and code in (select REQ from REQ_CULAS)" '--CULBMS must take course CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'CULMBS*' and code in (select REQ from REQ_CULMBS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'CULMBS*' and code in (select REQ from REQ_CULMBS)" '--DFVBS must take course CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'DFV_BS*' and code in (select REQ from REQ_DFVBS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'DFV_BS*' and code in (select REQ from REQ_DFVBS)" '--DPHAS must take course CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'DPH_AS*' and code in (select REQ from REQ_DPHAS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'DPH_AS*' and code in (select REQ from REQ_DPHAS)" '--DPHBS must take course CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'DPH_BS*' and code in (select REQ from REQ_DPHBS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'DPH_BS*' and code in (select REQ from REQ_DPHBS)" '-- FDAS must take coure CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FD*AS*' and code in (select REQ from REQ_FDAS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'FD*AS*' and code in (select REQ from REQ_FDAS)" '-- FMAS must take coure CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FM*AS*' and code in (select REQ from REQ_FMAS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'FM*AS*' and code in (select REQ from REQ_FMAS)" '-- IMDAS (WDIMAS) must take coure CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like '*IM*AS*' and code in (select REQ from REQ_IMDAS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like '*IM*AS*' and code in (select REQ from REQ_IMDAS)" '-- GADBS must take coure CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'GAD_BS*' and code in (select REQ from REQ_GADBS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'GAD_BS*' and code in (select REQ from REQ_GADBS)" '-- GRAS must take coure CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'GR*AS*' and code in (select REQ from REQ_GRAS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'GR*AS*' and code in (select REQ from REQ_GRAS)" '-- FDBFA must take coure CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FD*BFA*' and code in (select REQ from REQ_FDBFA)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'FD*BFA*' and code in (select REQ from REQ_FDBFA)" '-- FMBS must take coure CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FM*BS*' and code in (select REQ from REQ_FMBS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'FM*BS*' and code in (select REQ from REQ_FMBS)" '-- GRBS must take coure CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'GR*BS*' and code in (select REQ from REQ_GRBS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'GR*BS*' and code in (select REQ from REQ_GRBS)" '-- IDBS must take coure CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'ID*BS*' and code in (select REQ from REQ_IDBS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'ID*BS*' and code in (select REQ from REQ_IDBS)" '-- INDBS must take coure CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'IND_BS*' and code in (select REQ from REQ_INDBS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'IND_BS*' and code in (select REQ from REQ_INDBS)" '-- IMDBS (WDIMBS) must take coure CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like '*IM*BS*' and code in (select REQ from REQ_IMDBS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like '*IM*BS*' and code in (select REQ from REQ_IMDBS)" '-- SDBS must take coure CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'SD*BS*' and code in (select REQ from REQ_SDBS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'SD*BS*' and code in (select REQ from REQ_SDBS)" '-- MAABS must take coure CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'MAA_BS*' and code in (select REQ from REQ_MAABS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'MAA_BS*' and code in (select REQ from REQ_MAABS)" '-- VEBS must take coure CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'V*BS*' and code in (select REQ from REQ_VEBS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'V*BS*' and code in (select REQ from REQ_VEBS)" '-- VGPBS must take coure CurrentDb.Execute "Update CombineGrade4 set B='YY' where A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'VGP_B*' and code in (select REQ from REQ_VGPBS)" CurrentDb.Execute "Update CombineGrade4 set C='YY' where A is null and CrErn>0 and progverscode like 'VGP_B*' and code in (select REQ from REQ_VGPBS)" MsgBox "Update 2 Done" End Sub Sub Update3() CurrentDb.Execute "Update CombineGrade4 set StuID=0" Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("CombineGrade4") 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 strSSN = "SSNSSN" rs.MoveFirst If rs.EOF() Then Exit Sub End If Do Until rs.EOF() If rs!SSN = strSSN 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 strSSN = rs!SSN rs.MoveNext Loop MsgBox "Update 3 Done" End Sub Sub Update4() Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("CombineGrade4") If rs.BOF And rs.EOF Then Exit Sub End If rs.MoveLast laststuid = rs!StuID rs.MoveFirst c = laststuid 'watch For ID = 1 To laststuid d = ID 'watch k = Round(d / c * 100, 1) 'watch Debug.Print ID & " of " & c & " -- " & k & "%" & " in processing..." 'watch 'CULAS select (1) CurrentDb.Execute "Update CombineGrade4 set B='SL' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'CUL_AS*' and code not in (Select REQ from REQ_CULAS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" CurrentDb.Execute "Update CombineGrade4 set C='SL' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like 'CUL_AS*' and code not in (Select REQ from REQ_CULAS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" 'FDAS select (1) CurrentDb.Execute "Update CombineGrade4 set B='SL' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FD*AS*' and code not in (Select REQ from REQ_FDAS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" CurrentDb.Execute "Update CombineGrade4 set C='SL' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like 'FD*AS*' and code not in (Select REQ from REQ_FDAS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" 'FMAS select (1) CurrentDb.Execute "Update CombineGrade4 set B='SL' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FM*AS*' and code not in (Select REQ from REQ_FMAS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" CurrentDb.Execute "Update CombineGrade4 set C='SL' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like 'FM*AS*' and code not in (Select REQ from REQ_FMAS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" 'GRAS select (1) CurrentDb.Execute "Update CombineGrade4 set B='SL' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'GR*AS*' and code not in (Select REQ from REQ_GRAS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" CurrentDb.Execute "Update CombineGrade4 set C='SL' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like 'GR*AS*' and code not in (Select REQ from REQ_GRAS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" 'IMDAS select (1) CurrentDb.Execute "Update CombineGrade4 set B='SL' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like '*IM*AS*' and code not in (Select REQ from REQ_IMDAS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" CurrentDb.Execute "Update CombineGrade4 set C='SL' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like '*IM*AS*' and code not in (Select REQ from REQ_IMDAS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" '********************AS Degree MS, HU, SB, GE requirement************************** 'MS (1 course) CurrentDb.Execute "Update CombineGrade4 set B='MS' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like '*AS*' and code<>'MS090' and code like 'MS*')" CurrentDb.Execute "Update CombineGrade4 set C='MS' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like '*AS*' and code<>'MS090' and code like 'MS*')" 'HU (1 course for IMDAS) CurrentDb.Execute "Update CombineGrade4 set B='HU' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like '*IM*AS*' and code not in ('HU090', 'HU110', 'HU111', 'HU130') and code like 'HU*')" CurrentDb.Execute "Update CombineGrade4 set C='HU' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like '*IM*AS*' and code not in ('HU090', 'HU110', 'HU111', 'HU130') and code like 'HU*')" 'SB (1 course for IMDAS) CurrentDb.Execute "Update CombineGrade4 set B='SB' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like '*IM*AS*' and code like 'SB*')" CurrentDb.Execute "Update CombineGrade4 set C='SB' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like '*IM*AS*' and code like 'SB*')" 'SB (2 courses for CULAS, DPHAS, FDAS, FMAS, GRAS) CurrentDb.Execute "Update CombineGrade4 set B='SB' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode in ('CUL_AS_004','DPH_AS_003', 'FD__AS_001', 'FM__AS_001', 'GR__AS_001', 'GR__AS_002') and code like 'SB*')" CurrentDb.Execute "Update CombineGrade4 set C='SB' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode in ('CUL_AS_004','DPH_AS_003', 'FD__AS_001', 'FM__AS_001', 'GR__AS_001', 'GR__AS_002') and code like 'SB*')" 'GE (1 course for DPHAS, FDAS, FMAS, GRAS, IMDAS) CurrentDb.Execute "Update CombineGrade4 set B='GE' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode in ('DPH_AS_003', 'FD__AS_001', 'FM__AS_001', 'FM__AS_003', 'GR__AS_002', 'IMD_AS_002', 'WDIMAS_001') and B='' and code in (select REQ from GE))" CurrentDb.Execute "Update CombineGrade4 set C='GE' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode in ('DPH_AS_003', 'FD__AS_001', 'FM__AS_001', 'FM__AS_003', 'GR__AS_002', 'IMD_AS_002', 'WDIMAS_001') and C='' and code in (select REQ from GE))" '****************Elective****************** 'FDBFA Select Lower Division (1 course) 'CurrentDb.Execute "Update CombineGrade4 set B='SLLO' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FD*BFA*' and code not in (Select REQ from REQ_FDBFA) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN') and (code like '??1*' or code like '??2*')" 'CurrentDb.Execute "Update CombineGrade4 set C='SLLO' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like 'FD*BFA*' and code not in (Select REQ from REQ_FDBFA) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')and (code like '??1*' or code like '??2*')" 'FDBFA Select Upper Division (2 courses) 'CurrentDb.Execute "Update CombineGrade4 set B='SLUP' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and (code like '??3*' or code like '??4*') and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FD*BFA*' and code not in (Select REQ from REQ_FDBFA) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" 'CurrentDb.Execute "Update CombineGrade4 set C='SLUP' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and (code like '??3*' or code like '??4*') and A is null and CrErn >0 and progverscode like 'FD*BFA*' and code not in (Select REQ from REQ_FDBFA) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" 'FDBFA Select (3 courses) CurrentDb.Execute "Update CombineGrade4 set B='SL' where rec in(select top 3 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FD*BFA*' and code not in (Select REQ from REQ_FDBFA) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" CurrentDb.Execute "Update CombineGrade4 set C='SL' where rec in(select top 3 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like 'FD*BFA*' and code not in (Select REQ from REQ_FDBFA) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" 'FMBS Select Lower Division (1 course) 'CurrentDb.Execute "Update CombineGrade4 set B='SLLO' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FM*BS*' and code not in (Select REQ from REQ_FMBS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN') and (code like '??1*' or code like '??2*')" 'CurrentDb.Execute "Update CombineGrade4 set C='SLLO' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like 'FM*BS*' and code not in (Select REQ from REQ_FMBS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')and (code like '??1*' or code like '??2*')" 'FMBS Select Upper Division (2 courses) 'CurrentDb.Execute "Update CombineGrade4 set B='SLUP' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and (code like '??3*' or code like '??4*') and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FM*BS*' and code not in (Select REQ from REQ_FMBS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" 'CurrentDb.Execute "Update CombineGrade4 set C='SLUP' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and (code like '??3*' or code like '??4*') and A is null and CrErn >0 and progverscode like 'FM*BS*' and code not in (Select REQ from REQ_FMBS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" 'FMBS Select (3 courses) CurrentDb.Execute "Update CombineGrade4 set B='SL' where rec in(select top 3 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FM*BS*' and code not in (Select REQ from REQ_FMBS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" CurrentDb.Execute "Update CombineGrade4 set C='SL' where rec in(select top 3 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like 'FM*BS*' and code not in (Select REQ from REQ_FMBS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" 'GRBS Select (3 courses) CurrentDb.Execute "Update CombineGrade4 set B='SL' where rec in(select top 3 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'GR*BS*' and code not in (Select REQ from REQ_GRBS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" CurrentDb.Execute "Update CombineGrade4 set C='SL' where rec in(select top 3 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like 'GR*BS*' and code not in (Select REQ from REQ_GRBS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" 'IDBS Select (3 courses) CurrentDb.Execute "Update CombineGrade4 set B='SL' where rec in(select top 3 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'ID*BS*' and code not in (Select REQ from REQ_IDBS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" CurrentDb.Execute "Update CombineGrade4 set C='SL' where rec in(select top 3 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like 'ID*BS*' and code not in (Select REQ from REQ_IDBS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" 'IMDBS (WDIMBS) Select (3 courses) CurrentDb.Execute "Update CombineGrade4 set B='SL' where rec in(select top 3 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like '*IM*BS*' and code not in (Select REQ from REQ_IMDBS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" CurrentDb.Execute "Update CombineGrade4 set C='SL' where rec in(select top 3 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like '*IM*BS*' and code not in (Select REQ from REQ_IMDBS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" 'VEBS Select Lower Division (1 course) CurrentDb.Execute "Update CombineGrade4 set B='SLLO' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'V*BS*' and code not in (Select REQ from REQ_VEBS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN') and (code like '??1*' or code like '??2*')" CurrentDb.Execute "Update CombineGrade4 set C='SLLO' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like 'V*BS*' and code not in (Select REQ from REQ_VEBS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')and (code like '??1*' or code like '??2*')" 'VEBS Select Upper Division (2 course) CurrentDb.Execute "Update CombineGrade4 set B='SLUP' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and (code like '??3*' or code like '??4*') and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like 'VE*BS*' and code not in (Select REQ from REQ_VEBS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" CurrentDb.Execute "Update CombineGrade4 set C='SLUP' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and (code like '??3*' or code like '??4*') and A is null and CrErn >0 and progverscode like 'VE*BS*' and code not in (Select REQ from REQ_VEBS) and code not like 'HU*' and code not like 'SB*' and code not like 'MS*' and code not like '*A' and code not like '*B' and code not like '*AN' and code not like '*BN')" '********************BS Degree HU, MS, SB, GE requirement************************** 'HU3x (2 courses) CurrentDb.Execute "Update CombineGrade4 set B='HU3X' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code like 'HU3*')" CurrentDb.Execute "Update CombineGrade4 set C='HU3X' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like '*B*' and code like 'HU3*')" 'HU (1 course) CurrentDb.Execute "Update CombineGrade4 set B='HU' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and B='' and (code like 'HU2*' or code like 'HU3*'))" CurrentDb.Execute "Update CombineGrade4 set C='HU' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like '*B*' and C='' and (code like 'HU2*' or code like 'HU3*'))" 'MS (2 courses for DFVBS, FDBFA, FMBS, GADBS GRBS, IMDBS, INDBS, MAABS, VFXGBS) CurrentDb.Execute "Update CombineGrade4 set B='MS' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode in ('DFV_BS_001', 'FD__BFA001','FMM_BS_001', 'GAD_BS_007', 'GR__BS__001', 'IMD_BS_001', 'IND_BS_001', 'MAA_BS_007', 'SD__BS_001', 'SD__BS_002', 'VFXGBS_001', 'VFXGBS_002', 'WDIMBS_003') and code<>'MS090' and code like 'MS*')" CurrentDb.Execute "Update CombineGrade4 set C='MS' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode in ('DFV_BS_001', 'FD__BFA001','FMM_BS_001', 'GAD_BS_007', 'GR__BS__001', 'IMD_BS_001', 'IND_BS_001', 'MAA_BS_007', 'SD__BS_001', 'SD__BS_002', 'VFXGBS_001', 'VFXGBS_002', 'WDIMBS_003') and code<>'MS090' and code like 'MS*')" 'MS (1 course for CULMBS, DPHBS, VGPBS) CurrentDb.Execute "Update CombineGrade4 set B='MS' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode in ('CULMBS_009', 'DPH_BS_004', 'VGP_BA_001') and code<>'MS090' and code like 'MS*')" CurrentDb.Execute "Update CombineGrade4 set C='MS' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode in ('CULMBS_009', 'DPH_BS_004', 'VGP_BA_001') and code<>'MS090' and code like 'MS*')" 'SB3X (1 course) CurrentDb.Execute "Update CombineGrade4 set B='SB3X' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code like 'SB3*')" CurrentDb.Execute "Update CombineGrade4 set C='SB3X' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like '*B*' and code like 'SB3*')" 'SB (2 courses) CurrentDb.Execute "Update CombineGrade4 set B='SB' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and B='' and code like 'SB*')" CurrentDb.Execute "Update CombineGrade4 set C='SB' where rec in(select top 2 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like '*B*' and C='' and code like 'SB*')" 'GE (3 courses) CurrentDb.Execute "Update CombineGrade4 set B='GE' where rec in(select top 3 rec from CombineGrade4 where stuid=" & ID & "and A is null and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and B='' and code in (select REQ from GE))" CurrentDb.Execute "Update CombineGrade4 set C='GE' where rec in(select top 3 rec from CombineGrade4 where stuid=" & ID & "and A is null and CrErn >0 and progverscode like '*B*' and C='' and code in (select REQ from GE))" Next ID MsgBox "Update 4 Done" End Sub Sub Update5() CurrentDb.Execute "Update CombineGrade4 set TruErn=0" CurrentDb.Execute "Update CombineGrade4 set TruErn=CrErn where (C<>'' or C is null)" CurrentDb.Execute "Update CombineGrade4 set Taking=0" CurrentDb.Execute "UPdate CombineGrade4 set Taking=Credit where (adGradeLetterCode='' or adGradeLetterCode is null) and (B<>'' or B is null)" Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("CombineGrade4") If rs.BOF And rs.EOF Then Exit Sub End If rs.MoveFirst Cumulate = 0 Same_SSN = 0 strSSN = "SSNSSN" rs.MoveFirst If rs.EOF() Then Exit Sub End If Do Until rs.EOF() If rs!SSN = strSSN Then Same_SSN = 1 'if the SSN is same as prev Cumulate = Cumulate + rs!TruErn rs.Edit rs!Cum = Cumulate rs.Update Else Same_SSN = 0 'if not same SSN as prev Cumulate = 0 Cumulate = Cumulate + rs!TruErn rs.Edit rs!Cum = Cumulate rs.Update End If strSSN = rs!SSN rs.MoveNext Loop rs.MoveLast rs.MoveFirst CurrentDb.Execute "Update CombineGrade4 set Tem = Cum - TruErn" CurrentDb.Execute "Update CombineGrade4 set GL=1 where Tem between 0 and 35" CurrentDb.Execute "Update CombineGrade4 set GL=2 where Tem between 36 and 95" CurrentDb.Execute "Update CombineGrade4 set GL=3 where Tem between 96 and 143" CurrentDb.Execute "Update CombineGrade4 set GL=4 where Tem>=144" Set rs = Nothing MsgBox "Update 5 Done" End Sub Public Function isTableExists(strTableName As String) As Boolean On Error Resume Next isTableExists = IsObject(CurrentDb.TableDefs(strTableName)) End Function