Tables & Normalization

[rubyred]

This is an in-depht question and will probably take an Access ghuru to lead me in the right direction. I tend to be a little "thick" sometimes.

I am trying to create a DB reflecting a machine audit program. There will be approximately 25 audit items that will be checked on each machine each day and all 25 items will apply to all machines. However, not all machines (263 of them) will have an entry for each audit item on that day. Some machines will be running very good that day and may only have 5 out of 25 items.

We have to record the date - the shift - the Auditor - the Machine # and which of the 25 audit items applied to that machine # that day. Can anyone get me started setting up tables in the most normalized state. My first thought is to have a table that has the machine # and all the possible audit items and just check the ones that apply, but that could leave a lot of fields empty for each piece of equipment each day.

But how can you report just the exceptions? All I can visualize is a report with all the items listed vertically under a field called "Exceptions" but I would want a report with the items listed horizontally. I hope I am making sense, but I might not be seeing everything that I need to see here.

Thanks to anyone who may take me on as their "challenge".

 

[rubyred]

OOOPs! I just realized I put this in the wrong forum and I don't know how to tranfer it. . . Sorry!

 

[RichMorrison]

Sounds to me like you want to start with 3 tables.

Table 1 is "AuditItems". It would have a simple numeric primary key, probably AutoNumber. Add some text field to describe the item ("smoke pouring out of machine")

Table 2 is "Machines". Again a simple numeric preimary key. Add a text field to describe the machine ("grinder #12")

Both Table 1 and 2 are fairly static assuming your audit items don't change often and the machines are fairly permanent.

Table 3 is "ItemMachine" and describes the occurence of an exception. Primary key would be Item#(PK of Audit Item table), Machine# (PK of Machine table), and a TimeStamp field.

Table 3 would also contain shift, name of auditor, etc.

Whenever an auditor observes a fault or exception in a machine, that auditor makes an entry into table 3.

If you want to create a report with Audit Items across the column heading (horizontal) you would use a "cross tab" query. But that's another topic.

HTH,
RichM

[This message has been edited by RichMorrison (edited 05-11-2002).]

 

[rubyred]

Thanks for answering my post. I am following you and have a few questions if you don't mind.

The machines involved are weaving machines and they are identified by their loom number (Loom #101 - Loom # 1505, etc.) For purposes of the audit program, we will be looking at "like failures" that apply to all looms. However, some of the possible failure items involve reporting "how many" failures. So what we have is some fields being a Yes/No and others counting how many "failures" are visable.

I guess we are not actually looking at the machine itself, but what it is producing and how good a job is being done during the production.

Would I name these fields in table 1 for the Yes/No "Weaving Defects" and assign it as a text field and the ones that need a quantity "Quantity of Holes" and assign it a number field?

It this acceptable?

I'm still concerned about reporting as I know the Company will want to see a report that has each loom number listed on the left as the row and ALL the possible failures listed to the right of each loom whether it had an entry or not, each in its own column. Will crosstab queries become too complex for this type of reporting?

Thanks in advance for your help!

 

[RichMorrison]

Sounds like you will want various types of reports. Most reports should be based on queries against Table #3 joined to Table #1 or Table #2.

The queries will count the rows in table #3 to determine:
the number of faults of a specific type for any machine by joining #3 to #1,
or
the number of faults of any type for a specific machine by joining #3 to #2,
or
all the faults for all the machines.

Since Table #3 will contain a Date when the fault was observed, you will be able to select records based on time period. You report on total faults by Month by selecting all the records from Table #3 between 1/1/2002 and 1/31/2002.

You won't need any numeric or yes/no fields in order to produce reports. You will just count the rows in Table #3.

If you have a copy of the "NorthWind" demo database, look at the reports for Product Sales By Type or similar reports where things are grouped and counted.

Finally, you wrote:
<<
I'm still concerned about reporting as I know the Company will want to see a report that has each loom number listed on the left as the row and ALL the possible failures listed to the right of each loom whether it had an entry or not, each in its own column. Will crosstab queries become too complex for this type of reporting?
>>
Yes, you can list the looms down the page on the left and the failure types as horizontal column headings. A crosstab query will do this. Look for crosstab queries in Access Help or in some good manual.

HTH,
RichM

 

[rubyred]

Thanks once again Rich. I am going to experiment with it later this afternoon. I'll let you know how it goes. I may need to seek your assistance further once I get it going. I hope that is OK with you???

I sincerely appreciate you taking this time to help. Most of my databases to this point were more straight-forward such as date, shift, operator, pounds produced, etc. This one is new to me as far as recording exceptions only. Thanks sooooooooo much!

Until later. . .

 

[rubyred]

Rich. . . . It's me already. I am confused. I report a failure on one of the looms but in addition to knowing that there was this particular failure present (from Table 1), we need to know how many there are.

Let's say for example, Loom #1604 has Floats (one of the items in the Audit Item Table). Now we want to know how many actual floats are visible (this could be a high # like 25- 50 etc.) How would I record the quantity? There are probably 6 to 8 fields in the Audit Item Table with this situation. Not only recording the audit failure but having to say how many make up that failure. Sorry to be so "thick". Please help!

 

[RichMorrison]

your wrote:
<<
Rich. . . . It's me already. I am confused. I report a failure on one of the looms but in addition to knowing that there was this particular failure present (from Table 1), we need to know how many there are.
>>

That seems reasonable. You can add an additional field to Table #3 to record how many times a failure occurred. As you stated, not all failures will need a number of occurrences recorded. So some rows in Table #3 would not have any meaningful data. In table design, you can set a default value for numeric fields. Set the value to 1 and allow the person recording a fault to change the value to some greater number if needed.

RichM

 

[rubyred]

Rich,

Just wanted you to know that I am just about to let this database go active.

I used your "valued" advice for starters and then went from there. It was a struggle, but I believe my DB is normalized to a high degree (5? - maybe). Table 1 has Date, Shift, AuditorName and then Transaction Number as the PK. This way you only have to enter and/or store the date, shift and auditor one time per audit instead of approximately 350 times.

Table 2 has Transaction Number (same as table 1), MachineNumber, StyleRunning, and the ExceptionAuditItem (some of which still need another entry for quantity). Also, one machine may have up to 5-6 audit items during the audit. Sooooo
Table 3 has Transaction Number (same) Exception AuditItem and Quantity.
It works beautifully so far. Table 1 and 2 have a one to many relation. Table 2 and 3 have a One to one relation (but no blank spaces in any of my tables)! I actually have a fourth table so when one of the AuditItems is specifically "Loom Down" it goes to the 4th table with the "ReasonCode" the machine was down. It also has a one-to-one relation with the AuditItemTable.

And I really didn't have to do a crosstab query for what I needed. I want to thank you again for taking the time to help! I SINCERELY appreciate YOU.