Hi everyone, first time authoring here and looking to pass on one of the neat, but less intuitive aspects of data management in Excel.
Often I find myself with data in an external database, such as Access and continuously editing the query there to get the data how I want it in Excel. With care, this can be done directly in Excel. (Using 2003, but earlier version will be similar).
Open a workbook and on the active sheet in cells A1 enter Start Date and in B1 enter the date 01/01/2003. In A2 enter End Date and in B2 enter the date 02/02/2003.
Take the menu options Data>Import External Data>New Database Query…
You will fire from here a dialog asking for your selection of an external datasource. We have chosen ‘MS Access Database’.
Navigate your way to your Access database and select the table or query you want from the list displayed and add the fields you require, as below. (Remember, if you have a parameter query in Access already, this will create an error if we try to use it in Excel. ‘Too few parameters, expected 1’) .
Move on three screens making no changes until you arrive at the final screen (below). Take the second option to ‘View data or edit query in Microsoft Query’. This will launch Microsoft Query. (For those familiar with Access, this looks very similar to the query designer).
From the image below you can see we have shown the ‘criteria grid’ by selecting View>Criteria from the MS Query menus.
In our example we are going to take orders with a ship date between two dates, (01/01/2003 and 02/02/2003). To do this we enter the operator ‘Between’ followed by our first parameter. These are enclosed in square brackets and what is in here will, in certain circumstances, appear as the prompt in the input box, with the entry being the parameter. ‘Between [Enter the start date]’.
The next part is the ‘And’ operator followed by our second parameter, completing the parameter thus:
Between [Enter a start date] And [Enter an end date]
In MS Query select File>Return data to Microsoft Excel. You will be prompted for your two parameters. (start and end date), but you can ignore them. (Answer OK).
You will now get the dialog below, asking for the positioning on the sheet.
Click the ‘parameters…’ button to show the dialog below. You now have three choices.
- Prompt for the values. (You can enter any prompt here).
- Use the following value. (You can enter a static value).
- Get the value from the following cell (Our example).
Remember to set how the value is obtained for all values and, if you want the data to update each time you change the value of the cell(s), then select the checkbox. (against each value again).
Click OK in the ‘Parameters’ dialog and select $A$4 as the cell for the start of the data, click ‘OK’ in the ‘import data’ dialog and your data should flow in filtered between the two dates supplied. Each time you change the dates, the query is refreshed with the new input.
If you find you wish to change parameters or the way they action at a later date, this can be done in Excel via ‘Data>Import External Data>Parameters…’ or via the ‘External data’ toolbar. (Above).
Hope you can use this and any comments welcome
Nick Hodge
MVP – Excel