Enrollment Information

Aeries Queries

Enrollment Information

Student ID Data / LIST STU STU.ID STU.CID STU.FN STU.MN STU.LN STU.SF STU.FNA STU.MNA STU.LNA STU.BD STU.SX STU.BCY STU.BST STU.BCU STU.HL STU.SP STU.GR STU.ED STU.LD STU.SWR STU.EOY STU.HSG
Name Validations / Only alphabetic letter, numbers, periods, hyphens and apostrophe
LIST STU STU.ID STU.CID STU.FN STU.MN STU.LN STU.SF STU.FNA STU.MNA STU.LNA
Missing Last Name Alias / If the first name is populated then the last name must be populated
LIST STU STU.ID STU.CID STU.FN STU.MN STU.LN STU.SF STU.FNA STU.MNA STU.LNA IF STU.FNA # " " AND STU.LNA = " "
Missing First Name Alias / If last name is populated then the first name must be populated
LIST STU STU.ID STU.CID STU.FN STU.MN STU.LN STU.SF STU.FNA STU.MNA STU.LNA IF STU.LNA # " " AND STU.FNA = " "
Suffix Check / Suffix: I, II, III, IV, V, VI, JR, SR
LIST STU STU.LN STU.FN STU.SF BY STU.SF IF STU.SF # " "
Birth Date / Cannot be blank or out of range
LIST STU STU.LN STU.FN STU.BD BY STU.BD
Birth Country / Birth country cannot be blank
LIST STU STU.ID STU.LN STU.FN STU.BST STU.BCU
CA must have US birth Country
LIST STU STU.ID STU.LN STU.FN STU.BST STU.BCU IF STU.BST = 'CA' AND STU.BCU # 'US'
Birth State and Birth Country cannot be the same
LIST STU STU.ID STU.LN STU.FN STU.BST STU.BCU IF STU.BCU = STU.BST
Address / All address fields must be complete and follow postal standard
http://pe.usps.gov/cpim/ftp/pubs/Pub28/pub28.pdf
LIST STU STU.LN STU.FN STU.ID STU.AD STU.CY STU.ST STU.ZC
Parent Ed / Cannot be blank. Valid range is 1 - 6
LIST STU LN FN ID PED TG BY PED IF ( PED < 1 OR PED > 6 ) AND TG # * AND TG # C
Initial 9th Grade Entry / LIST STU LN FN ID GR INE IF INE = " " AND GR > 8 AND GR < 13
Home Language / All students must have a home language
LIST STU STU.ID STU.LN STU.FN STU.HL BY STU.HL
District and school mobility / School mobility cannot be greater than district mobility. School mobility is the grade when student entered school. District mobility is the grade when student entered district.
LIST STU STU.LN STU.FN STU.GR STU.SM STU.DM IF STU.SM < STU.DM
Leave date less than enter date / LIST STU STU.ID STU.LN STU.FN STU.ED STU.LD STU.SWR STU.EOY STU.HSG STU.TG IF STU.LD < STU.ED
Exit code in attendance leave record / LIST STU ATT STU.ID STU.LN STU.FN STU.ED STU.LD STU.SWR STU.EOY STU.HSG STU.TG ATT.CD ATT.RS ATT.DT IF ATT.CD > E AND STU.LD > 01/01/1990

Language Information

