This is a collection of queries related to Enrollment Data Management that have been obtained from various sources. These queries are being provided here for the convenience of our customers. The results of each query should be verified for accuracy and carefully considered before using.


List Queries

Fall 1 Queries for Reconciling CALPADS Data


See:  Fall 1 Queries and Reports


Fall 2 Queries for Reconciling CALPADS Data


See:  Fall 2 Queries


STU:  List of Home Language Codes and Descriptions

Lists all Home Language descriptions in the Code Table


LIST COD CD DE IF TC = STU AND FC = HL

(This query must be run at the district level)


STU:  Finding Students with/without Contact (CON) Links

Lists of students with/without Contact links so that the Contact links can be applied


1. Identify students with populated Contact 1 Link:

    LIST STU CON STU.ID STU.NM CON.NM STU.FW CON.RL? CON.C1F CON.C1D IF CON.C1F # " "
2. Identify students with populated Contact 2 Link:

    LIST STU CON STU.ID STU.NM CON.NM STU.FW CON.RL? CON.C2F CON.C2D IF CON.C2F # " "
3. Keep students with a populated Contact 1 Link or Contact 2 Link: 

    KEEP STU CON IF CON.C1F # " " OR CON.C2F # " "

4. Skip students with a populated Contact 1 Link or Contact 2 Link: 

    SKIP STU CON IF CON.C1F # " " OR CON.C2F # " "


STU:  Class List for Elementary Schools by Teacher

Class list for Elementary Schools sorted by Teacher Name


LIST STU TCH STU.NM STU.GR STU.SX STU.PG STU.TL STU.FW STU.MW TCH.TE BY TCH.TE


STU:  List of Student Name, Grade, Gender and Teacher by Teacher and Gender

List of Student Name, Grade, Gender and Teacher sorted by Teacher and Gender


LIST STU TCH STU.NM STU.GR STU.SX STU.TL TCH.TE BY TCH.TE STU.SX


STU:  Secondary List of Student Data by a Specific Period

List of Student Name, Address, Telephone Number and Teacher for a specific period sorted by Teacher Name


LIST STU TCH SEC MST STU.ID STU.NM STU.AD STU.CY STU.ZC STU.TL TCH.TE BY TCH.TE IF PD = #


STU:  Secondary Emergency Student List by Room Number

List of students sorted by Room then Student Name for any period during the day including: Parent Information and Telephone Numbers


LIST STU SEC MST STU.ID STU.NM MST.PD MST.RM STU.PG STU.TL STU.FW STU.FX STU.MW STU.MX BY MST.RM STU.NM


STU:  List of Student Birthdays by Teacher

List of student birthdays sorted by Teacher Name and Student Birthdate


LIST STU TCH STU.NM STU.BD TCH.TE BY TCH.TE STU.BD


STU:  List of Birthdays for a Specific Month

List of Student Birthday and Teacher for a specific month sorted by Teacher Name


LIST STU TCH STU.NM STU.BD TCH.TE BY TCH.TE IF BD.MONTH = #

(Run query.  Click the Report button and select Page Break by Teacher)


STU:  List of Students with Birthdate and Age

List of  Student Name, Birthdate and calculated age


LIST STU NM BD BD?


STU:  List of Students with a Different Last Name than the Parent/Guardian

List of students and parents with different last names


LIST STU NM PG IF PG ; LN


STU:  List of Students Enrolled After a Specific Date

List of students enrolled after a specific date with their Teacher/Counselor Name


LIST STU TCH STU.NM TCH.TE STU.GR STU.ED IF STU.ED > 08/20/2018


STU:  List of Students Exited After a Specific Date

List of students exited after a specific date with their Teacher/Counselor Name


LIST STU TCH STU.NM TCH.TE STU.GR STU.LD IF STU.TG # " " AND STU.LD > ##/##/20##

(Check the "Include Inactive Students" option)


STU:  List of Student Last School Information

List of students with Last School in Aeries included


LIST STU SN NM LS LS?


STU:  List of Students with Birthdate, District Enter Date, Student Enrollment Data and Initial US School Enrollment

Provides a list with student Birthdate, Enter Dates and US School Enrollment Date to help resolve Student Information (SINF) anomalies


LIST STU LAC STU.CID STU.ID STU.NM STU.GR STU.BD STU.DD STU.ED LAC.USS


PIC:  List of Students Missing a Current Year Photo

Provides a list of students who are missing a photo or do not have an updated current year photo. In this example, it will skip students who have a 2018-19 picture. Everyone else doesn't have one.


SKIP STU PIC IF PIC.YR = 2018


