Author Archives: Lynn Rowan

SQL Shortcut: How to use Excel to Create Custom SQL Queries

NoShowSelectedIs 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.

Continue reading