If you have used SpEd Forms for a while, you may have noticed a textbox at the bottom of most administrative reports entitled “SQL statement in use”. This text is generated from the search criteria you select and gives the database precise instructions to find the records that are displayed in your reports. These SQL (Structured Query Language) statements show how the information is stored in SpEd Forms and can be very useful, especially when creating custom reports and importing data.
Scroll to the bottom of any report you've run and take note of the “SQL statement in use”.
Let's take a closer look at a SQL statement that was generated from the administrative “Student List”. This particular statement can be broken down into four parts.
The format used is: Table_Name.Field_Name.
SELECT: The first part of the statement “selects” the column or field names that will be used to calculate which records will be displayed and/or which fields will actually be displayed in the results table.
FROM: Any tables used in the statement are listed after the “from” keyword. In this example, the “student” table is also “joined” with the “teams” table (whenever the student.student_id is equal to the teams.student_id), the “teammembers” table (whenever the teammembers.teammember_id is equal to the student.teammember_id) and the “iep” table (whenever the student.student_id is equal to the iep.student_id).
WHERE: Next are the conditions “where” specific criteria is placed into the statement (from the criteria you chose in the search box at the top of the page). In the following example, the Student.Name_FirstName must include the characters “Some” and the Student.Name_LastName must include the characters “Student” and the Student.TeamMember_id must be “515”.
Wildcards: The % signs that are used after “like” are wildcards, meaning that any character could also be included in the name. For example, this “where” condition could also include “someone”, “somebody” or even “awesome” (if their last name also contained the characters “student” and their teammember_id was equal to “515”).
ORDER BY: Finally, this list will be “ordered by” the student's last name and if more than one have the same last name those will be ordered by first name. SQL statements can then be ended by a “semi-colon”.
If you need help in deciphering what table and/or field names to use when creating custom reports or importing specific data into your database, please contact the SpEd Forms staff. We're here to help!