That was yesterday?

May 14, 2007 7:02 AM

I own a small importing business. Each of our orders has in the region of nine critical milestones that have to occur for us to sleep well at night. Currently we use MS Access and run reports weekly to make sure nothing gets overlooked. If Access had alarms alerting us to overdue milestones it would be ideal but that does't seem to be the case. Does anyone have any suggestions for software that would do this for us? It needn't be heavy duty - in fact the simpler the better.
posted by Umhlangan to computers & internet (3 comments total)

Well, I think you could just do that with Access. You'd have a Projects table, with a one-to-many to a Milestones table. Each Milestone row has 1) Milestone ID (unique key), 2) Project ID (foreign key), 3) Date Required, 4) Date Completed, 5) Description. (description could be a key for a one-to-many if you use the same descriptions over and over.)

Every morning, just run a query for rows with nothing or the empty string in Date Completed, where Date Required is less than today. Sort by project. You'll get a daily listing of projects that have missed a milestone.
posted by Malor at 7:19 AM on May 14, 2007

"Milestones" can imply some kind of time oriented project management structure, but I wouldn't make a blanket recommendation of project management software on such limited information as your question provides. It could be that you are looking for something akin to "order status codes" as used in many sales order management and ERP application software packages. They generally work something like this.

You set up a table of order process steps:

100 Order line entry
105 Backordered status/inventory availability check
110 Credit approval
120 Inventory commitment/pick list generation
130 Packing list generation/ship confirmation
135 Short shipped
140 Invoiced
150 Closed

You set up fields, by order or order line to store process steps. If you handle complex order process, like backorders, or order line splits, you may need a couple of fields to store current and immediately previous status, but it doesn't sound like you would.

You set up query processes that run with your business processing steps to advance order status according to your table of order process steps. Running a picking list for an order for example would advance all lines on that order that were pickable from status 110 to 120, per example above.

In the same way, you could use the idea of "order status codes" to automate your purchasing functions, and track your incoming shipments, although you'd clearly have tables with codes describing relavant business process steps for your purchasing cycle, and these would be keyed to your purchase order documents and systems, in the database.

The software could then run stored queries or database procedures against the database on a schedule you set to find how many orders were in what status, and assuming your orders were time stamped by the software whenever transactions occured to change status codes, how long they'd been in those statuses, etc. This kind of thing is old hat for thousands of business application software and accounting packages out there. You can program it in Access, but Access is far from the optimal tool for a multi-user business database.

You'd be far better off to start looking for standard multi-user business transaction processing and accounting systems, now. Even large companies like Oracle/Peoplesoft/J.D. Edwards and SAP now offer Web based products suitable for small business, but there are literally thousands of vendors of this kind of software in the world. Search for products using search terms "ERP" (Enterprise Requirements Planning), "DRP" (Distribution Requirements Planning), "SOP" (Sales Order Processing), "Inventory Management" etc. to generate plenty of sources.
posted by paulsc at 7:40 AM on May 14, 2007

Basecamp is great for setting up tasks, managing them with To-Do lists and calendars, and then arranging milestones (which you are reminded of via email). Best of all, it's web-based.
posted by deern the headlice at 7:46 AM on May 14, 2007