I recently had an interesting challenge that required the passing of two date parameters from an Excel spreadsheet over to an Access database in order to return data that met the parameters of data between the first date and the second date. The Excel spreadsheet template reported the data in a variety of static reports based on the date range of the data returned from the Access database. The solution to this problem was to use the Microsoft Query software available in the Excel spreadsheet. The following is a demonstration of how you can pull data from a Access database back into an Excel spreadsheet.
The following is an example of the report we want to populate from the Access database with data that meets a certain date range:
Our Access database contains over four years of data at the weekly level, but we will only want to pull back certain weeks of data to populate our report. The first step in our process will be to set up an external data pull using Microsoft Query:
We simply need to follow the prompts to find the appropriate Access database:
The next step is to select the table we want to extract our data from and select the fields from that table that we want to display in our data extract:
Then we follow the prompts through the Microsoft Query Wizard until we get to the point where the Wizard asked us where we want to return the data to and select "View data or edit query in Microsoft Query":
In Microsoft Query click on Show/Hide Criteria to make visible the criteria fields and then select from the criteria field the desired field that we will want to pass a parameter to:
In our example query we have selected the week ending field for which to develop our parameters to be passed and we will enter the Access formula for Between to enter the start date and the end date for our query to select data from the associated Access database table:
Please note that we used the opening and closing square brackets to indicate that the query will expect a parameter that we are naming Start and a second parameter that we are naming End as our starting and ending date parameters. When we have completed our parameter formula simply click on the File menu at the top and then click on the bottom option "Return data to Microsoft Excel" and we will be presented with the only option available which is to return the data as a Table. Select OK and the table will be populated in the Excel spreadsheet.
Now we are ready to set up our date parameter fields to pass to the access database, and we do this by navigating to the data tab and clicking on the Connections menu item. Select the two feels that we want to identify as our Start date and our End date at a convenient and strategic spot in the Excel spreadsheet; in our example we will go to Sheet2 and use cell A1 for our Start date and cell A2 for our End date. In our example we want to pull data from the Access database between the dates of October 9, 2010 (10/9/2010) and April 23, 2011 (4/23/2011).
The final step in the process is to now tell Microsoft Query which of these fields is related to the start date and which is related to the end date. We do this by navigating to the Data tab and then select Connection menu option:
From the Connections wizard click on the properties button on the right side of the wizard, select the Definitions tab and click on the Parameters button at the bottom of the dialog box.
In the parameters dialog box first select the Start date parameter and identify the cell associated with it (in our example cell A1 on Sheet2) then select the End date parameter (in our example cell A2 on Sheet2).
Click on the OK box and the processes now completed. There are a couple of caveats with this approach; for example you can only deliver the data back into a table and not a pivot table, however there are various formulas which we will cover in subsequent newsletters that will allow you to aggregate the data using SUMIFS and COUNTIFS formulas.
Insight, Information & Consulting Services is a data visualization consulting firm that can help your company set up Excel templates that connect to Access databases, SQL databases as well as other data sources to provide your field sales and marketing teams with actionable data delivery tools. Please contact Rick Pensa at 770-425-4243 for further information.
Comments