This is a collection of queries related to Medical and Health 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

Medical Alerts/Flags

FLG:  Any student that has a red flag on the MHS, MHS, MED, MNO and IMM tables and not from any other table.

LIST STU FLG MHS STU.ID STU.NM STU.GR STU.BD MHS.SD MHS.ED MHS.CD MHS.TG MHS.CO MHS.PC FLG.CO BY STU.GR STU.LN IF FLG.TC = 'MHS' OR FLG.TC = 'XMHS' OR FLG.TC = 'MNO' OR FLG.TC = 'MED' OR FLG.TC = 'IMM' AND MHS.CD # NULL AND MHS.SD >='07/01/2021'


Medi-Cal

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


Medical History


MHS:  List of Students with a Medical History Code 

Provides a list of student who have a Medical History Code


LIST STU MHS STU.ID STU.NM STU.GR MHS.CD MHS.CD? MHS.CO IF MHS.CD # " " 


MHS:  List of Student with a Medical History Code by Teacher Name for Elementary with Master Schedule School Type

Provides a list of students with attendance period teacher who have a Medical History Code sorted by Teacher 


LIST STU MHS SEC MST TCH TCH.TE MST.PD STU.NM STU.GR MHS.CD? MHS.CO BY TCH.TE IF MHS.CD # " " OR MHS.CO # " "


Medical Visits


MED:  List of Student visits from the Medical Log

Provides a list of student who visited after a specified date and the reason


LIST STU LN FN STU.ID MED MED.CD MED.CD? MED.DT IF MED.DT >= "8/21/2021"   


Medications


MED:  Medications and expiration date list

Provides a list of students with medications and the expiration date listed sorted by grade

LIST STU MLM STU.LN STU.FN STU.GR MLM.CD MLM.EX MLM.CO MLM.SD BY STU.GR IF MLM.CD#NULL AND MLM.SD>=‘08/18/2021’

Sorted by expiration date

LIST STU MLM STU.LN STU.FN STU.GR MLM.CD MLM.EX MLM.CO MLM.SD BY MLM.EX IF MLM.CD#NULL AND MLM.SD >=‘08/18/2021’



Immunizations

In addition to the queries provided below, the Student Immunization Status Report will enable you to run a list of all students missing immunizations and/or who have expired immunization exemptions as of a specified date and report tag these students.  See documentation Immunizations.

You are then able to run this query to list students who are missing immunizations as of the process date selected when the report ran:

LIST STU RTG FN LN GR PG AD CY ST ZC ZX IF RNM = 'PrintStudentImmunizationStatus' AND QT = "V"

or KEEP all students who are missing immunizations:


KEEP STU RTG IF RNM = 'PrintStudentImmunizationStatus' AND QT = "V"


IMM:  List of Kindergarten/Transitional Kindergarten Immunizations

Provides a list of Immunizations for students in Kindergarten/Transitional Kindergarten


LIST STU IMM STU.ID STU.NM STU.GR STU.BD IMM.S1 IMM.E1 IMM.E3 IMM.E2 IMM.PE IMM.DE IMM.AE IMM.IE IMM.VE IMM.PC IMM.DC IMM.AC IMM.IC IMM.VC IF GR = 0 OR GR = -1


IMM:  List of Student without Immunization Records

Provides a list of students that do not have any Immunizations entered


LIST STU IMM STU.ID STU.NM STU.BD STU.ED STU.GR BY STU.GR IF IMM.P1 = NULL AND IMM.PE = NULL



IMM:  List of Students Missing Physical (CHDP)

Provides a list of students missing a Physical (CHDP) record


LIST STU IMM TCH STU.ID STU.NM STU.GR TCH.TE IMM.E3 IMM.H1 IF IMM.H1 = NULL


IMM:  List of 1st Grade Students and Physical (CHDP) Data

Provides a list of all 1st graders and any Physical (CHDP) data


LIST STU IMM TCH STU.ID STU.NM STU.GR TCH.TE IMM.E3 IMM.H1 IMM.HW IMM.HC IF STU.GR = 1