Fields used for Language information/status / STU.BCU STU.HL STU.LF LAC.RD1 LAC.SD LAC.IFD LAC.RD1 LAC.ELP STU.BPS LAC.USS SSD.SUS MST.ESR, MST.INS, MST.LI
Initial US School Enrollment
LAC.USS / The date a student first enrolled in a K-12 public or private school.
LIST STU LAC STU.FN STU.LN STU.LF STU.TG LAC.USS STU.BCU BY LAC.USS IF STU.BCU # US AND STU.BCU # PR AND STU.TG # *
In US less than 3 cumulative years
SSD.SUS / Must manually update SSD.SUS with Y or N. Has the student been enrolled for less than 3 cumulative years? Check cumm for prior enrollment. Change 01/01/2007 to appropriate 3 year date.
LIST STU SSD LAC STU.ID STU.LN STU.FN STU.GR STU.BCU LAC.USS SSD.SUS IF STU.BCU # US AND LAC.USS > 01/01/2007 AND SSD.SUS < 0
English Language Acquisition Start Dates / Displays all language fluency codes. If STU.LF is not English Only then the acquisition start date is required.
LIST STU LAC STU.N STU.FN STU.ID STU.CID STU.LF STU.BCU STU.DD LAC.RD1 LAC.SD LAC.IFD LAC.ELP LAC.USS IF STU.LF > 0
English Language Acquisition Start Dates LF = L (EL) / EL – English Learners. First date that an English language acquisition status state for a specific student became effective. If STU.LF is not English Only then the acquisition start date is required (LAC.SD).
LIST STU LAC STU.LN STU.FN STU.ID STU.CID STU.LF STU.BCU STU.DD LAC.USS LAC.SD BY LAC.SD IF STU.LF = L
English Language Acquisition Start Date LF = I (IFEP) / IFEP Students – First date that an English language acquisition status state for a specific student became effective. If STU.LF is not English Only then the acquisition start date is required (LAC.IFD).
LIST STU LAC STU.LN STU.FN STU.ID STU.CID STU.LF STU.BCU STU.DD LAC.USS LAC.IFD BY LAC.IFD IF STU.LF = I
English Language Acquisition Start Date = Redesignated (RFEP) / See Jane Luick – She will provide dates – Sites do not update. If STU.LF is not English Only then the acquisition start date is required.
LIST STU LAC STU.LN STU.FN STU.ID STU.CID STU.LF STU.BCU STU.DD LAC.USS LAC.RD1 BY LAC.RD1 IF STU.LF = R
Incorrect LF setting / If the home language is English, students should not have a language fluency (LF) setting.
LIST STU LN FN HL LF IF HL = ‘00’ AND LF # “”
If the home language is not English, students need a language fluency setting
LIST STU LN FN HL LF IF HL > 0 AND LF = “”

Special Populations

Homeless (program file) / Set SSD.HMT = 09 – 13 Program entry must have participating start date. CALPADS does backward check – if PGM code exists then SSD.HMT must be 9 - 13
LIST STU SSD PGM STU.LN STU.FN STU.ID SSD.HMT PGM.CD PGM.PSD PGM.PED IF SSD.HMT < 13 AND SSD.HMT > 08 AND PGM.CD = 131
Homeless – missing entry / Program entry must have participating start date
LIST STU SSD PGM STU.LN STU.FN STU.ID SSD.HMT PGM.CD PGM.PSD PGM.PED IF SSD.HMT < 13 AND SSD.HMT > 08
Foster Family
SSD.HMT = 21 0r 22
PGM.CD = 135 / A program entry with code 135 must exist for all Foster children
LIST STU SSD PGM STU.LN STU.FN STU.ID SSD.HMT PGM.CD PGM.PSD PGM.PED IF ( SSD.HMT = 21 OR SSD.HMT = 22 )
Gate / Gate entries are updated in GTE.ESD and GTE.EED
*need to exit any residual data from PGM.CD = 127
Lunch Program / Free and Reduced Table – FRE.CD, FRE.ESD, FRE.EED
* need to exit any residual in PGM. CD = 175
Special Ed / Special Ed Table – CSE.DR CSE.SS CSE.ED CSE.XD SCE.DI
* need to exit any residual data in PGM.CD = 144
LIST STU CSE STU.NM STU.GR STU.BD CSE.DI BY CSE.DI IF CSE.DI # “”
Validate U2 (speech/Lang) and U5 (RSP/SDS) with Special Ed Disability
LIST STU CSE STU.NM STU.GR STU.BD STU.U2 STU.U5 CSE.DI IF STU.U2 # “”
LIST STU CSE STU.NM STU.GR STU.BD STU.U2 STU.U5 CSE.DI IF STU.U5 # “”
In almost all cases U2 AND U5 should not have data if CSE.DI is blank.
LIST STU CSE STU.NM STU.GR STU.BD STU.U2 STU.U5 CSE.DI BY CSE.DI
Lists students who have a disability but no entry in U2 or U5. This may be correct if the student is no longer receiving services.
LIST STU CSE STU.NM STU.GR STU.BD STU.U2 STU.U5 CSE.DI IF CSE.DI # “” AND STU.U2 = “” AND STU.U5 = “”
Migrant – Title 1 Part C / Program code 135 and either eligible or participating. If SSD.MID > 0 then PGM.CD must equal 135 and Vice Versa. SSD.MID – Migrant Code
Validate SSD.MID has corresponding Program Record
LIST STU SSD PGM STU.SC STU.LN STU.FN STU.ID STU.CID STU.GR SSD.MID PGM.CD PGM.ESD PGM.EED PGM.PSD PGM.PED IF SSD.MID # " " - resort by SSD.MID
Program Record missing valid SSD.MID
LIST STU SSD PGM STU.SC STU.LN STU.FN STU.ID STU.CID STU.GR SSD.MID PGM.CD PGM.ESD PGM.EED PGM.PSD PGM.PED IF PGM.CD = 135 AND SSD.MID = " "
Eligible Immigrants – Title III
SSD.SUS = N / Enrolled in US less than 3 years, birth country not US, Dist of Columbia or Puerto Rico, primary enrollment, age 3 – 21
LIST STU SSD LAC STU.ID STU.LN STU.FN STU.GR STU.BCU LAC.USS SSD.SUS IF STU.BCU # US
Program Start Date Validation / LIST STU PGM PGM.SC PGM.SN PGM.CD PGM.PSD PGM.PED PGM.SCL STU.SC STU.LN STU.FN STU.ED STU.LD IF PGM.PSD > 01/01/1990
504 Accomodations / All students with a 504 plan must have the Program Code set to 101 and the Eligibility Start Date set to the day the 504 starts. You do not need to enter an exit date until the 504 expires. Also, you do not need to exit students from this program when they leave your school, or change the start date if a student comes to you with an open 504 plan. Student eligibility can ‘roll-over’ from one school to the next if appropriate.
Use this query to identify all students whose U5 is set to 4 but may not have a corresponding program entry.
LIST STU PGM STU.LN STU.FN STU.GR STU.U5 PGM.CD PGM.EED PGM.ESD PGM.PSD PGM.PED IF U5 = 4
Use this query to identify all students who have a program entry but no corresponding entry in U5.
LIST STU PGM STU.LN STU.FN STU.GR STU.U5 PGM.CD PGM.EED PGM.ESD PGM.PSD PGM.PED IF PGM.CD = 101 AND U5 # 4

