Is there anything more boring than the “gruntwork” of selecting individual records from the list view in Aspen so that you can “Show Selected?” Have you ever clicked 50 records and lost your work when you viewed a student detail page to make sure you selected the right record? A light bulb went off in my head the day it occurred to me that an Excel function could concatenate fields in a spreadsheet to create a SQL query from a list of any length. Since then, I have used this method to create queries for as few as 5 records, and as many as 4500, based on such elements as local/state ID, course IDs and conduct incident numbers. This has been particularly useful when troubleshooting state reports. To adjust the length of the query, all that is needed is to adjust the number of rows in the Excel sheet.
Download the Excel workbook at the end of this entry, and then follow the steps below to utilize an Excel sheet as a “Query Builder” to create custom SQL statements. I have included sheets for LASID, SASID, STD NAMEVIEW, STAFF LOCAL ID, and STAFF STATE ID, but you can expand this to include other data elements following the same model.
Example: Use a validation error report to select multiple students from a list by state ID.
- Open your error report or list of sasids in Notepad++.
- Use “column mode” to isolate the state ID for all students in the report. (To access column mode, in Notepad++ hold down <Alt> key and then click and drag the cursor to highlight the entries in the column that you wish to select.)
- Click < Ctrl >< C > to copy the column of highlighted student IDs.
- Open the Query sheet in Excel and Paste the state ID into column B.
- Copy the SQL from column C.
- Paste the SQL from column C into the Options > Query > >Advanced SQL box in Aspen and click “Run” to run the query and display your results.
Such queries, which identify particular records according to a specified data element, can also be used to create a snapshot in areas of the program where queries are enabled, but snapshots are not. To create a “snapshot” from the query in locations where snapshots are not available, simply save your query as you would any other, named in a way that is meaningful and indicates that it contains static info. If you share this out to other users and add it to the filter menu, they can use it to isolate the records that they need to validate.
You can test this out by copying and pasting a couple of your own SASIDs in the SASID sheet. Let me know if these directions are not clear… Hope you agree that this is a time saver! Click here to download the Excel workbook for the Query Builder.