IMM:  List of Students and Tuberculosis (TB) Data

Provides a list of all students and any Tuberculosis (TB) data sorted by Grade


LIST STU IMM STU.ID STU.NM STU.LS STU.ED STU.BCU IMM.BP IMM.E3 IMM.T1 IMM.N1 IMM.N2 IMM.B1 IMM.XD IMM.XN IMM.XA IMM.XC STU.GR BY STU.GR


IMM:  List of Students and Mantoux Report Data (TB)

Provides a list a students with the Mantoux Report Data (TB)


LIST STU IMM STU.ID STU.NM STU.GR IMM.BP STU.LS STU.ED IMM.T1 IMM.N1 IMM.B1 IMM.T2 IMM.N2 IMM.B2 IMM.XD IMM.XN IMM.XA IMM.XC


IMM:  List of 6th Grade Students and Hepatitis (HEPB) and Measles Mumps Rubella (MMR) data

Provides a list of all 6th graders and any HEPB and MMR data


LIST STU IMM STU.ID STU.NM STU.GR IMM.E3 IMM.IE IMM.I1 IMM.I2 IMM.I3 IMM.AE STU.BD IMM.A1 IMM.A2 IF IMM.I3 = NULL OR IMM.A2 = NULL AND STU.GR = 6


IMM:  List of 6th Grade Students needing 7th grade Immunizations (Texas)

Provides a list of all 6th graders and any Meningococcal and Tdap data


LIST STU IMM LOC.NM STU.ID STU.LN STU.FN STU.GR STU.BD IMM.MC1 IMM.MCE IMM.MCD IMM.TB IMM.TE IMM.TED BY LOC.NM STU.GR IF ( IMM.MC1 = NULL OR IMM.TB = NULL OR IMM.MCE = NULL OR IMM.TE = NULL ) AND STU.GR = 6 


IMM:  List of 7th Grade Student Missing Hepatitis 3 and includes Measles Mumps and Rubella (MMR) and Hepatitis (HEP) Information

Provides a list of 7th grade students missing Hepatitis 3 Immunization and includes Measles Mumps and Rubella (MMR) and Hepatitis (HEP) Information


LIST STU IMM STU.SN STU.NM IMM.E3 STU.BD IMM.A1 IMM.A2 IMM.I1 IMM.I2 IMM.I3 IF IMM.I3 = NULL AND GR = 7


IMM:  List of Students who need 7th grade immunizations in Texas

LIST STU IMM STU.ID STU.LN STU.FN STU.GR STU.BD IMM.MC1 IMM.MCE IMM.MCD IMM.TB IMM.TE IMM.TED BY STU.GR IF IMM.MC1 = NULL OR IMM.TB = NULL OR IMM.MCE = NULL OR IMM.TE = NULL AND STU.GR = 7

 


IMM:  List of 7th Grade Students Missing Immunizations

Provides a list of students in 7th grade that are missing required Immunizations


LIST STU IMM STU.ID STU.NM STU.BD IMM.E3 IMM.A1 IMM.A2 IMM.AE IMM.IE IMM.I1 IMM.I2 IMM.I3 IF IMM.A2 = NULL OR IMM.I3 = NULL AND GR = 7


Immunizations Exemptions


IMM:  All Exemptions with their effective date and expiration dates
LIST STU IMM STU.SC STU.LN STU.FN STU.ID STU.GR IMM.PE IMM.PED IMM.PXD IMM.DE IMM.DED IMM.DXD IMM.AE IMM.AED IMM.AXD IMM.YE IMM.YED IMM.YXD IMM.IE IMM.IED IMM.IXD IMM.HAE IMM.HAD IMM.HAXD IMM.VE IMM.VED IMM.VXD IMM.TE IMM.TED IMM.TXD IMM.TBE IMM.TBD IMM.TXD IMM.MCE IMM.MCD IMM.MCXD IMM.PCE IMM.PCD IMM.PCXD IF IMM.PE # ' ' OR IMM.DE # ' ' OR IMM.AE # ' ' OR IMM.YE # ' ' OR IMM.IE # ' ' OR IMM.HAE # ' ' OR IMM.VE # ' ' OR IMM.TE # ' ' OR IMM.TBE # ' ' OR IMM.MCE # ' ' OR IMM.PCE # ' ' OR IMM.CVE # ' '

