This is an old revision of the document!
EOY Student Records Collection Queries
Document created by Jennifer Rhea
The following queries will help review SASI data due on the End of Year report for accuracy. Be sure to always select “Show Inactive Records” when performing these queries as inactive students are to be included in this report. Those that have an (M) next to them can be check by doing a matrix as well.
NOTES:
- In queries that contain “ “ there is a space between each quotation mark.
- For queries that begin with XXXX ASTU, where XXXX indicates different file names, link ASTU to XXXX by dragging Stulink from the XXXX column to the field above the second column.
- All schools must run all queries to make sure nothing is in the fields that don’t apply.
Check for Blank State Testing Identifier (M)
DISPLAY ASTU PermNum LastName FirstName StateID
Check Unique Local Student Identifier (M)
DISPLAY ASTU PermNum LastName FirstName
Check Responsible Division/Tuition Paid Code
DISPLAY ASTU PermNum LastName FirstName UserCd2 UserCd3
These two elements are queried together for comparison reasons. The UserCd2 (Residency Cd) field should be blank or 094 (Washington Co.) unless student resides in Johnson County, TN in which the code will be 888.
The UserCd3 (Tuition Tag) field should be blank unless student resides in Johnson County, TN in which the code will be 10, or the student is in the Co-op Special Ed program at AHS or GES, or is part of a Residential Special Ed Center. See SASI Coding for Special Ed document for details.
Check Responsible School (M)
DISPLAY ASTU PermNum LastName FirstName UserCd2 UserNum5
The Resp. Sch (usernum5) should be blank unless student resides in Johnson County, TN (usercd2) in which the code will be 8888.
Check Serving Division/School (M)
DISPLAY ASTU PermNum LastName FirstName UserCd3 AltID1 AltID2
The Serving Div (AltID1) and the Serving Sch (AltID2) field’s should be blank if the Tuition Tag (UserCD3) is blank (no tuition). If the Tuition Tag is greater than blank the Serving Div and Serving Sch fields should contain the appropriate data.
Check Active Status (M)
DISPLAY AENR ASTU 2,Status LastName FirstName 1,EnterCode 1,LeaveCode EffDate UserCd1 IF TransYear = "2006"
If student does not have an Enter or Leave Code their status must be N. Do a matrix to check that every student without an enter/leave code is tagged with an N (no show).
Check Enter Code/Leave Code/Date (M)
DISPLAY AENR ASTU PermNum LastName FirstName 1,EnterCode 1,LeaveCode EffDate IF TransYear = “06”
Link ASTU by dragging Stulink from the AENR column to the field above the second column.
Most all E1’s should fall on or before the first day of the school year. If not, please check for accuracy. Also check leave codes for accuracy. Once school is in session no enter or leave code should fall on a non-school day.
Check Ethnic Code (M)
DISPLAY ASTU PermNum LastName FirstName EthnicCode#
Every student should have an Ethnic Code.
Check Gender (M)
DISPLAY ASTU PermNum LastName FirstName Gender
Make sure all male students are marked M (Male) and all female students are marked F (Female).
Check Birthdate (M)
DISPLAY ASTU PermNum LastName FirstName Grade Birthdate
Check the Birth Date field for accuracy, in particularly the year of birth.
Check Grade Level (M)
DISPLAY ASTU PermNum LastName FirstName Grade
Check that there are no grades outside of your school’s grade range.
Check Disability Code (M)
DISPLAY ASTU PermNum LastName FirstName InstrSet IF InstrSet <> “ “
All special education students should have a primary disability code (InstrSet).
Check Free Meals
DISPLAY ASTU PermNum LastName FirstName FreeMeals# IF FreeMeals <> " "
DISPLAY ASTU PermNum LastName FirstName Homeless IF Homeless = “X”
DISPLAY ASTU PermNum LastName FirstName Migrant IF Migrant = “X”
These queries will give you a list of all students who have either Free or Reduced lunch, are tagged Homeless, or are tagged Migrant, which indicates a disadvantaged student. Check that these lists are accurate. Though Homeless and Migrant aren’t a part of the EOY report they do affect the Disadvantaged flag.
No student should be marked Migrant unless they have a certificate of eligibility provided by the state of Virginia. If student is migrant and does not receive free/reduced lunch send the students id and name to jrhea@wcs.k12.va.us to be marked as eligible for free/reduced lunch on the final report.
If student is homeless and does not receive free/reduced lunch send the students id and name to jrhea@wcs.k12.va.us to be marked as eligible for free/reduced lunch on the final report.
Check Limited English Proficiency/Home Language/Country of Birth (M)
DISPLAY AHLN ASTU PermNum LastName FirstName EngProf Language3 CntryCode IF EngProf <> " "
Link ASTU by dragging Stulink from the AHLN column to the field above the second column.
If the EngProf (ESL) field in the Student Atom is greater than blank the Language3 (Language Spoken to Student at Home) field in the Home Language Atom most likely should be something other than English. Student must have country of birth showing if born outside of the USA.
Check Gifted
DISPLAY AGAT ASTU LastName FirstName GateDate General Specified# Visual PsyMotor if GateDate <> " "
The GateDate field should reflect the student’s date of referral. Any other field should contain information only if student has been placed in the Gifted program. The PsyMotor field indicates students who are talented through Technical/Practical Arts.
Check Title 1 (All Schools)
DISPLAY ASTU PermNum LastName FirstName Grade UserCd7 if UserCd7 <> " "
Middle/High make sure nothing is in this field for grades greater than 02.
Check GED Program Code (All Schools)
DISPLAY ASTU PermNum LastName FirstName Grade UserCd4# UserCd4 IF UserCd4 IN [5,8,9]
The UserCd4 (Diploma Tag) field should contain one of the two (GED(5), GED/ISAEP(8), not GAD(9)) if they are in a GED program regardless of grade. Elem/Middle make sure nothing is in this field.
Check Graduate/Other Completer
DISPLAY ASTU PermNum LastName FirstName Grade UserCd4# UserCd4 InstrSet IF Grade = “12”
The UserCd4 (Diploma Tag) field should be greater than blank if grade = 12. If Diploma Tag = 3, 7, or 10 student must have Disability Code (InstrSet). If Diploma Tag = 10, or 11 student must be a senior.
Check Graduation Plan(M)
DISPLAY ASTU PermNum LastName FirstName Grade PostSecond IF Grade = “12”
The GradPln (PostSecond) field should be greater than blank if grade = 12.
Check Advanced Placement (Classes)
DISPLAY ACLS ASTU AMST ACRS 2,Status PermNum LastName FirstName UserNum2 Title IF NonAcadem = "A"
Link ASTU to ACLS by dragging StuLink from the ACLS file to field above the second column, then link AMST by dragging ClassLink from the ACLS file to the field above the third column, then link ACRS by dragging Course from the AMST file to the field above the fourth column.
If a student is currently enrolled in an AP course they must have 1, 2, or 3 marked in the UserNum2 field in the Student atom. This query’s accuracy depends on the AP courses being marked with an A (Advanced Placement) in the N/H field in the Course atom.
DISPLAY ASTU PermNum LastName FirstName UserNum2 IF UserNum2 <> “ “
This query is for middle/elem schools to make sure nothing is in the Advanced Placement (UserNum2) field in the Student atom.
Check Advanced Placement (Class History)
DISPLAY ACLH ASTU ACRS 2,Status PermNum LastName FirstName UserNum2 Title IF NonAcadem = "A"
Link ASTU by dragging StuLink from the ACLH file to the field above the second column, and then link ACRS by dragging Course from the ACLH file to the field above the third column.
If a student has taken an AP class during the current year they must have 1, 2, or 3 marked in the UserNum2 field in the Student atom. This query’s accuracy depends on the AP courses being marked with an A (Advanced Placement) in the N/H field in the Course atom.
Check Dual Enrollment (Classes)
DISPLAY ACLS ASTU AMST ACRS 2,Status PermNum LastName FirstName Title CollArea1 IF CollArea1 <> " "
Link ASTU to ACLS by dragging StuLink from the ACLS file to field above the second column, then link AMST by dragging ClassLink from the ACLS file to the field above the third column, then link ACRS by dragging Course from the AMST file to the field above the fourth column.
If a student is currently enrolled in a Dual Enrollment course they must be on this list. This query’s accuracy depends on each Dual Enrollment course being marked greater than blank in the Dual Enrollment (CollArea1) field in the Course atom.
DISPLAY ACRS Title CollArea1 IF CollArea1 <> “ “
This query is for middle/elem to check the Course file to make sure no courses are marked as dual enrollment courses.
Check Dual Enrollment (Class History)
DISPLAY ACLH ASTU ACRS 2,Status PermNum LastName FirstName Title CollArea1 IF CollArea1 <> " "
Link ASTU by dragging StuLink from the ACLH file to the field above the second column, and then link ACRS by dragging Course from the ACLH file to the field above the third column.
If a student has taken a Dual Enrollment course during the current year they must be on this list. This query’s accuracy depends on each Dual Enrollment course being marked greater than blank in the Dual Enrollment (CollArea1) field in the Course atom.
Check CTE Finisher (M)
DISPLAY AVOC ASTU PermNum LastName FirstName CarTech Technical CmpStat CipNo
This is a list of students who have one or the other markings in the Vocation Ed atom. Use this list to verify that they are marked correctly.
Check W8 Reason (M)
DISPLAY ASTU PermNum LastName FirstName LeaveCode UserCd1 IF LeaveCode = "W8"
All students with a droupout (W8) code must have a W8 Reason in the UserCd1 field.
DISPLAY ASTU PermNum LastName FirstName UserCd1 LeaveCode IF UserCd1 <> “ “
No student should have a W8 Reason code if they are not dropouts.
Check Summer Dropouts (M)
DISPLAY ASTU PermNum LastName FirstName Retain IF Retain <> " "
DISPLAY AENR ASTU 2,Status PermNum LastName FirstName EffDate 1,EnterCode 1,LeaveCode TransYear IF TransYear IN [05, 06] AND LeaveCode = " " AND 2,Status = "N"
DISPLAY AENR ASTU 2,Status PermNum LastName FirstName EffDate 1,EnterCode 1,LeaveCode TransYear IF TransYear = "06" AND EnterDate > "100206" AND 2,EnterCode = "E1" AND 2,Status <> "N"
Link ASTU by dragging StuLink from the AENR file to the field above the second column.
Any student who was in membership the last day of school the previous year but did not return to any school during the current year by Oct 1 must be marked as a Summer Dropout. The first query gives you a list of student who are already marked as summer dropouts that may or may not be correct. The second query gives you a list of No Show students for the current year. Check to see if any of those students actually dropped out of school over the summer. If so, they need to be marked as summer dropouts, otherwise they should not. The third gives you a list of students who enrolled in your school after Oct. 1 with a code of E1 (First School This Year). Check those students history to see if they were in membership on the last school day of the previous year, but did not enroll anywhere before Oct. 1. If they did not they should be marked a summer dropout, otherwise they should not.
Check Aggregate Days Present/Absent (Elem/Middle) (Hidden Abs query)
DISPLAY AATD ASTU PermNum LastName FirstName 2,Status AbsDate AllDayAbs IF AbsDate > LeaveDate AND LeaveDate > " "
Link ASTU by dragging StuLink from the AATD file to the field above the second column.
Check Aggregate Days Present/Absent (High) (Hidden Abs query)
DISPLAY AATP ASTU PermNum LastName FirstName 2,Status PeriodDate AllDayAbs IF PeriodDate > LeaveDate AND LeaveDate > " "
Link ASTU by dragging StuLink from the AATP file to the field above the second column.
This query gives you a list of students who have absent marks after their withdrawal date. This will affect both the Aggregate Days Present and Absent. If there are such errors alert your attendance clerk.
Check Retention Flag (M)
DISPLAY ASTU PERMNUM LastName FirstName NextGrade IF NextGrade <> " "
If a student is to repeat their current grade next year or will not be promoted until after summer school completion they should have their current grade in the NextGrade field indicating that he/she is Retained.
Check Truancy Conferences
DISPLAY ACNF ASTU ACNR LastName FirstName ReportDate Descript IF DescCode = ATT
Link ASTU by dragging StuLink from the ACNF column to the field above the second column, then link ACNR by dragging DescCode from the ACNF column to the field above the third column.
This query gives you a list of students who will be reported as Truant. All students who are sent to Area Wide Attendance and the absences are deemed accurate should be reported as truant. There is no query to account for such meetings. You will need to get a list from your attendance clerk/Principal and enter those in the Conference atom.
Check Non Public FTE (M)
DISPLAY ASTU PermNum LastName FirstName PctApport IF PctApport <> " "
Students containing a percentage amount in this field must be part time students taking two courses or less at your school. Check First Year In Ninth Grade (High)
DISPLAY ASTU PermNum LastName FirstName NextTrack IF Grade = "09"
All 9th graders must have either 1,2,3, or 4 entered into the 9thTrk (NextTrack) field.
Check Kindergarten Readiness Assessment
DISPLAY ASTU PermNum LastName FirstName BirthDate if BirthDate > "100101" and Grade = "KG"
If a student is on this list email those names to jrhea@wcs.k12.va.us indicating whether or not those students were administered the readiness assessment.
Check Early College School Program Code
DISPLAY ASTU PermNum LastName FirstName AttPrmCode IF AttPrmCode <> " "
This query gives you a list of students who have either signed the Early College Scholar Agreement (1) or have earned the Early College Scholar Certificate (2)
Check Distance Learning
DISPLAY ACRS Title Category IF Category <> " "
This list gives you the courses in the Course atom that are flagged as distance learning.
Check PK Experience Code/PK Weekly Time Code
DISPLAY ASTU PermNum LastName FirstName InstrSet UserCd6 UserCd6# UserNum1 if Grade = "PK"
All PK students must have a PK Experience Code and PK Weekly Time Code.
Check Student Name
DISPLAY ASTU PermNum LastName FirstName MiddleName
Every student should have a first, middle, and last name if applicable.
Page Tools
