Getting Data using MS Query in Microsoft Excel 2003 Importing Data from Access into a New Query Data can be imported from an Access (or other) database into Excel via the Import External Data command from the Data menu. You can also import data stored in another Excel file. When importing data from Access use New Database Query: 1. Start up Excel to get a new spreadsheet 2. From the Data menu choose Import External Data then New Database Query... 3. On the Databases tab, select the Data Source type - MS Access Database* - click on [OK] 4. Select the required file - change Drives to User (D:), open up the Training folder and choose example.mdb then click on [OK] (the file can be downloaded via the link provided here) 5. The Query Wizard now starts up: i. Select the Students table and click on the + to display the fields - move across the first 6 fields up to and including Hall (double click or select and click on [>]) - click on [Next>] ii. Set up a filter (none is required here) - click on [Next>] iii. Set up any sorting required (again none is required) - click on [Next>] iv. Choose Return Data to Excel to simply paste in the data. View Data or Edit Query in MS Query lets you create a parameter query, which you will see later - click on [Finish] 6. Finally, state where you would like your imported data to appear - which cell on which sheet, here choose A1 on the current empty sheet - press <Enter> or click on [OK] to finish Editing the Query You can now make any amendments required by editing the query: 1. If it isn't already displayed, turn on the External Data toolbar: from the View menu choose Toolbars and select External Data 2. Click on the [Edit Query] button - first one on the toolbar - to reactivate the Wizard 3. Click on [Next>] (unless you wish to add extra columns) 4. Set up a filter - click on Hall in Column to filter then use the list arrows to first select equals then choose any hall (eg Bridges) - click on [Next>] 5. Set up a sort on Surname in ascending order - click on [Next>] 6. In the final step of the Query Wizard turn on [View data or edit query in Microsoft Query] then click on [Finish] The results of the query are shown in the bottom half of the Query Window. The query would be of wider use if it were turned into a parameter query. To do this, you have to change the current filter Value into a question surrounded by square brackets. 7. In the Value: row type [Which Hall?] 8. Close the MS Query window by clicking on the close window button 9. Enter a parameter value - eg Childs then press <Enter> or click on [OK] To refresh the data or obtain a different set (as a result of supplying a different parameter), simply click on the [Refresh Data] button, the fourth on the External Data toolbar. Note that once a parameter query has been set up, you can't make changes to the query with the parameter prompt present. Edit the query, removing the prompt, and then make the required changes. Finally, reinstall the prompt (if still required) once the changes have been made. The toolbar has various other useful buttons: 10. Click on [Data Range Properties] (the second toolbar button) and have a look at the options provided (you can, for example, ask for the data to be refreshed each time the file is opened, or every n minutes) - click on [OK] to close the window 11. Click on [Query Parameters] (the third toolbar button)- this allows you to Get the value from the following cell rather than via the Prompt for value using the following string message For this to work, you have to first have a value in a cell: 12. Click on [OK] to close the Parameters window 13. Type the name of a Hall (eg Wells) into an empty cell (eg K1) 14. Click within the data from the query to activate the External Data toolbar 15. Click on [Query Parameters] again and set Get the value from the following cell: to =K1 then turn on Refresh automatically when cell value changes - click on [OK] The filter now works via the specified cell. 16. Type a new value into cell K1 (eg Wantage) and a new data subset will be shown. Using a Drop-Down Menu to Supply a Value If you really want to be clever, you can use a drop-down menu to supply the value for the query parameter. First, you have to create a list of possible values: 1. In an empty cell (eg M1) type the number 1 - press <right arrow> 2. In cell N1, type the name of the first Hall (ie Bridges) 3. Move down to M2, type the number 2 then move to N2 and type another Hall (eg Bulmershe) 4. Continue filling columns M and N with numbers and the names of the Halls as they appeared in the original data (eg St Patrick's) 5. Open the View menu, choose Toolbars and then Forms 6. Click on the [Combo Box] button and draw a box covering K1 to L1 (drag across the cells) 7. [Close] the Forms toolbar 8. Right click on the new Combo Box and choose Format Control... 9. On the Control tab, set the Input range by dragging through the Hall names typed into the cells in column N, set Cell link to L1 and Drop down lines to the number of Halls in the list 10. Press <Enter> or click on [OK] to close the Format Control window This above step stores the number of the chosen Hall in cell L1, which is hidden from view by the combo box itself. 11. Click away from the Combo Box to release it, then use its list arrow to test that it is working correctly 12. Click in cell J1 then press <right arrow> to move into K1 Remember, this cell controls the data shown in columns A to F. Instead of a fixed value, this needs to change as a new value is picked up from the combo box. This is achieved through a Lookup function: 13. Click on the [Insert Function] button on the Formula Bar 14. Set Lookup & Reference from the Or select a category: list and LOOKUP as the Function name - press <Enter> or click on [OK] 15. Press <Enter> or click on [OK] a second time for the more complicated form of the function 16. Set the Lookup_value to L1, set Lookup_vector by dragging down the numbers typed into the cells in column M, set Result_vector by dragging through the Hall names in column N - press <Enter> or click on [OK] to complete the function 17. Test out the drop-down menu by choosing different Halls from the Combo Box Note: To hide the lookup list of halls and numbers, simply change the font colour in these cells to white. Alternatively, hide the columns or place the cells on a different sheet.