Need Some Suggestions On Table Layouts

I am converting a log book over to MS Access. The log book contains information on inspection results for a metallurgical lab. I have one problem w/ my “inspection table” which I will explain later in the post.

This is the plan I am using to figure out my entities and attributes for the database. I wrote a short description about the hand written log book we use at work.


A log book is an array individual fields.
Each field is made up of 11 columns
(date, initial, log #, sent to customer, customer, dept, part #, load #,
results, acc/rej, and defect code).

The date pertains to the time a particular part was inspected.
Initial indicates who checked the part.
Log # starts at “1” Jan 1st and increments for each check throughout the
year
Sent to cust asks if “inspected” samples are sent back with finished product.
Customer displays owner of finished product.
Dept indicates the processing area of plant.
Part # distinguishes the part type.
Load # may display what furnace and run count for the year to date.
Or, tub number in an array of tubs processed through a continuous furnace.
Results give a list of values on test results.
Acc/rej is a simple “yes/no” question asking if finished product is
acceptable to ship to customer.
Defect Code represents a particular defect found during inspection


Tables
Log -- *LOGNum, LOGDate, &LOG_TECId, &LOG_CUSTId, &LOG_FURId &LOG_PARTId, &LOG_INSPId, LOGaccept, &LOG_DEFCode
Customer -- *CUSTId, CUSTName,
Part number -- *PARTNum, &PART_CUSTId,
Technician -- *TECHId, TECHName
Defects -- *DEFCode, DEFType
Furnace -- *FURId, FURType
Inspection type -- *INSPid, :confused:

* Primary key
& Foreign key

One problem with the Inspection table; we have different inspections for each part number. Some require 10 RC punches others only require 5 RC punches. Some Require a Total Case inspection. Others Require an Effective Case inspection or both TC & EFF Case.

Some part #s require all tests (i.e. Crush, Total Case, Effective Case, RC, & R15N)

Once I figure out the basic table setup I can use a query to build the final log. And develop a forum for the data entry. I’ll worry about the CPK reports later :)

Here is a sample database Let me know what you think. I still need help on the INSP table :)
Your Download-Link: http://rapidshare.de/files/10087240/SampleTable.zip.html