Sub Update1() 'Code last updated on 4/30/2011 'Need Tables: Master, TranscriptGrade, and FinalGrade. 'TranscriptGrade Columes: StudentName, SSN, TermCode, Code, AdGradeLetterCode 'CombineGrade Columes: SSN, TermCode, Code, AdGradeLetterCode. 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,6)" CurrentDb.Execute "Update FinalGrade set TermCode = left(Termcode,6)" CurrentDb.Execute "Update FinalGrade set TermCode = left(TermCode,4) & '01' where right(TermCode,2) = '02'" CurrentDb.Execute "Update FinalGrade set TermCode = left(TermCode,4) & '04' where right(TermCode,2) = '05'" CurrentDb.Execute "Update FinalGrade set TermCode = left(TermCode,4) & '07' where right(TermCode,2) = '08'" CurrentDb.Execute "Update FinalGrade set TermCode = left(TermCode,4) & '10' where right(TermCode,2) = '11'" CurrentDb.Execute "Update TranscriptGrade set TermCode = left(TermCode,4) & '01' where right(TermCode,2) = '02'" CurrentDb.Execute "Update TranscriptGrade set TermCode = left(TermCode,4) & '04' where right(TermCode,2) = '05'" CurrentDb.Execute "Update TranscriptGrade set TermCode = left(TermCode,4) & '07' where right(TermCode,2) = '08'" CurrentDb.Execute "Update TranscriptGrade set TermCode = left(TermCode,4) & '10' where right(TermCode,2) = '11'" CurrentDb.Execute "Update FinalGrade set TermCode = '200804' where TermCode = '200803'" CurrentDb.Execute "Update FinalGrade set TermCode = '200810' where TermCode = '200809'" CurrentDb.Execute "Update TranscriptGrade set TermCode = '200804' where TermCode = '200803'" CurrentDb.Execute "Update TranscriptGrade set TermCode = '200810' where TermCode = '200809'" CurrentDb.Execute "Update TranscriptGrade set TermCode = TermCode & 'QF'" 'QF: Quarter Full CurrentDb.Execute "Update FinalGrade set TermCode = TermCode & 'QF'" '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 "Delete from TranscriptGrade where Code like '*A'" CurrentDb.Execute "Delete from TranscriptGrade where Code like '*AN'" CurrentDb.Execute "Delete from TranscriptGrade where Code like '*B'" CurrentDb.Execute "Delete from TranscriptGrade where Code like '*BN'" CurrentDb.Execute "Delete from FinalGrade where Code like '*A'" CurrentDb.Execute "Delete from FinalGrade where Code like '*AN'" CurrentDb.Execute "Delete from FinalGrade where Code like '*B'" CurrentDb.Execute "Delete from FinalGrade where Code like '*BN'" 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 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 mid(trim(adGradeLetterCode),1,1) in ('A','B','C','D','F','I','W','')" CurrentDb.Execute "Update CombineGrade2 set CrAtt=0 where mid(trim(adGradeLetterCode),1,1) in ('T','P','K')" CurrentDb.Execute "Update CombineGrade2 set CrErn=Credit where mid(trim(adGradeLetterCode),1,1) in ('A','B','C','D','T','P','K')" CurrentDb.Execute "Update CombineGrade2 set CrErn=0 where mid(trim(adGradeLetterCode),1,1) in ('F','I','W','R','')" CurrentDb.Execute "Update CombineGrade2 set CrErn_=Credit where mid(trim(adGradeLetterCode),1,1) in ('A','B','C','D','T','P','K')" CurrentDb.Execute "Update CombineGrade2 set CrErn_=0 where mid(trim(adGradeLetterCode),1,1) in ('F','I','W','R','')" CurrentDb.Execute "Update CombineGrade2 set CrErn_=0 where Code like '*090'" 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, CrErn_, TruErn, Taking, StuID, Rec, A, B, C, Cum, Tem, GL) Select StudentName, SSN, Progverscode, Req, Termcode, Code, adGradeLetterCode, Credit, CrAtt, CrErn, CrErn_, TruErn, Taking, StuID, Rec, A, B, C, Cum, Tem, GL from CombineGrade3" CurrentDb.Execute "Update CombineGrade4 set CodeType = 'MC'" CurrentDb.Execute "Update CombineGrade4 set CodeType = 'GE' where Code like 'HU*' or Code like 'SB*' or Code like 'MS*' or Code like 'IS*'" MsgBox "Update 1 Done" End Sub Sub Update2() '01--CULAS must take course CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'CUL_AS*' and code in (select REQ from REQ_CULAS)" '02--CULBMS must take course CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'CULMBS*' and code in (select REQ from REQ_CULMBS)" '03--DFVBS must take course CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'DFV_BS*' and code in (select REQ from REQ_DFVBS)" '04--DPHAS must take course CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'DPH_AS*' and code in (select REQ from REQ_DPHAS)" '05--DPHBS must take course CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'DPH_BS*' and code in (select REQ from REQ_DPHBS)" '06-- FDAS must take coure CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FD*AS*' and code in (select REQ from REQ_FDAS)" '07-- FDBFA must take coure CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FD*BFA*' and code in (select REQ from REQ_FDBFA)" '08-- FMAS must take coure CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FM*AS*' and code in (select REQ from REQ_FMAS)" '09-- FMBS must take coure CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FM*BS*' and code in (select REQ from REQ_FMBS)" '10-- GADBS must take coure CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'GAD_BS*' and code in (select REQ from REQ_GADBS)" '11-- GRAS must take coure CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'GR*AS*' and code in (select REQ from REQ_GRAS)" '12-- GRBS must take coure CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'GR*BS*' and code in (select REQ from REQ_GRBS)" '13-- IDBS must take coure CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'ID*BS*' and code in (select REQ from REQ_IDBS)" '14-- IMDAS (WDIMAS) must take coure CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*IM*AS*' and code in (select REQ from REQ_IMDAS)" '15-- IMDBS (WDIMBS) must take coure CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*IM*BS*' and code in (select REQ from REQ_IMDBS)" '16-- INDBS must take coure CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'IND_BS*' and code in (select REQ from REQ_INDBS)" '17-- MAABS must take coure CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'MAA_BS*' and code in (select REQ from REQ_MAABS)" '18-- SDBS must take coure CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'SD*BS*' and code in (select REQ from REQ_SDBS)" '19-- VEBS must take coure CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'V*BS*' and code in (select REQ from REQ_VEBS)" '20-- VGPBS must take coure CurrentDb.Execute "Update CombineGrade4 set B='Y' where A is null and B='' and (CrErn>0 or adGradeLetterCode='') 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 '01 CULAS Elective (1 course) CurrentDb.Execute "Update CombineGrade4 set B='EL' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'CUL_AS*' and code not in (Select REQ from REQ_CULAS) and CodeType='MC')" '02 DPHAS Elective (1 course) CurrentDb.Execute "Update CombineGrade4 set B='EL' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'DPH_AS*' and code not in (Select REQ from REQ_DPHAS) and CodeType='MC')" '03 FDAS Elective (1 course) CurrentDb.Execute "Update CombineGrade4 set B='EL' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FD*AS*' and code not in (Select REQ from REQ_FDAS) and CodeType='MC')" '04 FMAS Elective (1 course) CurrentDb.Execute "Update CombineGrade4 set B='EL' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FM*AS*' and code not in (Select REQ from REQ_FMAS) and CodeType='MC')" '05 GRAS Elective (1 course) CurrentDb.Execute "Update CombineGrade4 set B='EL' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'GR*AS*' and code not in (Select REQ from REQ_GRAS) and CodeType='MC')" '06 IMDAS Elective (1 course) CurrentDb.Execute "Update CombineGrade4 set B='EL' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*IM*AS*' and code not in (Select REQ from REQ_IMDAS) and CodeType='MC')" '********************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 B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*AS*' and code<>'MS090' and code like 'MS*')" 'SB (1 course) CurrentDb.Execute "Update CombineGrade4 set B='SB' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*AS*' and code like 'SB*')" 'GE (1 course) CurrentDb.Execute "Update CombineGrade4 set B='GE' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*AS*' and code in (select REQ from GE))" '********************************************************************************** '07 CULMBS Elective(3 courses) CurrentDb.Execute "Update CombineGrade4 set B='EL001' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'CULMBS*' and code not in (Select REQ from REQ_CULMBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL002' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'CULMBS*' and code not in (Select REQ from REQ_CULMBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL003' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'CULMBS*' and code not in (Select REQ from REQ_CULMBS) and CodeType='MC')" '08 DFVBS Elective (3 courses) CurrentDb.Execute "Update CombineGrade4 set B='EL001' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'DFV_BS*' and code not in (Select REQ from REQ_DFVBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL002' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'DFV_BS*' and code not in (Select REQ from REQ_DFVBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL003' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'DFV_BS*' and code not in (Select REQ from REQ_DFVBS) and CodeType='MC')" '09 DPHBS Elective (2 courses) CurrentDb.Execute "Update CombineGrade4 set B='EL001' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'DPH_BS*' and code not in (Select REQ from REQ_DPHBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL002' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'DPH_BS*' and code not in (Select REQ from REQ_DPHBS) and CodeType='MC')" '10 FDBFA Elective (3 courses) CurrentDb.Execute "Update CombineGrade4 set B='EL001' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FD*BFA*' and code not in (Select REQ from REQ_FDBFA) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL002' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FD*BFA*' and code not in (Select REQ from REQ_FDBFA) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL003' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FD*BFA*' and code not in (Select REQ from REQ_FDBFA) and CodeType='MC')" '11 FMBS Elective (3 courses) CurrentDb.Execute "Update CombineGrade4 set B='EL001' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FM*BS*' and code not in (Select REQ from REQ_FMBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL002' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FM*BS*' and code not in (Select REQ from REQ_FMBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL003' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'FM*BS*' and code not in (Select REQ from REQ_FMBS) and CodeType='MC')" '12 GADBS Elective (2 courses) CurrentDb.Execute "Update CombineGrade4 set B='EL001' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'GAD_BS*' and code not in (Select REQ from REQ_GADBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL002' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'GAD_BS*' and code not in (Select REQ from REQ_GADBS) and CodeType='MC')" '13 GRBS Elective (3 courses) CurrentDb.Execute "Update CombineGrade4 set B='EL001' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'GR*BS*' and code not in (Select REQ from REQ_GRBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL002' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'GR*BS*' and code not in (Select REQ from REQ_GRBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL003' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'GR*BS*' and code not in (Select REQ from REQ_GRBS) and CodeType='MC')" '14 IDBS Elective (3 courses) CurrentDb.Execute "Update CombineGrade4 set B='EL001' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'ID*BS*' and code not in (Select REQ from REQ_IDBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL002' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'ID*BS*' and code not in (Select REQ from REQ_IDBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL003' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'ID*BS*' and code not in (Select REQ from REQ_IDBS) and CodeType='MC')" '15 IMDBS (WDIMBS) Elective (3 courses) CurrentDb.Execute "Update CombineGrade4 set B='EL001' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*IM*BS*' and code not in (Select REQ from REQ_IMDBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL002' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*IM*BS*' and code not in (Select REQ from REQ_IMDBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL003' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*IM*BS*' and code not in (Select REQ from REQ_IMDBS) and CodeType='MC')" '16 INDBS Elective (2 courses) CurrentDb.Execute "Update CombineGrade4 set B='EL001' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'IND_BS*' and code not in (Select REQ from REQ_INDBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL002' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'IND_BS*' and code not in (Select REQ from REQ_INDBS) and CodeType='MC')" '17 MAABS Elective (4 courses) CurrentDb.Execute "Update CombineGrade4 set B='EL001' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'MAA_BS*' and code not in (Select REQ from REQ_MAABS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL002' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'MAA_BS*' and code not in (Select REQ from REQ_MAABS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL003' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'MAA_BS*' and code not in (Select REQ from REQ_MAABS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL004' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'MAA_BS*' and code not in (Select REQ from REQ_MAABS) and CodeType='MC')" '18 SDBS Elective (2 courses) CurrentDb.Execute "Update CombineGrade4 set B='EL001' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'SD*BS*' and code not in (Select REQ from REQ_SDBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL002' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'SD*BS*' and code not in (Select REQ from REQ_SDBS) and CodeType='MC')" '19 VEBS Elective (3 courses) CurrentDb.Execute "Update CombineGrade4 set B='EL001' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'V*BS*' and code not in (Select REQ from REQ_VEBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL002' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'V*BS*' and code not in (Select REQ from REQ_VEBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL003' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'V*BS*' and code not in (Select REQ from REQ_VEBS) and CodeType='MC')" '20 VGPBS Elective (3 courses) CurrentDb.Execute "Update CombineGrade4 set B='EL001' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'VGP_B*' and code not in (Select REQ from REQ_VGPBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL002' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'VGP_B*' and code not in (Select REQ from REQ_VGPBS) and CodeType='MC')" CurrentDb.Execute "Update CombineGrade4 set B='EL003' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like 'VGP_B*' and code not in (Select REQ from REQ_VGPBS) and CodeType='MC')" '********************BS Degree HU, MS, SB, GE requirement************************** 'HU3XX (2 courses) CurrentDb.Execute "Update CombineGrade4 set B='HU3X1' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code like 'HU3*')" CurrentDb.Execute "Update CombineGrade4 set B='HU3X2' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code like 'HU3*')" 'HUXXX (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 B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code<>'HU090' and code like 'HU*')" 'MS3XX (1 course) CurrentDb.Execute "Update CombineGrade4 set B='MS3' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code like 'MS3*')" 'MSXXX (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 B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code<>'MS090' and code like 'MS*')" 'SB3XX (1 course) CurrentDb.Execute "Update CombineGrade4 set B='SB3' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code like 'SB3*')" 'SBXXX (2 courses) CurrentDb.Execute "Update CombineGrade4 set B='SBX1' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code like 'SB*')" CurrentDb.Execute "Update CombineGrade4 set B='SBX2' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code like 'SB*')" 'GE3XX (2 courses) CurrentDb.Execute "Update CombineGrade4 set B='GE3X1' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code in (select REQ from GE where REQ like '??3*'))" CurrentDb.Execute "Update CombineGrade4 set B='GE3X2' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code in (select REQ from GE where REQ like '??3*'))" 'GEXXX (1 course) CurrentDb.Execute "Update CombineGrade4 set B='GE' where rec in(select top 1 rec from CombineGrade4 where stuid=" & ID & "and A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code in (select REQ from GE))" Next ID CurrentDb.Execute "Update CombineGrade4 set C = B where CrErn > 0" CurrentDb.Execute "Update CombineGrade4 set D = Code where B = 'Y'" CurrentDb.Execute "Update CombineGrade4 set D = B where B <> 'Y'" 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 89" CurrentDb.Execute "Update CombineGrade4 set GL=3 where Tem between 90 and 134" CurrentDb.Execute "Update CombineGrade4 set GL=4 where Tem >= 135" 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