Iíve been away from Access for a while, but at a new job it got out that I had done some database design work in the past, and low and behold now Iím the database guy for the company, and already have my first project. AND, I already need help, so Iím looking to the brilliant minds here to give some advice on direction. Iíve given this project quite a bit of though already, so hereís where Iím at:
The objective of the database is to collect and analyze machine performance data and allow for easy comparison to the planned performance vrs the actual performance of the machines as well as of the parts produced. For those of you familiar with the concept of Overall Equipment Effectiveness (OEE), Iím basically trying to build a pared down version of an off the shelf OEE tool. (If anyone has done this and has an example database I could look at, Iíd appreciate it!)
Inputs to the database are basically all of the data the describes the actual output of a work order, including the quantity of parts produce and quantity of scrapped or defective parts, the time it took to make the parts (or the cycle time per each), the downtimes during production of that job, and perhaps some other details like who was operating the equipment during the production run.
There are 3 main desired outputs: 1) A report that allows for easy comparison between what we have bid a job at, what we scheduled the job at, and what we actually performed at. two of these pieces of data are in our existing ERP, the third would have to be tracked over time, aggregated, and reported if say, actual times are over 10% longer than bid time, or whatever I choose the report to show. 2) A report that gives the demonstrated output per machine that would feed an existing capacity planning tool. 3) A report that shows the major performance losses per machine. Basically, a report that could answer the question, Ďwhat were the main downtimes on Machine 1 last week/month?í
About half of the information required to produce all of the reports and such are in our existing MRP system (some old green screen thing), including the standard setup times, standard cycle times, bid times, work order numbers, routings, machine IDís, full parts lists, ext. It is very easy to have the system spit out flat files on a regular basis, so any Access database I develop could look at these and get up-to-date routings, machines, cycle times, setup times, ext. Basically, everything in the ERP system.
Now, to my first and biggest issue, where should I store the performance data that I need to capture?
Option 1) Re-design our existing ERP system to capture most (all?) of the performance data in the existing Work Order tables. Then create an Access database than links to all of the relevant flat files, does all the queries, reports and graphs, and perhaps saves some aggregate data. Advantages I see: Keeps all the data together, less to develop in Access. Disadvantages: Long lead time to develop in our ERP system, I need to use our ERP admin, as I canít do this work. I should note that I have confirmed that even if all of this data was in our existing ERP system, it does not have the query and reporting capabilities to provide any of the required reports, so I would still need to build this in Access.
Option 2) Develop Access database that tracks all of the performance data, and links to the data (via flat files) that our ERP system contains. Advantages: No ERP modification, I can do all the work. Disadvantages: Challenge matching performance data with work orders. Delay in when the data can be input, as I canít match a work order until that work order is put in the flat file and Access loads that flat file.
Option 3) Develop completely stand alone db, which is what all the 3rd parties want to sell us. Advantages: heck, Iíll just buy one instead of build it. Disadvantages: We have a tough enough time keeping our information accurate and up to date in our ERP system, to have a separate system to keep much of this data in parallel sounds impossible. (not seriously considering this option)
So Iíd love to hear about what you all think is the right way to go about building a system that needs to run alongside another existing system and how you recommend accomplishing that.
Thanks for your advice!