Need help with database setup/search

[M2345]

Hi,

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.

Thanks.

[June7]

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:

tblLines
LineID (PK)
LineDesc

tlbEquipment
EquipID (PK)
LineID (FK)
EquipDesc

tblDownTime
EquipID (FK)
DownDateTimeStart
DownDateTimeEnd