Add the specific exemption codes to only return specific exemption types. For example:

IMM:  Conscientious Exemptions (ROC)
List of all students with a Conscientious exemption and the effective date

LIST STU IMM STU.SC STU.GR STU.ID STU.LN STU.FN IMM.PE IMM.PED IMM.PXD IMM.DE IMM.DED IMM.DXD IMM.AE IMM.AED IMM.AXD IMM.YE IMM.YED IMM.YXD IMM.IE IMM.IED IMM.IXD IMM.HAE IMM.HAD IMM.HAXD IMM.VE IMM.VED IMM.VXD IMM.TE IMM.TED IMM.TXD IMM.TBE IMM.TBD IMM.TXD IMM.MCE IMM.MCD IMM.MCXD IMM.PCE IMM.PCD IMM.PCXD BY STU.GR IF ( IMM.PE = C OR IMM.DE = C OR IMM.AE = C OR IMM.YE = C OR IMM.IE = C OR IMM.HAE = C OR IMM.VE = C OR IMM.TE = C OR IMM.MCE = C OR IMM.PCE = C )


 

IMM:  Students on immunization catch up schedule with effective dates


LIST STU IMM STU.SC STU.GR STU.ID STU.LN STU.FN IMM.PE IMM.PED IMM.DE IMM.DED IMM.AE IMM.AED IMM.YE IMM.YED IMM.IE IMM.IED IMM.HAE IMM.HAD IMM.VE IMM.VED IMM.TE IMM.TED IMM.MCE IMM.MCD IMM.PCE IMM.PCD BY STU.GR IF ( IMM.PE = F OR IMM.DE = F OR IMM.AE = F OR IMM.YE = F OR IMM.IE = F OR IMM.HAE = F OR IMM.VE = F OR IMM.TE = F OR IMM.MCE = F OR IMM.PCE = F )




Queries for Status of Requirements information including follow-up dates.


Follow-up dates specified for each status checkpoint:

  • Pre-K:  LIST STU IMM ID NM IMM.SIP IMM.RMP IMM.RMP? IMM.RDP IMM.RFP IMM.RFP? IMM.FDP IMM.RTP IMM.RTP? IMM.RPP IF IMM.FDP <= 07/25/2023 

  • TK/K-12:  LIST STU IMM ID NM IMM.SIK IMM.RMK IMM.RMK? IMM.RDK IMM.RFK IMM.RFK? IMM.FDK IMM.RTK IMM.RTK? IMM.RPK IF IMM.FDK  <= 07/25/2023 

  • 7th Grade: LIST STU IMM ID NM IMM.SI7 IMM.RM7 IMM.RM7? IMM.RD7 IMM.RF7 IMM.RF7? IMM.FD7 IMM.RT7 IMM.RT7? IMM.RP7 IF IMM.FD7 <= 07/25/2023


    IMM:  Exemption with any follow up dates 

     

    LIST STU IMM STU.ID STU.NM STU.BD STU.ED STU.GR IMM.FDP IMM.FDK IMM.FD7 BY STU.GR IF IMM.FDP # NULL OR IMM.FDK # NULL OR IMM.FD7 # NULL



Hearing

HRN:  List of Students and Hearing Screening Data

Provides a list of all students and any Hearing Screening data sorted by Grade


LIST STU HRN STU.ID STU.NM STU.HP HRN.TD HRN.PF HRN.SCN HRN.DR HRN.DT STU.GR BY STU.GR


HRN:  List of Students who Failed the Hearing Screening after a Specific Date

Provides a list of students who failed a Hearing Screening that was given after a specific date sorted by Grade


LIST STU HRN STU.ID  STU.NM HRN.TD HRN.SCN HRN.PF STU.GR BY STU.GR IF HRN.PF = F AND HRN.TD > ##/##/20##


HRN:  List of Students with Hearing Doctor Referrals by Teacher

