November 14th, 2012, 11:09 AM
Need help with database setup/search
I've been using access for a couple of months now, just getting by for data analysis. I've got my database table set up to try and enter production downtime codes. The problem is that I have the database set up to record production line downtime codes so I can analyze production, but I also want to monitor each piece of equipment within the line and it's downtime trends. I look at the line as a whole as far as date, work order, scrap, good/bad parts, operator, etc., but I want to also look at the individual pieces of equipment in the line and see if one is trending to be a major cause of downtime. I want to get a good baseline on what "normal" is for each piece of equipment, so I can then start at the biggest problem and start improving these processes. I have separate columns in access for downtime code 1, downtime minutes 1, downtime code 2, downtime minutes 2, and so on. While this is ok, when I want to search for a specific code I have to look at multiple columns for the same number and there's got to be a better way to either set up my database or search it. I input a stack of these shift reports to get the data every week, so I don't have a lot of extra time to create separate tables, but would be willing to do it if that's what's gotta be done.
I know I'm bound to be leaving some detail in trying to describe this to make it clearer, but any help would be appreciated.
November 14th, 2012, 03:58 PM
You need to normalize the data structure. And yes, this could mean more tables. Review 'sticky' thread tutorials at top of forum.
Otherwise, there are techniques to manipulate non-normalized data (UNION query is one) but probably just as troublesome as fixing the data structure.
Each 'downtime code' field is for a piece of equipment on that line? Consider:
Last edited by June7; November 14th, 2012 at 04:00 PM.