TechRepublic member Todd Howard, principal consultant with Ultimately Digital Inc., doesn’t let habit stand in the way of innovation or cost savings. He has found inventive ways to use Microsoft Access to save time and money for his clients.

“Often, I find that while participating in a project that is using the latest technologies to develop a new application, the project itself is being managed either manually, or using only rudimentary tools,” he said. By using Microsoft Access, he has been able to automate key processes with a minimum of effort, resulting in significant cost savings.

I got the scoop on two ways Howard saved the day—and the dough—for his client during Y2K testing.

Microsoft Access as a test coordination tool
Though it’s an uncommon practice, Howard has used Access as a test coordination tool. Many traditional test tools on the market are quite costly, and they don’t necessarily help you in automating very business-specific aspects of your testing, he said.

In one case, Howard was working on a Y2K project, and his team determined that the most effective way to proceed with testing was to simulate various Y2K-sensitive dates and have the mainframe system generate approximately 200 reports. The reports then had to be sorted by business unit and assigned to process owners, who would verify that they were correct.

It seemed like a good plan, but Howard soon realized it had some flaws.

First, the business units were reluctant to allocate their resources to what they perceived to be his team’s testing effort, he said. In addition, the team had to test more than a dozen dates and produce a wide variety of monthly, weekly, and yearly reports.

The variety and number of reports created huge tracking and dissemination issues. Additionally, the team had to determine what needed to happen within the system during the simulated dates to produce the appropriate reports.

“In a nutshell, it was hugely complicated, and the Big Five consulting firm in charge of the project was ready to hire a couple of test coordinators to look after this,” Howard said. “Less than two days of effort with an Access database and we had a system that automated the entire tracking process.”

To make his solution work, he imported a list of all the necessary reports into Access, along with another list of which reports were produced under which circumstances. He then created a few simple forms that assigned the reports to key business processes, before assigning users to the key business processes. That way, the team was able to create documents that listed which reports would be produced for any given Y2K-simulated test date, and who would need to verify each report.

Howard estimated that it would have originally taken two test coordinators a month to manually coordinate the testing process which, at their estimated $500 per day, translated to $20,000. His alternative procedure took him only two days and reduced the test coordinators’ efforts by 90 percent, resulting in a cost savings of approximately $18,000 (see Figure A).

Figure A

Howard’s method also improved the efficiency and success of the testing process.

“If we hadn’t automated the process…the business users that were assigned [to verify the reports] would have had to invest a lot more time,” he said. While it would be difficult to estimate the value of the added efficiency factor, Howard said the Access database was probably worth double the estimated cost savings.

Access linked Project to an asset management database
In another instance, Howard was faced with a need for rapid, accurate status reports about the testing and implementation phase of another Y2K project. The team built two plans in Microsoft Project: one for test execution and the other for implementation status. Also, they created an asset management database that identified every information technology asset in the company. Each asset was assigned a “Target Level of Y2K Readiness.”

“Management was vitally interested in observing the progress of testing and implementation on these assets,” Howard said. “Our approach was to keep the MS Project plans extremely up to date with the daily status of testing and implementation tasks.”

Each asset had an associated task for its testing and implementation on the project plan. Since Project was the most timely and accurate repository of project status information, Howard created a small database that brought together the Project data and the information from the asset management database. By establishing a custom inventory ID field for each task in Project, he was able to export information about the project’s tasks into the new database, which could then associate the asset using the same inventory ID.

The completed database was capable of quickly producing detailed reports that concisely summarized the exact status of every item in the asset management database, Howard said. The new database made it unnecessary for a project coordinator to manually provide updates, which Howard estimated would have taken about 30 percent of the coordinator’s time during the course of the five-month project, at a cost of $15,000. The two days Howard spent creating the new database and the time saved by automating the status reports made his solution cost an estimated $3,500, for a total project savings of $11,500 (see Figure B).

As a bonus, the project’s visibility was improved by the extremely timely and accurate status reports, Howard said.

Figure B

From the client’s point of view…
His clients rarely have any reservations about his unconventional use of Access, Howard said. “In the instances where I’ve used Access to automate a process that would require tedious manual work, it’s a very easy sell.”

All it takes to convince clients to use his methods is a quick calculation of the cost for the manual process by a consultant vs. the day or two it takes to get a database up and running, he said. And, because Access is a common desktop application at most of his clients’ sites, there is no need for a business case to get the tool.

“It’s a matter of, we’ve got the tool, so why not use it effectively?” he said.

Earn a buck when you share your cost-saving tips

Have you developed a way to save money in your IT department? Share your tips with TechRepublic. If we publish your tip and it is substantive, we’ll send you $50. If we publish a brief, quick tip that you’ve submitted, we’ll send you a TechRepublic mug.