Provides a list of students with data in the Doctor Referral field in Hearing sorted by Grade


LIST STU HRN STU.ID STU.NM HRN.DR HRN.DT STU.GR BY STU.GR IF HRN.DR # " "


HRN/VSN:  List of Students with Hearing or Vision Follow Up Data by Teacher

Provides a list of students with data in the Follow Up fields in Hearing or Vision sorted by Teacher


LIST STU HRN VSN TCH STU.ID STU.NM STU.GR HRN.FO VSN.FO TCH.TE BY TCH.TE IF VSN.FO # " " OR HRN.FO # " "


Scoliosis


SCO:  List of Students with Scoliosis Screening Results for the Current School Year

Provides a list of students with Pass or Fail data on a Scoliosis Screening tested during the current school year


1.  7th Grade Females

     LIST STU SCO STU.ID STU.SC STU.NM STU.GR SCO.TD SCO.SCN SCO.PF IF SCO.PF = P AND SCO.TD >  06/30/20## AND STU.GR = 7 AND STU.SX = F

     (Change SCO.PF = F to search for students who Failed the screening)

2.  8th Grade Males

     LIST STU SCO STU.ID STU.SC STU.NM STU.GR SCO.TD SCO.SCN SCO.PF IF SCO.PF = P AND SCO.TD >  06/30/20## AND STU.GR = 8 AND STU.SX = M

     (Change SCO.PF = F to search for students who Failed the screening)


SCO:  List of Students who were Not Screened or Failed the Scoliosis Screening

Provides a list of students who do not have any data in the Pass/Fail field or Failed the Scoliosis Screening


LIST STU SCO STU.ID STU.NM STU.GR SCO.TD SCO.SCN SCO.PF SCO.CO IF SCO.PF = F OR SCO.PF = NULL 


SCO:  List of Students with a Doctor Referral - DR = R.  In Texas use DR = Y for State Screening Reports.

Provides a list of students who were given a Doctor Referral (DR = R) after Scoliosis Screening


LIST STU SCO STU.ID STU.NM SCO.TD SCO.SCN SCO.DR SCO.CO IF SCO.DR = "R"
(Change SCO.DR = "Y" if using Y/N drop-down)


Vision


VSN:  List of Students with Vision Testing Results (Pass/Fall) within a Specific Date Range by Teacher/Counselor

List of students Pass/Fail results for Vision testing within a specific date range sorted by Teacher/Counselor Number


LIST STU VSN STU.ID STU.NM VSN.PF STU.CU BY STU.CU IF ( VSN.DT > ##/##/20## AND VSN.DT < ##/##/20## )


VSN:  Vision Results on Students for Charting

Provides a list of students with Vision results for charting


LIST STU VSN STU.ID STU.NM STU.GR VSN.TD VSN.PF VSN.RG VSN.LG VSN.BG VSN.RN VSN.LN VSN.BN VSN.CT



Label Queries


IMM:  Address Labels to Parents for Students Missing Physical (CHDP)

Labels to parents of students who are missing a Physical (CHDP)


LIST STU IMM "TO THE PARENT OF:" \ STU.FN STU.LN \ STU.AD \ STU.CY "," STU.ST STU.ZC IF IMM.H1 = NULL


IMM:  Address Labels to Parents for Students Missing 2nd MMR or any Hepatitis Vaccinations

Labels to parents of students who are missing the 2nd MMR vaccine or any of the hepatitis shots 


LIST STU IMM STU.PG \ "RE:" STU.NM \ STU.AD \ STU.CY STU.ST STU.ZC IF IMM.A2 = NULL OR IMM.I1 = NULL OR IMM.I2 = NULL OR IMM.I3 = NULL


SCO:  Address Labels to Parents for Students with a Doctor Referral

Labels to parents of student who require a Doctor Referral from the Scoliosis Screening


LIST STU SCO "To the parent of:" \ STU.FN STU.LN \ STU.AD \ STU.CY STU.ZC IF SCO.DR = "R"
(Change SCO.DR = "Y" if using Y/N drop-down)