There are two required steps to create an Ad Hoc Query.
- In Step 1, define the criteria to limit the data returned. Keep in mind, you do not need to fill in every item. In fact, it's better if you don't. Leave items blank if you want to return all values for that item. A common mistake is to define too many criteria which ends up excluding too much data. We suggest you keep the criteria broad (for example, choose FISCAL YEAR or STATE) then use tools in Excel to further refine your data.
- In Step 2, choose the groups of data you would like to see. For some types of data, one or more of the groups defaults to selected. For other, no group is selected by default. You must choose at least one group, but you may choose multiple groups.
After setting your selections in Step 1 and Step 2, you are ready to run your ad hoc query. Click the RUN QUERY or press ALT-R. However if you wish, you may specify addition criteria (in Step 3) or the sort order (in Step 4). However, we recommend you perform these functions in Excel.
We have a few tips for you about setting criteria. In some cases, you may be able to select the value from a dropdown list. In other places, you may be allowed to type it in. When typing in criteria, keep in mind that it must match the results exactly. Type carefully! If you wish, you may enter a list of values separated by commas (but be careful not to add extra spaces). If you're not sure what the values are you may use one or more percent signs as wild cards. For example, if you enter Y% the system will return any result that begins with the character 'Y': 'Yes', 'Yup', and 'Yabba Dabba Doo!' would all be returned.
When you run your Ad Hoc Query, you will have three options to deliver the results. Format for printing redisplays the results on a screen suitable for printing, though you may need to adjust your printer's settings for optimal results. Save to Word and Save to Excel create a document or spreadsheet (respectively) and open the file on your workstation. You will need to save it to your local hard drive if you want to retain a copy or if you wish to format it further.
Some people have asked about how to filter data to values within a range or other complex filters. Ad Hoc Query is designed to pull data out of the website easily - for complex filtering, sorting, and formatting you use the tools available in Excel.
For example, let's suppose you wanted to pull out a query where the numbers in a particular column are between two parti cular values (say, 100 and 1000). We suggest leaving the query as broad as possible to get data out of the website. in Ad Hoc Query, select (perhaps) a year or a state, then click RUN QUERY. When you get the results, click on SAVE TO EXCEL. In Excel, click on DATA on the header to show the DATA ribbon. Move your cursor to the row with the headers and click FILTER. This will turn on the "auto-filter" feature. For each column there's a dropdown button to let you filter on that column. Excel offers a lot of options - you can deselect zero to find all the non-zero values, or you can use the number filters to specify a range. There's a lot of (free) information on the internet about how to use Excel, so if you get stumped type a question into a search engine such as Bing, Google, or Duck Duck Go.
If you need additional help with this feature, please send an email to RSAMIS.Technical.Support@ed.gov.