In a prior post, I wrote about how Monarch outperformed Access as a report writer. Today the wind has blown the other way, and we’ll focus on how Access can provide additional functionality for your Monarch models.
Monarch user Diane recently asked whether a logarithmic function was available in the software. Unfortunately it is not, so the hunt was on to find an alternate solution.
Initially I thought that she might be able to export values out to Excel and then apply Log functions to the data, and would then finally be able to import back into Monarch with an external lookup. But the large data sets would prevent that with Excel 2003.
And that was a good thing, because it forced me to look for another option, and what I devised was, I believe, a better solution.
It occurred to me from my SQL programming efforts that many functions can be called in the process of querying the database, so I checked to see if the Log function could be called in an Access query. It could!
This small act can let you effectively add to Monarch’s abilities pretty easily. I described the steps to take in detail on Datawatch’s forum, but here is the general concept.
First, we need to create the initial Access database in which to build our custom query, so export the table records to a new Access database. For this type of need, there’s a good chance the query will be used regularly, so be sure to setup a Project Export with Monarch.
There’s an important step while defining the export, and that is to tell Monarch not to overwrite the file when exporting, but instead to add data to the database. Why is that? If Monarch overwrites the Access database, it will destroy the custom query that will be defined in the next major step. Finish defining the database definition by electing to overwrite the destination table within the database and running the export.
Now start up Access and open the newly created database and create a new query with the query wizard. Add all of the fields that you need to the query and select the default of Detail and finish the query. Open your new query and under the View menu select SQL View. You’ll see something like
SELECT Table.Field1, Table.Field2
FROM Table;
This is where you can define a new field using a function. Just add a little bit to the SQL that Access creates.
SELECT Table.Field1, Table.Field2, Log(Table.Field1)/Log(10) AS LogValue
FROM Table;
The “AS” lets you name the field as you desire, instead of letting Access assign a name for you.
Saving the query stores your customization in the database and this addition to the database will be used by Monarch Pro. This is the next step of the process.
Back in the Monarch model, create a new external lookup. Select the Access database, and instead of selecting the table that was exported from Monarch, select the new query.
When Monarch runs the query, the SQL statement calculates the function using the values in the table, and the results of those calculations are brought into Monarch.
OK, interesting enough, but what can be done with it?
There are a number of functions that are available in Access that aren’t built into Monarch. Which one can you use to your advantage to help you excel with Monarch?
