24 May 2006

Real World Access (1)

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

Graham Mandeno’s joinery

A medium sized business - a timber joinery - which builds very high class custom door and window assemblies for well-heeled clients.  They understand Excel and used to set up spreadsheets to calculate quantities of time and materials needed for different assemblies.  The results of these calculations were used to produce customer quotes.  This was very flexible, but also extremely time-consuming, so they asked me what could be done.

I set up a database for them (originally Access 2 on WFW, now Access 2003 and SQL Server on SBS) in which they could define "templates" with each template being associated with an Excel spreadsheet.  Each template was related to a table of input and output parameters, with each parameter being associated with a given cell in the spreadsheet.  The application asked for the input parameters, fired up an automation instance of Excel with the spreadsheet open in the background, popped the parameter values into the required cells, recalculated the spreadsheet, and then extracted the results from the output parameter cells.  The spreadsheets could perform whatever calculations they required, provided the necessary values were placed in the appropriate cells. Once the quantity of time and materials had been ascertained by this process, it was an easy matter to apply unit costs from the database and generate the quote.  Also it was easy to create variations (how much would this cost in mahogany instead of cedar?)

As time went on, the app has grown with the addition of invoicing, order entry, stock control, CRM, links to MYOB accounting system, etc.  Basically it now runs their entire business!

Tags: ,

Powered by Qumana


Post a Comment

Links to this post:

Create a Link

<< Home