AUT:  Authorizations on a Specific Date

List of students with Authorizations in place on a Specific Date


LIST STU AUT STU.ID STU.NM AUT.CD AUT.CD? AUT.ST AUT.ST? IF AUT.SDT <= ##/##/2018 AND ( AUT.EED = NULL OR AUT.EED >= ##/##/20## )

(use the date date in both date fields)


AUT:  Authorization Date and Time Stamp with Authorization Code and Information

Provides a list of Authorization data with a Date and Time Stamp


LIST AUT SC SN TY CO DT SQ ST (( CAST(SDT AS DATETIME) )) CD TA EED SRC


RAL:  List of Data Records in Record Access Log (RAL) for a Specific School Year

Provides a list of all data entry in the Record Access Log (RAL) for a specific year


LIST STU RAL STU.ID STU.NM STU.GR RAL.DT RAL.NM RAL.RS RAL.CRC RAL.RA IF RAL.DT > 06/30/20##


STAFF:  List of Teachers with a Multiple Teacher Status Code 

Lists Teachers with a Multiple Teacher Status Code and the additional Teacher Numbers


1.  Elementary from Teacher Table (TCH)

     LIST TCH TE MTS ID2 ID3


2.  Secondary from Master Schedule Sections (MST.SE)

     LIST MST SE TN MTS TN2 TN3


STAFF:  List of Teacher Educational Service, Language of Instruction and Instructional Strategy Codes

Lists Teacher Educational Service, Language of Instruction and Instruction Strategy codes


1.  Elementary from Teacher Table (TCH)

     LIST TCH TE ESR LI INS


2.  Secondary from Master Schedule Sections (MST.SE)

     LIST MST TCH MST.SE TCH.TE MST.ESR MST.LI MST.INS


STAFF:  List of Teachers Missing a Staff ID in the Teacher Table

Provides a list to verify that each Teacher record is linked to a Staff ID


LIST TCH TE ID IF ID = " "


STAFF:  List of Teachers Missing a SEID - District Level

Provides a list to very that Teacher in the Staff Table has a SEID


LIST STF LN FN SID IF TG # I AND SID = " "


Total Queries

STU:  Total of Pre-Enrolled Students for a Specific Grade by Gender

Total of male and female students with a pre-enrolled status for a specific grade


TOTAL STU GR SX BY GR SX IF GR = 6 AND TG = "*"


Label Queries

STU:  Print Mailing Labels for Contacts with a Mail Tag to Receive Mailings

Print labels for Contacts identified with a Mail Tag to receive all mailings


LIST STU CON CON.NM \ 'RE:' STU.NM \ CON.AD \ CON.CY CON.ST CON.ZC IF CON.MT = A


Change Queries

STU:  Change Student Next Grade to One Grade Higher than Current Grade Level

Change all students' Next Grade field to one grade higher than current Grade


CHANGE STU NG TO (( GR + 1 ))


STU:  Change Student Completion Date to a Specific Date for a Specific Grade

 Change the Graduation Date field to a specific date for students in a specific Grade level


CHANGE STU DG TO ##/##/20## IF GR = ##


STU:  Change Student Completion Status for a Specific Grade

Mass adds a 480 -Promoted (Matriculated) Code in the Completion Status field for matriculating students for a specific Grade level


CHANGE STU STU.HSG TO 480 IF STU.HSG = " " AND GR = ##


STU:  Change Graduation Fields for 12th Grade Students

Mass populates the Completion Status (STU.HSG) and Completion Date (STU.DG) fields for 12th graders


1.  Change students in 12th grade to a Completion Status of 100 - Graduated, Standard HS Diploma if the 

     Completion Status field has not been populated

     CHANGE STU STU.HSG TO 100 IF GR = 12 AND STU.HSG = " "

   

2.  Change students in 12th grade to have a specific Completion Date if the Completion Date has not been populated

     CHANGE STU DG TO MM/DD/20YY IF GR = 12 AND DG = NULL


STAFF:  Change Language of Instruction to Blank if Erroneously Populated with code 00 - English or 37 - Sign Language for a Teacher with an Educational Service Code of 4 - ELD and SDAIE Not Primary Language

Mass removes the erroneously codes because a Language of Instruction is Blank if the Education Service Code is 4 - ELD and SDAIE Not Primary Language.


1.  Elementary from the Teacher Table (TCH)

     CHANGE TCH LI TO " " IF LI = 00 OR LI = 37 AND ESR = 4


2.  Secondary from the Master Schedule Sections (MST.SE)

     CHANGE MST LI TO " " IF LI = 00 OR LI = 37 AND ESR = 4