Expert: Scottgem - 6/9/2008

Question
QUESTION: We carry circa 25 products.
Each product needs to be processed in 2-3 stages. circa 20 minutes/stage.
Raw materials come in several times a month. However for some products the raw materials are readily available while for others 1-2 months are necessary.
Each product is packaged differently.
Each consignment consists of a product mix. (between 1 and 25)
There are circa 5 large customers (2000 products/month) , 5 medium ones (200/month) and 10 small ones (20/month). (but none of these order every single month). At the moment most customers need to wait circa 2 months but the planning must be improved as sometimes an order can be fulfilled in 2 days.

I need to:

  1. provide future delivery dates to customers
  2. plan work shifts (1-4 people)
  3. plan which products to produce (from planned arrival date of raw material. I know 2-3 weeks in advance)
  4. plan quantities to produce for each run. (At the moment we run 5-10 products a day)
  5. optional-prepare some production benchmarks (per shift and per product). i have a vague idea though.
  6. optional- calculate true cost per order ( in terms of matrials + man hours)


What should i use (on a limited budget) ??

   * Excel template ?
   * Access template ?
   * online solution ?
   * Anything else?

Many thanks,
Nil



ANSWER: You have a database drive process here. So I would definitely recommend starting with an Access template that does either inventory, bill of materials or order entry. You might consider all three, using different front ends against a single back to perform different tasks.

I'm confident Access can handle this (definitely better than Excel can) but there will be some considerable work in creating processes for all these tasks. Its highly imperative that you design your table structure properly. and then build your front ends from there. Define what data you need to capture first, then organize that data into normalized tables.

Once you have the table structure, prioritze what tasks you need to complete first. Build each functionality separately. Make sure it works, then go onto the next.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: MS Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Many thanks,
any template you can reccommend?
does an Access newbie stand a chance?

Answer
I recommended three templates. And yes, I think you stand a chance, but its going to take a lot of work. It will be worth it in the long run.

And I (and others) are here to help you along as you go.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: MS Office Access 2007 VBA