Windows XP 
Access 2007
Hi all. I have been tasked with importing an Excel form into Access. I slogged through the first revision of this form, but now a new one is coming out, and it has added a lot of complexity to the form. I *could* force Access to collect this data, but it is not normalized or optimized for Access, so instead I would like to redesign it.
The purpose of the form is to collect data about a manufacturing process (shift patterns, cycle time, scrap rate, downtime, etc), compare this data against demand volume, and output a judgment that will tell the user if the process is capable of meeting the required demand on a daily basis. This is very similar to the AIAG "run-at-rate" form.
The problem with this form is the collection of sub-processes. Originally, the form allowed for up to 10 processes (anywhere between 1-10 was accepted). However, this didn't adequately capture some supplier processes, so the decision was made to increase the maximum processes to 30. From an Excel standpoint, this is no problem, but from an Access problem, this adds to the problem of wasted data.
Currently this is how the data is collected, by part number:
PART_NUMBER PROCESS CYCLE_TIME PROCESS_OBSERVATION_MINUTES PARTS_RUN 1234567890 1 10 60 600 1234567890 2 10 80 752 1234567890 3 10 75 620 1234567890 4 10 90 823 1234567890 5 0 0 0 1234567890 6 0 0 0 1234567890 7 0 0 0 1234567890 8 0 0 0 1234567890 9 0 0 0 1234567890 10 0 0 0
This data is not normalized, because Access ends up storing a lot of zeroes if the process is not using all 10 process input blocks. Adding an additional 20 sub-processes will only end up in more wasted data.
My question is, how can I optimize my Excel form? I need to think of a way to design the form to allow for any number of processes, from one-infinite, and eliminate empty garbage data in my database.
Thanks for any help you can provide!
Reply With Quote
