28 May 2006

Real World Access (9)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Duane Hookom's nutritional products

Our factory makes products like infant formula and other nutritional products. It is very important to track which ingredients are used in which products. Each "recipe" is stored in its own Excel file and these are "controlled" documents. Our quality staff had been keeping a huge spreadsheet listing ingredients across the top and products along the left side. Picture "X"s in a grid. Searching and maintaining the cross-reference was a huge but necessary task.

I created a fairly simple Access application that pulled a list of Excel files from a folder on our server. Users would click a button and code would open each Excel file individually and find the top of the list of ingredients. The ingredients and products were read and stored in a normalized table linked from SQL Server.

Finding out which ingredients are in which products and vice-versa are now a snap. I actually have reports/queries in Access and on our intranet. Users are super happy with a minimal amount of work. I would like to replace all the Excel files but they have lots of other functionality and are "controlled". I put in about 15% of the total effort and got about 85% return in less maintenance and greater accuracy with the new system. Some times you have a draw a line when measuring return on investment and other priorities.



Post a Comment

Links to this post:

Create a Link

<< Home