Skip to Main Content

Knowledge base

Knowledge Base
Knowledge Base - Download "Census" grading to spreadsheet
by Alice Moore
01/19/2011 (Software)


UPDATED 1-19-2011


To find which sections did not mark ANY 'R' grades for census:
(the SCS.STC.STATUS of 1 and 2 represents the "special processing codes" for "N"ew or "A"dded courses) This also throws out the Labs.
===============================================================================
SELECT STUDENT.COURSE.SEC WITH SCS.TERM EQ '09/FA'
SELECT STUDENT.COURSE.SEC WITH SCS.STC.STATUS EQ '1' '2'
SELECT STUDENT.COURSE.SEC WITH SCS.STC.CRED.TYPE NE 'L'

if you also want to exclude the C&T sections then do the following SELECT before saving otherwise go directly to the SAVE.LIST command:

SELECT STUDENT.COURSE.SEC WITH STC.SUBJECT NE 'C&T'

SAVE.LIST AM.SCSJUNK
SETPTR ,,0,0,0,3,BANNER REGFLAGS.TXT,BRIEF,NHEAD
GET.LIST AM.SCSJUNK
LIST STUDENT.COURSE.SEC BY W04.SCS.CENSUS.COURSE BY SCS.MID.TERM.GRADE1 BREAK.ON "'T'"  W04.SCS.CENSUS.COURSE BREAK.ON "'T'"  SCS.MID.TERM.GRADE1  TOTAL COUNTER DET.SUP HDR.SUP NI.SUP COL.SUP LPTR
SELECT.ONLY

  1. Use the Options|Import drop-down in UI4.x to move file to your PC directory. 
    You will enter REGFLAGS.TXT in the Items and Local File fields.
    be sure ASCCI is selected and Suppress ID checked
  2. Open  new sheet in Excel, on DATA tab, select  "From Text" on the "Get External Data" menu. 
  3. In the Text Wizard , choose fixed width, then Next, -
  4. Move first break line to position 36, Click FINISH , then OK
  5. Select entire spreadsheet, then Choose Remove Duplicates option on DATA tab, select columns A & C (unselect B)
  6. Then Sort by Col A  (this puts null rows to the bottom) -
  7. Delete totals rows at bottom
  8. Select entire spreadsheet again and  sort by col B (this puts all courses with no grade mark to the bottom
  9. Highlight/Select the rows in columns A-C  for those with no grade mark in column B (field is null) and cut
  10. Go to top of spreadsheet, paste into columns G-I (then auto-fit columns)
  11. Delete column H (it is blank)
  12. Select columns A-C, on Formulas tab, choose Define Name, and name block as CRS
  13. With cursor in col I1, insert a VLOOKUP formula by typing  =VLOOKUP(G1,CRS,2,FALSE) , then drag formula down thu all rows.
  14. Go to top of spreadsheet
  15. Copy Col I and Paste Special | Values into Col J, then delete Col I
  16. Use the Replace function (on Home tab | Find & Select) in Col I to get rid of #N/A
  17. Select Columns G-I and sort on column I (all of the sections with no grade mark of 1 in col I sorts to the bottom)
  18. Cut the rows in columns G-H starting in the row where there is no grade mark of 1 in col I
  19. Go to top of spreadsheet and Paste into columns L-M - (then Autofit selection) - These are the sections for which no marks were entered for Census.

=============================================================================

TO CREATE THE "HIT LIST" BY STUDENT (showing number of  "grades" marked)
=====================================================================
SELECT STUDENT.COURSE.SEC WITH SCS.TERM EQ '10/FA' WITH SCS.STATUS EQ 'A'
SAVE.LIST AM.CENSUS   --> (4855 RECS)

Check if the unique student count in STUDENT.COURSE.SEC matches the unique student count in STUDENT.TERMS OF PRE-REGD:

SELECT STUDENT.TERMS WITH STTR.TERM EQ '10/FA' WITH STTR.CURRENT.STATUS EQ 'P''R' SAVING UNIQUE STTR.STUDENT  (xxx records)

GET.LIST AM.CENSUS
SELECT STUDENT.COURSE.SEC SAVING UNIQUE SCS.STUDENT (xxx records)

These 2 record counts should match.


To get list of those with at least 1 hit!
SETPTR ,,0,0,0,3,BANNER FALLHITS.TXT,BRIEF,NHEAD
GET.LIST AM.CENSUS
SELECT STUDENT.COURSE.SEC WITH SCS.MID.TERM.GRADE1
LIST STUDENT.COURSE.SEC BY SCS.STUDENT  ID.SUP BREAK.ON SCS.STUDENT W04.SCS.STU.NAME DET.SUP TOTAL COUNTER HDR.SUP COL.SUP SELECT.ONLY LPTR

Now get list of all  students (as selected from STUDENT.TERMS above) and list their demographic data so you can merge hit count into it:

*** Beginning in SPRING 2011 - PRODUCE BELOW DATA FOR ALL TRANSCRIPTED STUDENTS FROM Fall term instead of just those that had pre-registered.  They would like ALL students to be in the final spreadsheet, not just those that have student course sec records.  ****

NEW QUERIES:

SELECT STUDENTS WITH W04.STU.TERMSTAT EQ '10/FA*T'
SAVE.LIST AM.ALLFALL

GET.LIST AM.ALLFALL
SSELECT STUDENTS
DOWNLOAD STUDENTS FORMAT QUOTE FILE USER.HOLD FALLSTUDS.TXT OVERWRITING @ID STU.FIRST.NAME STU.MIDDLE.NAME STU.LAST.NAME W04.STU.EMAIL W04.STU.STUD.PHONE W04.STU.LOCAL.ADDRESS1 W04.STU.ADVISOR.NAME  STU.RESTRICTIONS NUM.VALUES ALL MV.ORIENTATION HORIZONTAL
==================================================================

OLD QUERY using only pre-registered students):
GET.LIST AM.CENSUS
SELECT STUDENT.COURSE.SEC SAVING UNIQUE SCS.STUDENT
SSELECT STUDENTS
DOWNLOAD STUDENTS FORMAT QUOTE FILE USER.HOLD FALLSTUDS.TXT OVERWRITING @ID STU.FIRST.NAME STU.MIDDLE.NAME STU.LAST.NAME W04.STU.EMAIL W04.STU.STUD.PHONE W04.STU.LOCAL.ADDRESS1 W04.STU.ADVISOR.NAME  STU.RESTRICTIONS NUM.VALUES ALL MV.ORIENTATION HORIZONTAL
=====================================================================

Use the Options|Import drop-down in UI4.x to move the all students data and the hit list data to your PC directory. 


Import the "all student" list into Excel sheet

Open a new sheet and import in the Hit list

Copy the 3 columns from the "hit" list sheet and paste into the "all student" sheet and on the Formulas ribbon, use Define Name to name this block of data HITS

Add the following formulat in a new column   =VLOOKUP(A1,HITS,3,FALSE)

Then copy this column to a new coplumn using Paste Special - Values and then change the value #N/A to 0

You can now delete the "formula" column and the HITS columns

Move the "hits" column and the P1 & P2 columns to the front of the spreadsheet before the email column.

Add column headers

Sort the spreadsheet by Hits (small to Lg) then by P1 (Z to A) then by Last, first, middle columns

Save and email to Registrar.