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


COD:  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)


LOC:  List of Information on District Schools

Provides information on district schools including:  School Number, Name, Address, Telephone, Low Grade and High Grade


LIST LOC CD NM AD CY ST ZC AC TL LO HI


STU:  List of Students Not Assigned to a Teacher/Counselor

Provides a list of students who have not been assigned to a Teacher/Counselor


LIST STU ID NM IF CU = 0


STU:  List of Students if in a Specific Grade Level

Provides a list of students if in a specific Grade 


LIST STU ID FN LN GR IF GR = ##  


STU:  List of Students if a Specific Gender and in a Specific Grade Level ("And" Condition/Comparison)

Provides a list of students if student is a specific Gender or in a specific Grade


LIST STU NM GR SX IF GR = ## AND SX = M

(Change Gender as needed)


STU:  List of Students if in Two Specific Grade Levels ("OR" Condition/Comparison)

Provides a list of students if the student is in two specific Grade Levels


LIST STU FN LN GR SX IF GR = ## OR GR = ##


STU:  List of Students if Grade Level is Greater Than a Specific Grade Level (">" Condition/Comparison)

Provides a list of students if the student's Grade Level is greater than a specific Grade Level


LIST STU NM GR SX IF GR > ##


STU:  List of Students if Grade Level is Less Than a Specific Grade Level ("<" Condition/Comparison)

Provides a list of students if the student's Grade Level is less than a specific Grade Level


LIST STU FN LN GR SX IF GR < ##


STU:  List of Students if Grade Level Does Not Equal a Specific Grade Level ("#" Condition/Comparison)

Provides a list of students if the student's Grade Level does not equal a specific Grade Level


LIST STU FN LN GR SX IF GR # 10

(Update Grade Level as needed)


STU:  List of Students if Last Name Contains Specific Letters or Characters (":" Condition/Comparison)

Provides a list of students if the student's Last Name contains specific letters or characters


LIST STU FN LN GR SX IF LN : SON

(Replace with Letter or Characters to search for)   


STU:  List of Students if the Parent Last Name is Different than the Student's Last Name and Does Not Contain the Same Letters as the Student's Last Name (";" Condition/Comparison)

List of students and parents with different last names


LIST STU NM GR PG IF PG ; LN


STU:  List of Students with Corresponding Language

Listing of students with ID, Corresponding Language and Descriptions stored in the COD table for one field (HL) but used by another field (CL)


LIST STU ID NM CL CL?HL


STU:  List of Students with Race Code ("BY" Condition/Comparison)

Provides a list of students, Grade, Race Code sorted by Race Code


LIST STU ID FN LN GR RC1 BY GR RC1


STU:  List of Student Missing an Ethnicity or Race Code

Provides a list of students if the Ethnicity or Race Code is missing


LIST STU ID NM GR ETH RC1 IF ETH = " " OR RC1 = " "


STU: List of Student Ethnicity and Race Codes for Students Who are Not Hispanic 

Provides a list of Ethnicity and Race Codes for students who are not Hispanic to search for missing Race Codes sorted by Race Code


LIST STU ID NM ETH RC1 GR SX BY RC1 IF ETH = "N"


STU:  List of Students with First or Last Name Containing "Double Quotes"

Provides a list of any students who have double quotes entered in their name.


LIST STU ID NM IF LN : "'+CHAR(34)+'" OR FN : "'+CHAR(34)+'"


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: List of Students if Address is on a Specific Street

Provides a list of students living on a specific street


LIST STU NM PG AD IF AD : "Main"

(Replace with Street Name)


STU:  List of Students if the Parent Name Contains a Specific Name or Characters (Search Criteria)

Provides a list of students who have a parent with a specific name or characters


LIST STU NM PG IF PG : “Jones”

(Replace with parent name or characters)


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:  Class List for Elementary Schools by Teacher and Gender

Class list for Elementary Schools sorted by Teacher Name and Gender


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


STU:  Student Directory by Teacher Name

Provides a listings of a student's Address, Telephone, Parent/Guardian and Teacher Name sorted by Teacher Name


LIST STU TCH STU.NM STU.AD STU.CY STU.ZC STU.TL STU.PG 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 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 Students if the Next Grade Level and the Current Grade Level are the Same.

Provides a list of students if their Next Grade Level and Current Grade Level are the same.


LIST STU NM ID GR NG IF NG = GR


STU:  List of Students with Next School, Next Grade and Next Teacher

Provides a list of students with Next School, Next Grade and Next Teacher information


LIST STU ID NM NS NG NT


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


STU: List of Students with the Graduation Year

Provides a list with School, Student ID, Student Name, Grade and Graduation Year.


LIST STU SC ID NM GR (( SUBSTRING(DB_NAME(),4,2) + 2001 + 12 - GR ))


STU:  List of Students and Information for Easy MAA (EasyMAA.org) Medi-Cal Administrative Activities


See FAQ:  Creating a file for Easy MAA (EasyMAA.org) Medi-Cal Administrative Activities


FLG:  List of Students with Table Code of Flags and Flag Comments

Provides a list of students who have Red Flags with Grade, Flag Table Code and Comment sorted by Table Code


LIST STU FLG STU.NM STU.GR FLG.TC FLG.CO BY FLG.TC IF FLG.SN > 0


LOG:  List of Entries and Changes to a Specific Table with User Information for Aeries Admins

Provides a list of entries and changes to a specific table with User information for Aeries Admins to investigate modifications of the data


LIST LOG IF CD : "CON"

(Replace CON with table:  STU, GRD, GRH, etc.)


LOG:  List of Entries and Changes to a Specific Table with User Information for Aeries Admins and Date and Time Stamp

Provides a list of entries and changes to a specific table with User information for Aeries Admins to investigate modifications of the data and includes a Date and Time Stamp


LIST LOG TID DT SC SN ID USR RCD OD ND (( DTS )) IF CD : "CON"


EML:  List of Email Data That Was Sent to an Email Address for Aeries Admins

Provides a list of entries with Email information for Aeries Admins to investigate based on a "Sent To:" Email Address


LIST EML EID TN UN SCL DT FAD TAD SUB (( DTS )) IF TAD : "parent.brown@example.com"

(Replace "parent.brown@example.com" with Email Address "Sent To:")


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.


1.  Skip the student who have a current year photo  

     SKIP STU PIC IF PIC.YR = 2018

2.  List the students missing a photo

     LIST STU ID SC NM 


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 Teacher Information

Provides a list of teacher information including:  Teacher Number, Staff ID, Name, Title, Email and Room Number


LIST TCH TN ID TE TT EM RM


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 = "*"


STU:  Total of Students by Ethnicity Code and Gender

Provides a total count of students by Ethnicity Code and by Gender


TOTAL STU ETH SX BY ETH SX


STU:  Total of Students Assigned to Each Teacher/Counselor

Provides student counts for each Teacher/Counslelor


TOTAL STU CU BY CU


STU:  Total of Students by Race and Grade

Provides student counts sorted by Grade Level for each Race


TOTAL STU RC1 GR BY RC1 GR


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