Staff Information

All Staff Data / LIST STF SID ID FN MN LN FNP MNP LNP BD SX ETH RC1 RC2 EL PS HD LD TYD TYS TG PSC IF PSC # 999
Staff Assignment / LIST STF STJ STF.SID STF.ID STF.FN STF.LN STF.BD STF.SX STJ.JC STJ.FTE STJ.NC1 STJ.NC2 STF.PSC
Teacher, Staff Assignment extract record / LIST TCH STF STJ TCH.TN TCH.TE TCH.ID TCH.MTS TCH.HQT TCH.CB TCH.NCC TCH.ESR TCH.LI TCH.INS TCH.FS STF.ID STF.LN STF.FN STF.MN STF.SX STF.BD STF.HD STF.LD STF.EL STF.ETH STF.RC1 STF.PS STF.TYD STF.TYS STF.LNP STF.FNP STF.MNP STF.SID STJ.JC STJ.FTE STJ.NC1
District database – Teacher, staff, assignment extract record / LIST TCH STF STJ TCH.SC TCH.TN TCH.TE TCH.ID TCH.MTS TCH.HQT TCH.CB TCH.NCC TCH.ESR TCH.LI TCH.INS TCH.FS STF.ID STF.LN STF.FN STF.MN STF.SX STF.BD STF.HD STF.LD STF.EL STF.ETH STF.RC1 STF.PS STF.TYD STF.TYS STF.LNP STF.FNP STF.MNP STF.SID STF.PSC STJ.JC STJ.FTE STJ.NC1
Teacher/Staff quick sort / LIST TCH STF TCH.TN TCH.TE TCH.MX TCH.TS TCH.ID TCH.TG STF.ID STF.PSC STF.TG
LIST TCH STF TCH.SC TCH.TN TCH.TE TCH.ID TCH.MTS TCH.HQT TCH.CB TCH.NCC TCH.ESR TCH.LI TCH.INS TCH.FS TCH.TG STF.ID STF.LN STF.FN STF.MN STF.SX STF.BD STF.HD STF.LD STF.EL STF.ETH STF.RC1 STF.PS STF.TYD STF.TYS STF.LNP STF.FNP STF.MNP STF.SID STF.PSC

Course and Section Data

Course section data / LIST CRS MST TCH STF CRS.C3 CRS.CN CRS.CO CRS.VCL CRS.NCC CRS.TY CRS.U1 MST.SE MST.SM STF.SID TCH.ID TCH.TE CRS.CL MST.ESR MST.LI MST.INS MST.MTS MST.FS MST.TEP MST.HQT IF MST.SE > 0
ISP Student Courses / LIST STU SEC MST CRS TCH STU.SN STU.LN STU.FN STU.GR STU.CID STU.HS STU.BD STU.SX STU.ED STU.LD STU.TG CRS.CN CRS.CO CRS.C3 MST.SE MST.SM MST.TN TCH.TE
Course Section Data - District / LIST CRS MST TCH STF CRS.C3 CRS.CN CRS.CO CRS.VCL CRS.NCC CRS.TY CRS.U1 MST.SC MST.SE MST.SM STF.SID TCH.ID TCH.TE CRS.CL MST.ESR MST.LI MST.INS MST.MTS MST.FS MST.TEP MST.HQT IF MST.SE > 0

