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 Student (STU) 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 All Students including School and Grade Level

Provides a list of all students and includes school and grade level


LIST STU SC ID FN LN GR


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 GN IF GR = ## AND GN = 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 GN IF GR = ## OR GR = ##

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 > ##/##/20##

STU:  List of Students if Pre-enrolled by a Specific Grade Level

Provides a list of students' current Grade Level and Next Grade Level if the student is pre-enrolled and has a specific Next Grade level


LIST STU ID NM GR NG TG IF TG = * AND NG = ##

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 GN 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 GN 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 GN 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 GN 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.GN 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.GN TCH.TE BY TCH.TE STU.GN

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.GN STU.TL TCH.TE BY TCH.TE STU.GN

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 Students missing Birthdate

List of Student ID, Student Name, Grade Level if Birthdate field is blank


LIST STU ID NM BD IF BD = NULL

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 


LIST STU ID NM STU.GR STU.ED IF STU.ED > (Enter date ##/##/20##)

STU:  List of Students Enrolled After a Specific Date with Teacher information

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 with a No Show Tag

List of students who were exited using a No Show designation


LIST STU ID NM LD TG IF TG = N

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

Texas Student Data Queries

STU:  List of Early Childhood students - District Report

Provides a list of EE - Early Childhood students 


LIST STU ID NM GR IF GR >= -2 AND GR <= 12

STU:  List of All Students including Campus and Grade Level

Provides a list of all students and includes campus and grade level.


LIST STU SC ID FN LN GR

STU:  List of Students Enrolled After a Specific Date - New Student Enrollment Report

List of students enrolled after a specific date 


LIST STU ID NM STU.GR STU.ED IF STU.ED > (Enter date ##/##/20##)

ARC:  List of Student with At-Risk Indicators

List of students with active At-Risk Indicator(s)


LIST STU ARI ARC STU.ID STU.NM ARC.CD ARC.DE ARI.SD IF ARC.CD > 0 AND ARI.ED = NULL 

ACT:  List of Students and Information on Activities and Awards - includes inactive records

Provides a list with Student Status, Student ID, Student Name, Grade Level, Activity/Award, Activity/Award Code and Description


LIST STU ACT STU.TG STU.ID STU.NM STU.GR ACT.TY ACT.TY? ACT.CD ACT.CD? ACT.NM ACT.DT ACT.IBF ACT.IBV ACT.IBS IF ACT.TY > " "

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

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 

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##

T LOG IF CD : "CON"

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

 the students missing a photo

     LIST STU ID SC NM 

List of Authorization (AUT) Queries

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

List of Log (LOG) and Email (EML) Queries

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

List Staff (STF) Queries

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 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 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 GN BY GR GN 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 GN BY ETH GN

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

KEEP/SKIP Queries

SKIP STU SSS:  Skips Students if Students have a Specific Course Request Number and Next Grade Level

Skips the students based on the Course Request Number and Next Grade level so that a Mass Add Course Request process can be applied to all other students


SKIP STU SSS IF SSS.CN = #### AND NG = ##

KEEP STU MST:  Keeps Students if Students have a Specific Course and Grade Level

Keeps the students based on the Course Number and Grade level so that students can be assigned the next course in the grade level


KEEP STU SEC MST IF MST.CN = #### AND GR = ##

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

STU:  Change Students to a Specific Staff ID/Counselor by Grade Level

Change the Teacher/Counselor field to a specific staff person by specific Grade Level


CHANGE STU CNS TO #### IF GR = ##

(enter Staff ID (STF.ID)

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