Sub TempGrade() 'Last updated 4/28/11 'Gather grades for a student. Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("TempMaster") If rs.BOF And rs.EOF Then Exit Sub End If rs.MoveFirst CurrentDb.Execute "Delete from TempGrade" SSN = rs!SSN SSN = "'" & SSN & "'" CurrentDb.Execute "Insert into TempGrade (SSN, TermCode, Code, adGradeLetterCode) select SSN, Termcode, Code, adGradeLetterCode from CombineGrade where SSN =" & SSN End Sub Sub TempGrade2() CurrentDb.Execute "Delete from TempGrade2" CurrentDb.Execute "Insert into TempGrade2(SSN, TermCode, Code, AdgradeLetterCode) Select distinct SSN, TermCode, Code, AdgradeLetterCode from TempGrade" CurrentDb.Execute "Update TempGrade2 set B='', C=''" CurrentDb.Execute "Update TempGrade2 set txtID=Left(ssn,3) + Mid(ssn,5,2) + Mid(ssn,8,4)" CurrentDb.Execute "Update TempGrade2 set StuID=val(txtID)" CurrentDb.Execute "Update TempGrade2 set Credit=3" CurrentDb.Execute "Update TempGrade2 set Credit=4 where code like 'HU*' or code like 'SB*' or code like 'MS*' or code like 'IS*'" CurrentDb.Execute "Update TempGrade2 set Credit=3 where code like '*090*'" CurrentDb.Execute "Update TempGrade2 set Credit=2 where code in ('FD3337', 'FS497', 'GD4413', 'MM4403', 'ID4415')" CurrentDb.Execute "Update TempGrade2 set Credit=6 where code in (select CourseCode from SixCR)" CurrentDb.Execute "Update TempGrade2 set CrAtt=Credit where mid(trim(adGradeLetterCode),1,1) in ('A','B','C','D','F','I','W','')" CurrentDb.Execute "Update TempGrade2 set CrAtt=0 where mid(trim(adGradeLetterCode),1,1) in ('T','P','K')" CurrentDb.Execute "Update TempGrade2 set CrErn=Credit where mid(trim(adGradeLetterCode),1,1) in ('A','B','C','D','T','P','K')" CurrentDb.Execute "Update TempGrade2 set CrErn=0 where mid(trim(adGradeLetterCode),1,1) in ('F','I','W','R','')" CurrentDb.Execute "Update TempGrade2 set CrErn_=Credit where mid(trim(adGradeLetterCode),1,1) in ('A','B','C','D','T','P','K')" CurrentDb.Execute "Update TempGrade2 set CrErn_=0 where mid(trim(adGradeLetterCode),1,1) in ('F','I','W','R','')" CurrentDb.Execute "Update TempGrade2 set CrErn_=0 where Code like '*090'" Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("TempGrade2") 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 TempGrade4" CurrentDb.Execute "Insert into TempGrade4(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 TempGrade3" CurrentDb.Execute "Update TempGrade4 set CodeType = 'MC'" CurrentDb.Execute "Update TempGrade4 set CodeType = 'GE' where Code like 'HU*' or Code like 'SB*' or Code like 'MS*' or Code like 'IS*'" End Sub Sub TempGrade4() '01--CULAS must take course CurrentDb.Execute "Update TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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 TempGrade4 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)" '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ '01 CULAS Elective (1 course) CurrentDb.Execute "Update TempGrade4 set B='EL' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='MS' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='SB' where rec in(select top 1 rec from TempGrade4 where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*AS*' and code like 'SB*')" 'GE (1 course) CurrentDb.Execute "Update TempGrade4 set B='GE' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL001' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL002' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL003' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL001' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL002' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL003' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL001' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL002' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL001' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL002' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL003' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL001' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL002' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL003' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL001' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL002' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL001' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL002' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL003' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL001' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL002' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL003' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL001' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL002' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL003' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL001' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL002' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL001' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL002' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL003' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL004' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL001' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL002' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL001' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL002' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL003' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL001' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL002' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='EL003' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='HU3X1' where rec in(select top 1 rec from TempGrade4 where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code like 'HU3*')" CurrentDb.Execute "Update TempGrade4 set B='HU3X2' where rec in(select top 1 rec from TempGrade4 where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code like 'HU3*')" 'HUXXX (1 course) CurrentDb.Execute "Update TempGrade4 set B='HU' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='MS3' where rec in(select top 1 rec from TempGrade4 where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code like 'MS3*')" 'MSXXX (1 course) CurrentDb.Execute "Update TempGrade4 set B='MS' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='SB3' where rec in(select top 1 rec from TempGrade4 where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code like 'SB3*')" 'SBXXX (2 courses) CurrentDb.Execute "Update TempGrade4 set B='SBX1' where rec in(select top 1 rec from TempGrade4 where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code like 'SB*')" CurrentDb.Execute "Update TempGrade4 set B='SBX2' where rec in(select top 1 rec from TempGrade4 where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code like 'SB*')" 'GE3XX (2 courses) CurrentDb.Execute "Update TempGrade4 set B='GE3X1' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='GE3X2' where rec in(select top 1 rec from TempGrade4 where 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 TempGrade4 set B='GE' where rec in(select top 1 rec from TempGrade4 where A is null and B='' and (CrErn>0 or adGradeLetterCode='') and progverscode like '*B*' and code in (select REQ from GE))" CurrentDb.Execute "Update TempGrade4 set C = B where CrErn > 0" CurrentDb.Execute "Update TempGrade4 set D = Code where B = 'Y'" CurrentDb.Execute "Update TempGrade4 set D = B where B <> 'Y'" '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ CurrentDb.Execute "Update TempGrade4 set TruErn=0" CurrentDb.Execute "Update TempGrade4 set TruErn=CrErn where (C<>'' or C is null)" CurrentDb.Execute "Update TempGrade4 set Taking=0" CurrentDb.Execute "UPdate TempGrade4 set Taking=Credit where (adGradeLetterCode='' or adGradeLetterCode is null) and B<>''" Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("TempGrade4") 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 TempGrade4 set Tem = Cum - TruErn" CurrentDb.Execute "Update TempGrade4 set GL=1 where Tem between 0 and 35" CurrentDb.Execute "Update TempGrade4 set GL=2 where Tem between 36 and 89" CurrentDb.Execute "Update TempGrade4 set GL=3 where Tem between 90 and 134" CurrentDb.Execute "Update TempGrade4 set GL=4 where Tem>=135" Set rs = Nothing MsgBox "TempGrade4 Done" End Sub