Year End Queries

Next School / Verify the student name, grade and next school field are set correctly
LIST STU LN FN GR NS
Change the “next school” for students in grades 0 – 5. Change 43 to your school code
CHANGE STU NS TO 43 IF NS = 0 OR ( (NS = 72 OR NS = 73 ) AND GR < 6 )
Middle Schools
CHANGE STU NS TO 72 IF NS = 0 AND GR = 7
Next Grade / Use this query to show students that have an incorrect next grade.
LIST STU FN LN NS GR NG IF NG = GR OR NG < GR
6TH Grade Matriculators / Use the following query to list 6th grade students their next school and their Graduation Status. Graduation status field should be empty.
LIST STU LN FN ID CID GR NS HSG IF GR = 6
Use the following queries to update graduation status and graduation date. You must run both in the order they are listed.
CHANGE STU HSG TO 480 IF GR = 6 AND ( NS = 72 OR NS = 73 )
CHANGE STU DG TO 06/08/2011 IF GR = 6 AND HSG = 480
8TH Grade Matriculators / 8TH grade students going to BCHMS, VHS, or Will C. Wood next year. If the student is not going to one of our high schools, the student needs to be exited at the end of the year.
Use the following query to list 8th grade students, their next school and their Graduation Status. Graduation status field should be empty.
LIST STU LN FN ID CID GR NS HSG IF GR = 8
Use the following queries to update graduation status and graduation date. You must run both in the order they are listed.
CHANGE STU HSG TO 480 IF GR = 8 AND NS = 82
CHANGE STU DG TO 06/08/2011 IF GR = 8 AND HSG = 480 (enter your graduation date)

Red Flag Queries

Contacts Red Flag / KEEP STU FLG IF FLG.TC = CON
Student name plus contact name, phone and work phone
LIST STU CON FLG STU.FN STU.LN STU.GR CON.NM CON.TL CON.WP IF FLG.TC = CON
Same as above but includes Contact Red Flag comments
LIST STU CON FLG STU.FN STU.LN STU.GR FLG.CO CON.NM CON.TL CON.WP IF FLG.TC = CON
Fee Red Flag / KEEP STU FLG IF FLG.TC = FEE
CCE and ERD / LIST STU ODE FLG STU.LN STU.FN STU.GR STU.ED STU.LD STU.TG ODE.DNM ODE.SNM ODE.ED ODE.LD ODE.CO FLG.TC FLG.CO IF FLG.TC = ATT AND ODE.DNM # " "

Other Queries

CST scores and Content Cluster / Overall CST scores and content Cluster (strand) scores by teacher for an elementary site.
CST ELA Scores:
LIST STU SUP TCH TST STU.NM STU.GR STU.LF STU.PED STU.DSB TST.SS TST.PL TCH.TE TCH.TN BY TCH.TN STU.GR TST.SS IF TST.ID = “CST” AND TST.PT = 1 AND TST.TA = “SPRG04”
CST Math Scores
LIST STU SUP TCH TST STU.NM STU.GR STU.LF STU.PED STU.DSB TST.SS TST.PL TCH.TE TCH.TN BY TCH.TN STU.GR TST.SS IF TST.ID = “CST” AND TST.PT = 21 AND TST.TA = “SPRG04”
To find Content Cluster scores for ELA:
LIST STU SUP TCH CST STU.NM STU.GR STU.ED STU.PED SUP.DSB CST.RS TCH.TE TCH.TN BY TCH.TN STU.GR CST.RS IF CST.ID = “ENGLANG” AND CST.PT = 1 AND CST.TA = “SPRG04”
To find Content Cluster scores for Math:
LIST STU SUP TCH CST STU.NM STU.GR STU.ED STU.PED SUP.DSB CST.RS TCH.TE TCH.TN BY TCH.TN STU.GR CST.RS IF CST.ID = “MATHGRD2” AND CST.PT = 1 AND CST.TA = “SPRG04”
Find student’s whose Program = N but are attending classes / LIST STU SEC MST STU.LN STU.FN STU.ID STU.SP STU.TG MST.SE MST.PD MST.SM IF STU.SP = N AND MST.SE > 0 AND MST.SM # S